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!

 

[Fluff]

Hi & welcome to MrExcel.
Do you have the dynamic array functions such as FILTER & SORTBY?

 

[Legacy 462862]

Hi & welcome to MrExcel.
Do you have the dynamic array functions such as FILTER & SORTBY?

Thanks!

I haven't included those - I've never used VBA before so I'm very new to this.

 

[Fluff]

Do you mean that you don't have those functions?
If you type =so into a cell do you get the option of SORT and SORTBY?

 

[Legacy 462862]

I don't have those functions

 

[Fluff]

Ok, do you just want to use the function to return the largest, or 2nd largest etc value, or do you want a macro the will output the results?

 

[Legacy 462862]

A macro preferably but whatever works the best way?

 

[Legacy 462862]

A bit of context might help - the data I'm trying to sort is for missing timesheets. Each week the doc is updated with those who haven't completed it and I'm trying to find a way of identifying the common culprits (preferably ranking the top 10).

 

[Fluff]

Ok what cells/columns are you interested in?
Can you post some sample data using the XL2BB add-in. Best if you use fake names rather than real ones.

 

[Legacy 462862]

I'm having issues with the XL2BB add-in, I've tried on multiple laptops.

I've attached a screenshot. The data is from N2:X21 but each week an extra column will be added. I've attached a mock of the data.

EDIT:
Image removed as contains confidential info.