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:

PuebloEsteban

New Member
Joined
Apr 2, 2021
Messages
8
Office Version
  1. 2010
Platform
  1. Windows
The names start approximately at row 6 and progress into the hundreds.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,467
Office Version
  1. 365
Platform
  1. Windows
Sorry, but I'm now totally lost. The code is looking at column A of every sheet in the workbook & outputting the values it finds in col A of the results sheets.
Is that not what it should be doing?
 

PuebloEsteban

New Member
Joined
Apr 2, 2021
Messages
8
Office Version
  1. 2010
Platform
  1. Windows
Figured it out, thanks! Just changed a few As to Bs in the code.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,467
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,361
Messages
5,635,808
Members
416,884
Latest member
leeshjay

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