1

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
  • I tried this formula is Conditional formatting: =COUNTIFS($A$1:$A$9,A1,$B$1:$B$9,B1,$C$1:$C$9,C1)+COUNTIF($A$1:$A$9,A1)=3
    – Lee
    Aug 13, 2019 at 9:43

1 Answer 1

1

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

enter image description here

2
  • 1
    It's working properly ☺ Aug 10, 2019 at 7:17
  • JvdV: Thank you so much for your answer
    – JMD
    Aug 14, 2019 at 19:30

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .