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

  • 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 at 9:43
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

  • 1
    It's working properly ☺ – Rajesh S Aug 10 at 7:17
  • JvdV: Thank you so much for your answer – JMD 2 days ago

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.