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 to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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.
 
Upvote 0
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!!
 
Upvote 0
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
1Claims
2NAME
3Robby Cage
4Wynell Reinecke
5Natosha Rozelle
6Lindsy Denardo
7Takako Aguiar
79Rachana Patel
80Sean Henwood
81
FY13



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



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


After the macro in a new worksheet Results:


Excel 2007
AB
1Tyesha Galeano6
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.

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.
 
Upvote 0
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!
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
WX
11Tyesha Galeano6
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).

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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

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
Back
Top