Conditionally formatting duplicate values in Google sheets with exclusion criteria?

Question

I'm currently working in a Google sheet where I use the following formula to catch duplicate values with conditional formatting:

=countif($B:$B,B2)>1

While this works on its own, I find that it does not account for duplicate values I do not want to be counted. In column O, I have values marking the row as "Canceled". So I want to have Google sheets only conditionally format cells if the following criteria are met:

• The value in column B has a duplicate

AND

• Neither duplicate value has the word "canceled" in column O for that row.

This is the formula I tried, but it no longer formats duplicates:

=AND((countif($B:$B,B2)>1=TRUE),ISNUMBER(SEARCH("Cancelled",$O:$O)=FALSE))

I think what this has done has told the logic to not format duplicates unless there are no instances of "canceled" in column O.

Answer 1

Solved my own question, in a bit of a brute force method. Since Google sheets was a bit iffy about letting me do an AND statement on ISNUMBER(SEARCH()), I added 3 helper columns which I hid:

• Column P =ISNUMBER(SEARCH("Cancelled",$O2)) *Checking for Cancelled on in a string TRUE/FALSE
• Column Q =IF($P2=TRUE,1,0) *Returns 1 in any column with TRUE for Cancelled
• Column R =$Q2+$B2 *Adds a 1 to any order number where the previous formula returns a 1

I then used =countif($R:$R,R2)>1 and applied it to column B.

Answer 2

I believe the following formula, entered into the Conditional Formatting dialog as "Custom formula is" would do the trick alone (i.e., no need for hidden helper columns):

=AND(O2<>"Cancelled",COUNTIF(B:B,B2)>1,COUNTIF(B:B,B2)>2*COUNTIFS(B:B,B2,O:O,"Cancelled"))

One note: This formula will, in effect, cancel itself if there is a one-to-one correlation between one duplicate that is not canceled and one duplicate that is. However, in the case that there were multiple non-cancelled duplicates (say, three non-cancelled and one cancelled), then there is not a one-to-one match and all non-cancelled duplicates will be highlighted (since, without further information here, there is no way for Sheets to know which is the match.

If you only ever have a max of two duplicates in Column B, this formula should work fine.