How Many in column B for each in Column A

[jomili]

My brain is frazzled and I can't think my way through this. In a nutshell; Vendor supplies us with a list of employee names that the vendor did services for. Vendor can't spell, so the names he supplies are all over the place. In my example, I have the actual name in column A, and the vendor supplied names in column B. (this is just a sample; actual list is about 10K more employees). What I want to know is:

• (First)how many UNIQUE derivatives exist in column B for each name in column A?
• (Second)Which actual name has the most derivatives?

Sorry to bring this to you, but my brain is apparently on strike, 'cause I can't think this one through yet.
I appreciate any help I can get.

[DJunqueira]

Proposed solution with Power Query.

[FlameRetired]

With formula ... although with that many lines of data this may be prohibitive. In C4 and filled down this gives a count of unique derivatives for each name in column A. Since you are using Office 365 this does not have to be array entered.

=IF(COUNTIF($A$4:$A4,$A4)>1,"",SUM(FREQUENCY(IF($A4=$A$4:$A$92,MATCH($B$4:$B$92&"",$B$4:$B$92&"",0)),

ROW($B$4:$B$92)-MIN(ROW($B$4:$B$92))+1)))

Then the MAX

=MAX($C:$C)

[TMS]

Another option, if I have understood your requirement.

[TMS]

Or maybe this is better.

[jomili]

I am SO sorry. Apparently I forgot about this post (hence my frazzled brain in the first post). I just this minute came back to it. Please accept my apologies.

DJunqueria and FlameRetired, I really appreciated both your efforts, but the results aren't what I expected. For instance, "Akiri,Brigid O" is "Our" name. That name appears 19 times. But the vendor gave us 17 different names for this person:"Brigid" and "Brigid O Akiri" were both used twice, so the correct answer would be 17 unique names, not 19.

TMS, both of your attempts were spot on, but I liked the second better. Thanks for showing me a way to eat my elephant. I haven't yet tried it on my full data set yet, but that's my next project. I'll let you know how it works.

[TMS]

You're welcome. Thanks for the rep.