Find most frequently occurring string over multiple sheets

nataliejb

New Member
Joined
Jan 21, 2015
Messages
6
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:p></o:p>
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:p></o:p>

Thanks for the help in advance! :)

Natalie.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
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

New Member
Joined
Jan 21, 2015
Messages
6
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!!
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649

ADVERTISEMENT

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):

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #FF0000;;">Claims</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">NAME</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-top: 1px solid black;;">Robby Cage</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Wynell Reinecke</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Natosha Rozelle</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Lindsy Denardo</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Takako Aguiar</td></tr><tr ><td style="color: #161120;text-align: center;">79</td><td style=";">Rachana Patel</td></tr><tr ><td style="color: #161120;text-align: center;">80</td><td style="border-bottom: 1px solid black;;">Sean Henwood</td></tr><tr ><td style="color: #161120;text-align: center;">81</td><td style="text-align: right;border-top: 1px solid black;;"></td></tr></tbody></table><p style="width:2.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">FY13</p><br /><br />

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #FF0000;;">Claims</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">NAME</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-top: 1px solid black;;">Mittie Disney</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Lauryn Forry</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="border-bottom: 1px solid black;;">Gennie Mckiernan</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="border-top: 1px solid black;;">Sterling Bourassa</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Kary Mingus</td></tr><tr ><td style="color: #161120;text-align: center;">69</td><td style=";">Tyesha Galeano</td></tr><tr ><td style="color: #161120;text-align: center;">70</td><td style="border-bottom: 1px solid black;;">Jaz Semmler </td></tr><tr ><td style="color: #161120;text-align: center;">71</td><td style="text-align: right;border-top: 1px solid black;;"></td></tr></tbody></table><p style="width:2.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">FY14</p><br /><br />

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #FF0000;;">Claims</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">NAME</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Desirae Delossantos</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="border-bottom: 1px solid black;;">Cornell Guilliams</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="border-top: 1px solid black;;">Jessika Behne</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Earlean Crabill</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Branden Helstrom</td></tr><tr ><td style="color: #161120;text-align: center;">79</td><td style=";">Valorie Elsworth</td></tr><tr ><td style="color: #161120;text-align: center;">80</td><td style=";">James Vales </td></tr><tr ><td style="color: #161120;text-align: center;">81</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:2.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">FY15</p><br /><br />

After the macro in a new worksheet Results:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Tyesha Galeano</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Results</p><br /><br />

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.

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

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

New Member
Joined
Jan 21, 2015
Messages
6
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

Well-known Member
Joined
Apr 8, 2009
Messages
17,649

ADVERTISEMENT

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

New Member
Joined
Jan 21, 2015
Messages
6
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

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
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:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>W</th><th>X</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">Tyesha Galeano</td><td style="text-align: right;;">6</td></tr></tbody></table><p style="width:6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Stats FY15</p><br /><br />

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).

Code:
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

New Member
Joined
Jan 21, 2015
Messages
6
hiker95

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

:):pray::biggrin::biggrin:

Natalie.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,646
Messages
5,654,561
Members
418,140
Latest member
ahepple86

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