Count how many instances of an item appear in a range

[Jon]

I have a spreadsheet with 4 columns: name,city state, and age.
Let's say I have 350 rows. I have sorted the rows by State. What I
want to do is count the number of times each state appears in the
state column and place the results somewhere else.
Example:
John Doe California
Pete Smith California
Dan Doe California
Jane Dill Idaho
Bill Will Nevada
Dan John Nevada
etc.....

I need a macro that will place in another location (let's say starting
a cell G5) the following
California 3
Idaho 1
Nevada 2

Thanks for any help you can give.
Jonco

 

[Bernard Liengme]

Do you really want a macro?
You could use Filter/Unique to get a list of unique state names (if needed)
Let's say the first unique state name is pasted to G5
If you data is in A1:E400 with state in column C
In H5 enter =COUNTIF(C1:C400,G5) to get the count
best wishes

 

[Lars-Åke Aspelin]

I have a spreadsheet with 4 columns: name,city state, and age.
Let's say I have 350 rows. I have sorted the rows by State. What I
want to do is count the number of times each state appears in the
state column and place the results somewhere else.
Example:
John Doe California
Pete Smith California
Dan Doe California
Jane Dill Idaho
Bill Will Nevada
Dan John Nevada
etc.....

I need a macro that will place in another location (let's say starting
a cell G5) the following
California 3
Idaho 1
Nevada 2

Thanks for any help you can give.
Jonco

For this problem you could use a pivot table.

But if you prefer to have a macro, here is one that you can try;

Sub test()
With Worksheets("Sheet1")
first_row = 1
state_column = 2
Set output_cell = .Range("G5")
last_row = .Cells(first_row, state_column).End(xlDown).Row
MsgBox last_row
n = 0
k = 0
For i = first_row To last_row
n = n + 1
If .Cells(i + 1, state_column) <> .Cells(i, state_column) Then
output_cell.Offset(k, 0) = .Cells(i, state_column)
output_cell.Offset(k, 1) = n
n = 0
k = k + 1
End If
Next i
End With
End Sub

Hope this helps / Lars-Åke