Identifying frequency of records

[Jasper1]

I have a database in Excel 2019 which includes a column of 2000+ cells. Each cell either contains

a statement using the words Not Known or Broadcast Recording or No Choir or
from 1 upto 100 names.

A name will only appear once in a cell but may appear in several cells. The format for each name is John Smith, June Smith, etc each individual's family name being followed by a comma and a single space with the final/only name in the cell not having a comma after it. All appear in one column and on one worksheet and are listed in the cell in alphabetical order derived from the family name.

I have attached a sample for guidance using edited names showing as is and as should be.

What I would like to produce in another worksheet is a list of the names in alphabetical order by the family name in one column with in the adjoining column a total of the number of appearances of that name in the original list. I suspect that there may be around 1500 individuals involved some of whom may appear upto 1700 times in the column. So essentially, what I envisage is

identifying the first name in the first cell in the column
using it as the subject of a search through all the other cells in the column
calculating the number of times it has been encountered
inserting the name and the number of appearances in a new worksheet
identifying the second name in the first cell in the column and following through the same process until all the records in the first cell are exhausted
moving to the second cell and now checking that the name has not been encountered before, ignoring it if it has and processing it if it has been encountered ...and so on until all cells and names have been processed.

Very grateful for any ideas as to how this could be achieved or alternative methods

[CheeseSandwich]

Hi Jasper,

Below is my atempt, although it wont like it if some of the names have a middle name:

Please Login or Register  to view this content.

Hope this helps

[CheeseSandwich]

Another method might be:

Please Login or Register  to view this content.

This version modifies the raw data, the prvious one does not touch it.

[Jasper1]

Thank you very much for both solutions = just what I have been trying to achieve for the last week or so! And so quickly too. Very grateful indeed

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.