Duplicates using conditional formatting

[Texman]

I have a sheet with a column containing surnames (more than 80 at any given time) and I need to highlight any that are duplicated. So I could normally do this easy enough using conditional formatting. The issue I have is that some cells contain more than one name so for example I might have 'Smith' in cell A2, 'Brown' in cell A3, 'Jones' in cell A4 etc but in cell A5 I could have 'Smith', 'Brown' or even 'Smith', 'Brown', 'Jones'. Conditional formatting does not pick the names as duplicates when entered together in a cell. I guess this is because technically they are not duplicate entries.

Is there any way that conditional formatting can be set do what I need?

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this could suit you. It allows for up to 4 names per cell but that could be increased. In your version of Excel the formula in column B may need to be confirmed with Ctrl+Shift+Enter, not just Enter.
After entering the formulas in this helper column the helper column could be hidden. We then use these helper results in the Conditional Formatting rule as shown.

20 11 26.xlsm
	A	B
1	Names	Helper
2	Smith	TRUE
3	Brown	TRUE
4	Jones, Jack, Kerr	FALSE
5	Smith, Brown	TRUE
6	Hill, Kent, James	TRUE
7	Black	FALSE
8	Kent, Hall, Hill	TRUE
CF Dupes

Cell Formulas
Range	Formula
B2:B8	B2	=COUNT(SEARCH(" "&TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2,",","")," ",REPT(" ",100)),{1,101,201,301},100))&" "," "&SUBSTITUTE(A$2:A$8,",","")&" "))>(LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A8	Expression	=B2	text	NO

 

[Texman]

Peter, that works a treat! Thank you so much. You were correct that my 2010 version of Excel did require Ctrl. Shift. Enter to confirm the formula. Isn't that called an array formula? You mention that this formula will work with up to 4 names per cell but could be extended. I sometimes have a few cells containing up to six names. How would the formula need to be amended?

 

[Peter_SSs]

Peter, that works a treat!

Good news!
Yes, it is called an array formula.

4 names per cell but could be extended. I sometimes have a few cells containing up to six names. How would the formula need to be amended?

Change {1,101,201,301} to {1,101,201,301,401,501}

 

[Texman]

Good news!
Yes, it is called an array formula.


Change {1,101,201,301} to {1,101,201,301,401,501}

Peter, that's fantastic! Once again, thank you so much for your assistance.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.