Find most frequently occurring string over multiple sheets 2

[PuebloEsteban]

Hello,
I'm using this code:

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:

Find most frequently occurring string over multiple sheets

Hi All, I am new to the Forums but regularly search old threads for helpful hints. Always manage to find something I can adaptto what I need. Legends! I have an Excel file that contains 8 sheets (FY08, FY09....FY15). What I wouldlike to do is find the most frequently occurring name from all...

www.mrexcel.com

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.

 

[Fluff]

Hi & welcome to MrExcel.
If you delete this line from the 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.

 

[PuebloEsteban]

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]

Isn't that what the code already does?

 

[PuebloEsteban]

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]

The error that comes up is Run-time error '13': Type mismatch

 

[Fluff]

If you delete this line

  .Range("A2:B" & n).ClearContents

as I suggested, then it will output all the values.
Alternatively use

  .Range("A11:B" & n).ClearContents

to just show the top 10.
How are you deleting the most frequent string?

 

[PuebloEsteban]

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]

Are you saying that throughout the workbook you only have two different values in col A?

 

[PuebloEsteban]

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.