So you first want to identify all the names that appear anywhere in the grid of names, ignoring any duplicates. And present that as a list in a separate section of the sheet.
Then you want to count the corresponding number of entries in the grid which match each of the names from step 1. And enter that into your list next to the corresponding name.
Then you want to sort the result so the name with the most entries is the first, and the name with the least entries is last.
Is that an accurate statement of your goal and one approach to solving it?
Said another way, is the goal to “Have a de-duplicated list of names that appear anywhere in the grid, each accompanied by its number of entries in the grid, and with the list of counts + names sorted by number of entries in descending order”?
If you’re willing to manually identify all the names, and enter list of names in your results section of the sheet, having Excel generate the counts are trivial. If your grid is cells B2:F27, then =CountIf(B2:F27, "Carlos Alcaraz") will result in the number of Carlos entries in your grid.
I hope it’s obvious how to generalize that, with a list of names in e.g. column B and a list of CountIf() expressions next to them in column A taking their comparison value from Column B of the same row.
Getting them into sorted order is where it gets hard using just formulas versus macros or pivot tables.
Good. A clear understanding of the goal is a key step to solving anything.
I am not a pivot table maven up to this task. But I suspect they are the (only?) way forward if you want the process totally self-updating and automated when existing or new names are added to your grid.
This process would be trivial as a macro, but if you’re not already a computer programmer of some sort you’ll never get it working right. It’s not the sort of macro where you can just activate the keystroke/mouse recorder while you do the work once then play it back later when you have new data. It would be real coding.
I doubt there is a practical way to do this with a pivot table.
There is a simpler way using the approach indicated by @LSLGuy and @Chronos
Set out your data as below and then set up a second table under that as shown.
The names in ColA you need to add manually
The work in the lower table is done using countif formulas.
The formula for cell B17 is shown. You can copy that across and down.
Put a row sum in ColF and then sort the lower table by ColF descending.
You may want to add a total Slams to check that all titles (=33) are accounted for in the lower table.
Might even be worth having the names selected from a data validation droplist.
You will need to adjust the ranges as further years are added, and similarly any new title holder.
I was trying to do the same and UNIQUE() didn’t work, so I looked it up and apparently Office 2019 is before the UNIQUE function was added to EXCEL. It is in 2021 and later and 365
Yeah, it’s only in later versions. Unique (and other similar functions) are so dang useful! The newest versions have tons of really useful new functionality, especially the way they deal with array formulas.
MSFT has decided that since the world uses Excel for everything, it needs to be able to do everything. Database, query tool, statistical tool, presentation tool, word processor, etc.
Which also points out that any computer question needs to include which device, OS, & app(s) version(s) the OP is working with. e.g. Excel for Mac 2010 and Excel 365 for PC are two very different beasts.
A VSTACK function will turn the four columns into one.
Then you can do a conventional Pivot Table to count the winners.
For some reason I can’t sort the Count of Winners column in the PT
So I use a SORT function to do that.
