Duplicates using conditional formatting

Texman

New Member
Joined
Nov 25, 2020
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
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?
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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
AB
1NamesHelper
2SmithTRUE
3BrownTRUE
4Jones, Jack, KerrFALSE
5Smith, BrownTRUE
6Hill, Kent, JamesTRUE
7BlackFALSE
8Kent, Hall, HillTRUE
CF Dupes
Cell Formulas
RangeFormula
B2:B8B2=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
CellConditionCell FormatStop If True
A2:A8Expression=B2textNO
 
Solution

Texman

New Member
Joined
Nov 25, 2020
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Peter, that works a treat!
Good news! :biggrin:
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

New Member
Joined
Nov 25, 2020
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
Good news! :biggrin:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the follow-up. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,976
Messages
5,599,143
Members
414,292
Latest member
kingshuk963

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top