Sorting list of names by number of occurances

Hi Andrew, have you tried summarizing the data in a pivot table?

With only one column, I'm kind of lost as to how I would set that up. I guess I could sort out the duplicates to another column, do a Countif() for all the individual names in another column, then use that data in a Pivot table. I'll give it a shot...

--
Andy

Hi Andrew,

Here is a formula-based solution.

I'll assume your data to occupy the range A1:A9:

{"NAMES";"Tom";"Sue ";"Bob";"Jim";"Bob";"Tom";"Tom";"Carlo"}

I added the pair {Carlo} to your sample.

In B1 enter: =COUNTA(A:A)

In B2 enter: =IF(LEN(A2)>0,IF(ISNUMBER(MATCH(A2,$A$1:A1,0)),"",COUNTIF(INDIRECT($C$1),A2)),"") [ copy down this as far as needed ]

In C1 enter: =ADDRESS(2,1)&":"&ADDRESS(B1,1)

In C2 enter: =IF(ISNUMBER(B2),B2&"-"&A2,"") [ copy down as far as needed ]

In D1 enter: =ADDRESS(2,3)&":"&ADDRESS(B1,3)

In D2 array-enter: =IF(LEN(C2)>0,SUM(IF(C2<INDIRECT($D$1),1),1),"") [ copy down as far as needed ]

Note 1. In order to array-enter a formula, you need to hit CONTROL+SHIFT+ENTER at the same time, instead of just ENTER.

In E1 enter: =ADDRESS(2,4)&":"&ADDRESS(B1,4)

In E2 enter: =IF(ISNA(MATCH(ROW()-ROW($E$2)+1,INDIRECT($E$1),0)),"",INDEX(INDIRECT($D$1),MATCH(ROW()-ROW($E$2)+1,INDIRECT($E$1),0))) [ copy down this as far as needed ]

In F1 enter: Sorted NAMES [ just a label ]

In F2 enter: =IF(LEN(E2)>0,RIGHT(E2,LEN(E2)-SEARCH("-",E2)),"") [ copy down this as far as needed ]

In G1 enter: Freq [ just a label ]

In G2 enter: =IF(LEN(F2)>0,VALUE(LEFT(E2,SEARCH("-",E2)-1)),"") [ copy down this as far as needed ]

You'll see the following in F to G as the result of sorting by freq of occurrence:

{"Sorted NAMES","Freq";"Tom",3;"Bob",2;"Sue ",1;"Jim",1;"Carlo",1}

Note 2. If you'd like to have a copy of the workbook using the above system of formulas, let me know.

Aladin

===========================================

--

Andrew, you can create a pivot table using only one column as the data source. Just summarize by name in the row section and use the count function in the data section.

Does this help?

I'll give it a shot. Thanks!

--
Andrew

This looks like a good solution, but the formula for D2 deleted after the "C2" some how.

In D2 array-enter: =IF(LEN(C2)>0,SUM(IF(C2Note 1. In order to array-enter a formula, you need to hit CONTROL+SHIFT+ENTER at the same time, instead of just ENTER.

Could you post the formula for D2 again?

Thanks,
Andrew Hi Andrew, Here is a formula-based solution. I'll assume your data to occupy the range A1:A9: {"NAMES";"Tom";"Sue ";"Bob";"Jim";"Bob";"Tom";"Tom";"Carlo"} I added the pair {Carlo} to your sample. In B1 enter: =COUNTA(A:A) In B2 enter: =IF(LEN(A2)>0,IF(ISNUMBER(MATCH(A2,$A$1:A1,0)),"",COUNTIF(INDIRECT($C$1),A2)),"") [ copy down this as far as needed ] In C1 enter: =ADDRESS(2,1)&":"&ADDRESS(B1,1) In C2 enter: =IF(ISNUMBER(B2),B2&"-"&A2,"") [ copy down as far as needed ] In D1 enter: =ADDRESS(2,3)&":"&ADDRESS(B1,3) In D2 array-enter: =IF(LEN(C2)>0,SUM(IF(C2<INDIRECT($D$1),1),1),"") [ copy down as far as needed ] Note 1. In order to array-enter a formula, you need to hit CONTROL+SHIFT+ENTER at the same time, instead of just ENTER. In E1 enter: =ADDRESS(2,4)&":"&ADDRESS(B1,4) In E2 enter: =IF(ISNA(MATCH(ROW()-ROW($E$2)+1,INDIRECT($E$1),0)),"",INDEX(INDIRECT($D$1),MATCH(ROW()-ROW($E$2)+1,INDIRECT($E$1),0))) [ copy down this as far as needed ] In F1 enter: Sorted NAMES [ just a label ] In F2 enter: =IF(LEN(E2)>0,RIGHT(E2,LEN(E2)-SEARCH("-",E2)),"") [ copy down this as far as needed ] In G1 enter: Freq [ just a label ] In G2 enter: =IF(LEN(F2)>0,VALUE(LEFT(E2,SEARCH("-",E2)-1)),"") [ copy down this as far as needed ] You'll see the following in F to G as the result of sorting by freq of occurrence: {"Sorted NAMES","Freq";"Tom",3;"Bob",2;"Sue ",1;"Jim",1;"Carlo",1} Note 2. If you'd like to have a copy of the workbook using the above system of formulas, let me know. Aladin

Yeah, right. That's because of LessThan symbol the script of this web site cannot handle properly, a fact that I keep forgetting:

The mangled array-formula is:

=IF(LEN(C2)>0,SUM(IF(C2 &LT; INDIRECT($D$1),1),1),"")

Aladin In D2 array-enter: =IF(LEN(C2)>0,SUM(IF(C2Note 1. In order to array-enter a formula, you need to hit CONTROL+SHIFT+ENTER at the same time, instead of just ENTER. Could you post the formula for D2 again? Thanks,

Thanks, works like a charm!

--
Andy : This looks like a good solution, but the formula for D2 deleted after the "C2" some how. Yeah, right. That's because of LessThan symbol the script of this web site cannot handle properly, a fact that I keep forgetting: The mangled array-formula is: =IF(LEN(C2)>0,SUM(IF(C2 &LT; INDIRECT($D$1),1),1),"") Aladin : In D2 array-enter: =IF(LEN(C2)>0,SUM(IF(C2Note 1. In order to array-enter a formula, you need to hit CONTROL+SHIFT+ENTER at the same time, instead of just ENTER. : Could you post the formula for D2 again? : Thanks,

Here's a macro

Sub CountNames()
Dim names As Range, cell As Range
Dim nameList As Variant
Dim nameArray
Dim J As Long, I As Long
Dim first As Integer, last As Integer
Set names = Intersect(Columns(1), ActiveSheet.UsedRange)
For Each cell In names
cell.Value = StrConv(cell.Value, vbProperCase)
Next
nameList = Application.Transpose(names)
listSort nameList, 1, UBound(nameList) - LBound(nameList) + 1
J = 0
For I = LBound(nameList) To UBound(nameList)
If nameList(I) &LT;&GT; "" Then
If J = 0 Then
J = 1
ReDim nameArray(1 To 1)
nameArray(1) = nameList(I)
Else
If nameList(I) &LT;&GT; nameList(I - 1) Then
J = J + 1
ReDim Preserve nameArray(1 To J)
nameArray(J) = nameList(I)
End If
End If
End If
Next
first = LBound(nameArray)
last = UBound(nameArray)
Range("b" & first & ":b" & last).Value = Application.Transpose(nameArray)
With Range("c" & first)
.Formula = "=CountIf(" & names.Address & ", B1)"
.AutoFill Destination:=Range("c" & first & ":c" & last)
End With
With Range("c" & first & ":c" & last)
.Copy
.PasteSpecial (xlValues)
End With
Application.CutCopyMode = False
End Sub
Sub listSort(SortArray, L, R)
Dim I, J, X, Y
I = L
J = R
X = SortArray((L + R) / 2)
While (I &LT;= J)
While (SortArray(I) &LT; X And I &LT; R)
I = I + 1
Wend
While (X &LT; SortArray(J) And J > L)
J = J - 1
Wend
If (I &LT;= J) Then
Y = SortArray(I)
SortArray(I) = SortArray(J)
SortArray(J) = Y
I = I + 1
J = J - 1
End If
Wend
If (L &LT; J) Then Call listSort(SortArray, L, J)
If (I &LT; R) Then Call listSort(SortArray, I, R)
End Sub

Here's another macro

Alternative macro :-

Sub CountNames()
Dim rng As Range
Application.ScreenUpdating = False
With Intersect(Columns(1), ActiveSheet.UsedRange)
.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
.SpecialCells(xlCellTypeVisible).Copy Range("B1")
ActiveSheet.ShowAllData
End With
Columns(2).Sort Key1:=Range("B1")
Set rng = Range(Range("B1"), Range("B1").End(xlDown)).Offset(0, 1)
With Range("C1")
.Formula = "=CountIf(" & Intersect(Columns(1), ActiveSheet.UsedRange).Address & ", B1)"
.AutoFill Destination:=Range(rng.Address)
End With
With rng
.Copy
.PasteSpecial (xlValues)
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Re: Here's another macro

Thanks for all the good info. Works great...

--
Andy