Need formula or vba code to find top 5 occurrences of text string in one column

[Geeta2013]

Hi All - Just as an fyi, I often use this site. I generally am able to take prior issues I see here, and resolve my own. However, I have searched the forum, and do not find a resolution to my current issue. Here goes (yes, the data was imported from a database apparently put together by someone who shouldn't have been doing that for a living)

Column: A - has inserted in each cell the names of people to whom emails were sent. If the emails were sent to multiple people, then that cell contains the names of all the people to whom the emails were sent. There are thousands of rows. (Some have one email address, some have multiple email addresses, some people are emailed in many different rows). What I need to do is to figure out a way to find out top 5 names that appear in Column A i.e. - who are the five people emailed to within this Column. Unfortunately, I cannot upload the file ( I may be shot if I do). So, for argument's sake: I will provide 7 names: Smith, Cathy; Doe, John, Doer, Jane; Singh, Asong; Singh, Along; Crying, Me; Happy, Me; (this is the way they are presented within the column - starts with the last name, separated by a comma from the first name, and each individual is separated by a comma.

Thanks ahead for your time.

Regards,
Geeta

 

[Smallman]

Hi Geeta

If I have understood your problem correctly then you might have a look at the following. After the process has run I would suggest you filter by TOP 5 and you should be done. If you want the code equivalent then just sing out.

Option Explicit
 
Sub FixProblemo()
Dim r As Range
  Sheet2.[a2:A100].ClearContents
  Columns("A").TextToColumns , xlDelimited, , , , True
  Range("A2", Range("A" & Rows.Count).End(xlUp)).Copy Sheet2.[a2]
 
    For Each r In Range("A1").CurrentRegion.Offset(1, 1).SpecialCells(2)
        Sheets(2).Range("A" & Rows.Count).End(xlUp)(2) = Array(r.Text)
    Next
End Sub

I assume Doe, John, in your example should have had a semi colon after the name. File attached to show workings. 41 names in the list before. Check sheet 2 after - 41 names.

Take care

Smallman

 

[Geeta2013]

Thank you. Worked like a charm.

 

[Smallman]

No worries Geeta

Catch you next time.

Smallman