Find most frequently occurring string over multiple sheets

[nataliejb]

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 the sheets. Ihave used

=INDEX('FY08'!A3:A498,MODE(IF('FY08'!A3:A498<>0,MATCH('FY08'!A3:A498,'FY08'!A3:A498,0))))

to find the most frequently occurring name in each sheet but now I would liketo find the most frequently occurring name over all the sheets - which is not necessarilyone of the names that occurs most frequently in a single sheet.<o></o>
Previous threads I have come across are only to find howfrequently a particular name occurs in multiple sheets. This isn’t exactly what I am after as I don’twant to count the number of times a name appears but extract the most frequently occurring name.

I have tried to be fairly succinct with my issue but pleaselet me know if I haven’t explained it very well.<o></o>

Thanks for the help in advance!

Natalie.

 

[hiker95]

nataliejb,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


3. Is the range A3:A498 in column A the same for all the worksheets FY08, FY09....FY15?


Because of the size of your raw data, screenshots are probably not a good idea.

4. Can we see at least two, or, three of the worksheets with just the information in column A?


You can upload your workbook to Box Net,

sensitive data changed

mark the workbook for sharing

and provide us with a link to your workbook.

 

[nataliejb]

Hi Hiker95,

Thanks for your response. I have done as you have suggested and upload a file which mirrors the file I am working on. I needed to remove all data and have changed names for privacy reasons.

I am using Office 2010 and Windows 7 on a PC. All names are in the same column for all worksheets A3:A180.

The link to the mock file is https://app.box.com/s/lq0myrqggtdiy1tvdt0rjfai22lnix52

Definitely appreciate the help!!

 

[nataliejb]

Accidental double post. Aplogies.

 

[hiker95]

nataliejb,

Thanks for the workbook.

You did not say where the results should be written to, or, displayed?

Here is a macro solution for you to consider.

Sample worksheets (not all rows are shown for brevity):

Excel 2007
	A
1	Claims
2	NAME
3	Robby Cage
4	Wynell Reinecke
5	Natosha Rozelle
6	Lindsy Denardo
7	Takako Aguiar
79	Rachana Patel
80	Sean Henwood
81
FY13

Excel 2007
	A
1	Claims
2	NAME
3	Mittie Disney
4	Lauryn Forry
5	Gennie Mckiernan
6	Sterling Bourassa
7	Kary Mingus
69	Tyesha Galeano
70	Jaz Semmler
71
FY14

Excel 2007
	A
1	Claims
2	NAME
3	Desirae Delossantos
4	Cornell Guilliams
5	Jessika Behne
6	Earlean Crabill
7	Branden Helstrom
79	Valorie Elsworth
80	James Vales
81
FY15

After the macro in a new worksheet Results:

Excel 2007
	A	B
1	Tyesha Galeano	6
2
Results

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

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

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the GetMostFrequentName macro.

 

[nataliejb]

Hi hiker95,

Thank you so much. It works great!

I haven't done much with macro's before so forgive me for asking the next question... If I wanted the reults to display in an already existing sheet titled "Stats FY15" the name in cell W11 and the number in X11 what would I need to edit?

Hope you had an excellent weekend!

 

[hiker95]

nataliejb,

Thanks for the feedback.

You are very welcome. Glad I could help.

Hope you had an excellent weekend!

Yes I did. I hope you had an excellent weekend.

If I wanted the reults to display in an already existing sheet titled "Stats FY15" the name in cell W11 and the number in X11 what would I need to edit?

All names are in the same column for all worksheets A3:A180

1. Do you want the macro to search in worksheet Stats FY15 in the above range A3:A180?

2. In worksheet Stats FY15, is there any data/information below cells W11, and, X11?

 

[nataliejb]

Hi hiker95,

No, just the sheets FY08:FY15 in the range A3:A180. There is currently no data in or below W11 and X11, only above. There is however data in the columns A1:V133. It is summary and statistical data from FY15.

 

[hiker95]

nataliejb,

Thank you.

The following is based on my screenshots in my reply #5.

Here are the results after the new macro in worksheet Stats FY15:

Excel 2007
	W	X
11	Tyesha Galeano	6
Stats FY15

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub GetMostFrequentNameV2()
' hiker95, 01/26/2015, ME830512
Dim ws As Worksheet
Dim rng As Range, c As Range, o As Variant, lr As Long
Application.ScreenUpdating = False
With CreateObject("Scripting.Dictionary")
  .CompareMode = vbTextCompare
  For Each ws In ThisWorkbook.Worksheets
    If Not ws.Name = "Stats FY15" 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
  o = Application.Transpose(Array(.Keys, .Items))
End With
With Sheets("Stats FY15")
  lr = .Cells(Rows.Count, "W").End(xlUp).Row
  If lr > 10 Then .Range("W11:X" & lr).ClearContents
  Range("W11").Resize(UBound(o, 1), UBound(o, 2)) = o
  lr = .Cells(Rows.Count, "W").End(xlUp).Row
  .Range("W11:X" & lr).Sort key1:=.Range("X11"), order1:=2, key2:=.Range("W11"), order1:=1
  .Range("W12:X" & lr).ClearContents
  .Columns("W:X").AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the GetMostFrequentNameV2 macro.

 

[nataliejb]

hiker95

You.Are.A.Legend! Cannot thank you enough. Works great and and results display exactly where I wanted them. Thank you!!

ray:

Natalie.