Copper Contributor

I have a column with names in it.  The column does have repeat names.  I need to make sure that when that name appears in the column, there is the same value in another column for all the names or rows with that same name.


6 Replies


Let's say the names are in D2 and down, and the other data in E2 and down.

Select D2:E100 or however far down you want.

The active cell should be in row 2.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula




Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.


Rows that violate the requirement will be highlighted.

@Hans Vogelaar Thank you for the info, but it did not work.  Here is what I am looking to do:

Row 2SmithMD
Row 3JonesPA
Row 4SmithDO
Row 5JonesPA


I need the formula to look at all Names in the Column, and if there is more than 1 record that has a different DESIG, then highlight.  So with the case above, Smith would be highlighted, and Jones would not.


This is the result of the rule that I proposed, adjusted for columns A and B:


Workbook attached below. Is this not what you want?

THANK YOU!!! The formula works, I just hadn't selected the area first.
Hi, again. Can I use the same formula if I want to add in a First Name column? It would highlight if the two NAME columns were the same with a different DESIG?


Let's say first and last name are in columns A and B, and DESIG in column C.

Select A2:C100 or however far down you want, and use the following formula in the conditional formatting rule: