Sep 12 2019 07:20 AM
Having a tough time figuring this one out.
I have a list of reports in a sheet. One column is full of report numbers. I want to conditionally format, highlight any duplicate report number, EXCEPT if the cell says "DRAFT".
I've tried =countif($C:$C,"<> DRAFT") but that isn't working. Any ideas?
Thanks in advance!
Sep 12 2019 08:25 AM
SolutionYou may try this...
Select the whole column C and make a New Rule for conditional formatting using the formula given below and set the format as per your choice.
=AND(COUNTIF(C:C,C1)>1,C1<>"DRAFT")
Sep 12 2019 10:41 AM
That worked, thanks!
Just so I understand the formula a bit better, what is the purpose of the ">1" part of the formula?
Sep 12 2019 11:14 AM
You're welcome!
>1 means that if there is only one occurrence of a value, the Countif formula will return 1 and if there are more than one occurrences of the same value, the Countif formula will return a count which would be greater than 1. So a value will be considered as a duplicate value only if the Countif returns a count which is greater than 1.
If that answers your query, please don't forget to mark the post with the proposed solution as an Accepted Answer which will mark your question as Solved.
Sep 12 2019 08:25 AM
SolutionYou may try this...
Select the whole column C and make a New Rule for conditional formatting using the formula given below and set the format as per your choice.
=AND(COUNTIF(C:C,C1)>1,C1<>"DRAFT")