Find most frequently occurring string over multiple sheets 2

PuebloEsteban

New Member
Joined
Apr 2, 2021
Messages
8
Office Version
  1. 2010
Platform
  1. Windows
Hello,
I'm using this code:

Code:
VBA Code:
Sub GetMostFrequentName()
' hiker95, 01/23/2015, ME830512
Dim ws As Worksheet, wr As Worksheet
Dim rng As Range, c As Range, o As Variant, n As Long
Application.ScreenUpdating = False
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add().Name = "Results"
Set wr = Sheets("Results")
wr.UsedRange.Clear
With CreateObject("Scripting.Dictionary")
  .CompareMode = vbTextCompare
  For Each ws In ThisWorkbook.Worksheets
    If Not ws.Name = "Results" Then
      Set rng = ws.Range("A3:A" & ws.Range("A" & Rows.Count).End(xlUp).Row)
      For Each c In rng
        If c <> "" Then
          If Not .Exists(Trim(c.Value)) Then
            .Add Trim(c.Value), 1
          Else
            .Item(Trim(c.Value)) = .Item(Trim(c.Value)) + 1
          End If
        End If
      Next c
    End If
  Next ws
  n = .Count
  o = Application.Transpose(Array(.Keys, .Items))
End With
With wr
  .Cells(1, 1).Resize(UBound(o, 1), UBound(o, 2)) = o
  .Range("A1:B" & n).Sort key1:=.Range("B1"), order1:=2, key2:=.Range("A1"), order1:=1
  .Range("A2:B" & n).ClearContents
  .Columns("A:B").AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

from this question:

And finding that when I search for the string that was returned, to delete it (so I know I have finished with that person/name), I am getting very different results in terms of how many times that name is actually occurring / being found in the spreadsheet.

For example if I press F8, run the macro, and the name returned is Abraham Matthews, then I find/replace Abraham Matthews, the spreadsheet might replace e.g. 6 names. Then if I run it again, and it finds Melissa Purview, then I find/replace Melissa Purview, it might replace 11 incidences of that name. This makes me think that the macro is not finding the most occurring names in the workbook by frequency.

Can anyone assist? I have data in columns from A to Q in approximately 12 worksheets, with at least 200 rows.
 
Last edited by a moderator:

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,890
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
If you delete this line from the code
VBA Code:
  .Range("A2:B" & n).ClearContents
you will be able to see all the names that were found & how many times.
That may give you an indication of what the differences are.
 
Solution

PuebloEsteban

New Member
Joined
Apr 2, 2021
Messages
8
Office Version
  1. 2010
Platform
  1. Windows
Thank you very much. Extra challenge... is it possible to generate the names of the top ten most occurring people, with the frequency of occurring numerical data next to it?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,890
Office Version
  1. 365
Platform
  1. Windows
Isn't that what the code already does?
 

PuebloEsteban

New Member
Joined
Apr 2, 2021
Messages
8
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Hi Fluff, the code currently outputs the most frequent string, I was hoping that it would be possible for it to output the ten most frequent strings in order. I'm having some trouble with the initial code though. Now when I use the code I'm getting a debug error with :

o = Application.Transpose(Array(.Keys, .Items))

highlighted. The problem occurs after I run the code, then delete the most frequent string, so I can find the next most frequent string.
 

PuebloEsteban

New Member
Joined
Apr 2, 2021
Messages
8
Office Version
  1. 2010
Platform
  1. Windows
The error that comes up is Run-time error '13': Type mismatch
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,890
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

If you delete this line
VBA Code:
  .Range("A2:B" & n).ClearContents
as I suggested, then it will output all the values.
Alternatively use
VBA Code:
  .Range("A11:B" & n).ClearContents
to just show the top 10.
How are you deleting the most frequent string?
 

PuebloEsteban

New Member
Joined
Apr 2, 2021
Messages
8
Office Version
  1. 2010
Platform
  1. Windows
Thanks Fluff. If I delete the .Range line I only get two strings, the two that are tied for highest frequency. Then if I cntrl+F, replace the string with nothing, I get the run-time error. If I replace the .Range line with .Range("A11:B" & n).ClearContents I only get one string returned. If I cntrl+F, replace the string with nothing, I get a new run-time error: 9, subscript out of range, with: .Cells(1, 1).Resize(UBound(o, 1), UBound(o, 2)) = o highlighted in the debug window.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,890
Office Version
  1. 365
Platform
  1. Windows
Are you saying that throughout the workbook you only have two different values in col A?
 

PuebloEsteban

New Member
Joined
Apr 2, 2021
Messages
8
Office Version
  1. 2010
Platform
  1. Windows
Oh okay, yes there are only two string values in column A. I'm trying to output frequency of strings in column B actually, column B contains all of the names.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,187
Messages
5,640,693
Members
417,161
Latest member
Devon150

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