VBA help to identify most frequently occurring text

[Legacy 462862]

Hi,

I've used the following to identify the most frequently occurring text in my sheet:

- - -

Function Freq(rng As Range) As String

Dim cll As Range
Dim lngCount As Long
Dim lngMax As Long
Dim str As String

Set dic = CreateObject("scripting.dictionary")

On Error Resume Next

For Each cll In rng

If cll.Value <> "" Then
dic(cll.Value) = dic(cll.Value) + 1
lngCount = dic(cll.Value)
If lngCount > lngMax Then
lngMax = lngCount
str = cll.Value
End If
End If
Next

On Error GoTo 0

Freq = str

End Function

- - -

Does anyone know what needs to be changed to identify the 2nd, 3rd, 4th, 5th etc? Or a different way of doing it?

Thanks!

 

[Legacy 462862]

It has totals in row 24 and a bit of text but that's it.

 

[Legacy 462862]

Could it be to do with the hidden columns?

 

[Fluff]

Ok, how about

Sub zoersdn()
   Dim Ary As Variant, Kth As Variant
   Dim r As Long, i As Long, NxtRw As Long
   Dim Dic As Object
  
   Set Dic = CreateObject("scripting.dictionary")
   Dic.CompareMode = 1
   Ary = Range("N11:X21").Value2
   NxtRw = UBound(Ary) + 4
  
   For r = 2 To UBound(Ary)
      For i = 1 To UBound(Ary, 2)
         If Ary(r, i) <> "" Then
            Dic(Ary(r, i)) = Dic(Ary(r, i)) + 1
         End If
      Next i
   Next r
   ReDim Ary(1 To Dic.Count, 1 To 2)
   Kth = Application.Large(Dic.Items, 10)
   If IsError(Kth) Then Kth = Application.min(Dic.Items)
   r = 0
  
   For i = 0 To Dic.Count - 1
      If Dic.Items()(i) >= Kth Then
         r = r + 1
         Ary(r, 1) = Dic.Keys()(i)
         Ary(r, 2) = Dic.Items()(i)
      End If
   Next i
   Range("N" & NxtRw).Resize(r, 2).Value = Ary
End Sub

 

[Legacy 462862]

That's pulled 30 of the names with the value '1' next to them

 

[Fluff]

Do any of the names exist more than once in the N11:X21 range?

 

[Legacy 462862]

Yes some of them do

 

[Fluff]

In that case check the none of the names have leading/trailing spaces and that they are spelt exactly the same (case does not matter)

 

[Legacy 462862]

They're all in exactly the same format (cases, spelling, space etc). Also, I only want to see the top 10.

 

[Fluff]

What happens if you have multiple people who are tied?

 

[Legacy 462862]

They can still be included. The code you used before seemed to pull the correct data and was closer to what I'm looking for but the numbers were slightly off.