0

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.

  • 1
    @user0 It's generally okay to leave UK spellings ("cancelled") intact when editing. Not a big deal, but just something to be mindful of. – jonsca Jul 18 '18 at 0:07
0

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.

0

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.

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.