I will try my best to explain the situation :

I have a sheet where we enter test data and serial numbers.

  • Column A is serial number
  • Column C is if the unit passed or not.

I currently have conditionalformatting to highlight duplicate serial numbers (based on Column A).

Because we fix and retest failed units we get duplicates of serial numbers where one has pass in column C and the other has fail.

What I would like to do is :
Have the sheet find duplicates but only highlight when both duplicates have "pass" in column C.

I have tried both and , I don't know if it is possible.


1 Answer 1


how about you create another column and concatenate A and C, then do this:


When you concatenate serial and pass/notpassed you have a unique value for serial A-pass and serial A-"not passed" and then you can countif them

  • That worked, I would have never thought to do it that way. Thank you. Commented Nov 13, 2019 at 13:41
  • Many times you will see the need of adding a new column with some extra data you'd need to do what you want to achieve Commented Dec 1, 2019 at 17:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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