VBA help to identify most frequently occurring text

L

Legacy 462862

Guest
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!
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,135
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Do you have the dynamic array functions such as FILTER & SORTBY?
 
L

Legacy 462862

Guest
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,135
Office Version
  1. 365
Platform
  1. Windows
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?
 
L

Legacy 462862

Guest

ADVERTISEMENT

I don't have those functions
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,135
Office Version
  1. 365
Platform
  1. Windows
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?
 
L

Legacy 462862

Guest

ADVERTISEMENT

A macro preferably but whatever works the best way?
 
L

Legacy 462862

Guest
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,135
Office Version
  1. 365
Platform
  1. Windows
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.
 
L

Legacy 462862

Guest
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.
 
Last edited by a moderator:

Watch MrExcel Video

Forum statistics

Threads
1,132,640
Messages
5,654,539
Members
418,139
Latest member
nimesh72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top