Search for repeating names in a range of cells

[Reiner94]

Hey everyone. Hoping someone can assist. I have a range of cells with names separated by commas. What I'd like Excel to do is search each cell to find repeating names. The issue is that names change and there are an infinite number of different names, so I can't have it search for a specific value; rather, I need Excel to compare different cells and isolate strings that are the same.

Cell A1 might contain "Jade, Denzel, Jonie, Rianna, Lester".

Cell A2 might contain "Jonie, William, Cam".

Cell A3 might contain "Lester, Jonie".

What I'm looking for Excel to do is search each cell, and if the same name is found more than twice in the range (in this example, that'd be Jonie), to return that name in a separate cell.

Any creative solutions? I am still building my sheet so I can adapt it into whatever I need to make this happen. I realize this could be a multiple-part solution. For instance I was thinking why not have Excel extract each name first into individual cells, and then look for triplicates in the resulting range of data. But since each cell could have a different number of names, using MID, RIGHT and LEFT functions are a little tedious.

If I need to venture into VBA to do this, no problem at all - I will need help with the code though!

Thanks kindly!

[MarvinP]

Hi Reiner,

If you were using Excel 365 with the newer functions of TextSplit and Spill functions, your problem would be easier. See the attached that uses these functions of:

=TOCOL(VSTACK(TEXTSPLIT(A2, ","), TEXTSPLIT(A3, ","), TEXTSPLIT(A4, ",")),3)

=UNIQUE(FILTER(B2:C11,C2:C11>1))

If one of the smart Guru's can come up with a better answer, it might be time to upgrade to Excel 365.

[MarvinP]

Hi,
Because you are using an older version of Excel his is another method..
In C2 put this formula:

=TRIM(MID(SUBSTITUTE($A2, ",", REPT(" ", LEN($A2))), (COLUMNS($C1:C1) - 1) * LEN($A2)+1, LEN($A2)))

Pull it across and down as far as you need to include all the names.
Then do a Conditional Format to show Duplicates. That will show whose name appears more than once.

[Reiner94]

Thanks, that's a bit of a workaround but I can make that work! Thanks for your help!