I have employee records where an employee can have more than one row based on his rate, I want to format the rows where the employee Id is not unique to the employee if the id is duplicated but his last name and first name are not the same as the duplicate ID to highlight the mismatched records. picture example Shows a mismatched rows in row 2 and row 8 ; records in row 5 and 6. Thanks for your help in advance.Image of excel data sheet
1 Answer
You can compare the times a unique ID excists against the times the ID exists along with first and last name with the following:
- Select range
A2:D10
Insert new conditional formatting rule > Formula:
=COUNTIF($A$2:$A$10,$A2)<>COUNTIFS($A$2:$A$10,$A2,$B$2:$B$10,$B2,$C$2:$C$10,$C2)
Apply a format and confirm
-
1
-