Forum Discussion
FORMULA OR KUTOOLS QUERY
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.
HansVogelaar Thank you for the info, but it did not work. Here is what I am looking to do:
| NAME | DESIG | |
| Row 2 | Smith | MD |
| Row 3 | Jones | PA |
| Row 4 | Smith | DO |
| Row 5 | Jones | PA |
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?
- 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:
=COUNTIFS($A:$A,$A2,$B:$B,$B2,$C:$C,"<>"&C2)
- THANK YOU!!! The formula works, I just hadn't selected the area first.