FORMULA OR KUTOOLS QUERY

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

@TRiceGMH 

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

 

=COUNTIFS($D:$D,$D2,$E:$E,"<>"&$E2)

 

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:

 NAMEDESIG
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.

@TRiceGMH 

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

HansVogelaar_0-1693329408933.png

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?

@TRiceGMH 

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:

=COUNTIFS($A:$A,$A2,$B:$B,$B2,$C:$C,"<>"&C2)