1

I have a Google Sheets table that looks like this:

(col a)    (col b)   (col c)          (col d)
Company    Cohort    Session Count    Group
--------------------------------------------
Acme       Jan2019   4                Foo
Acme       Jan2019   3                Bar
Fairview   Jan2019   3                Baz
Fairview   Jan2019   3                Buzz
Warner     Feb2019   2                Buzz

I want to highlight rows were A and B are the same but C is not. Bonus points if it can highlight only the row with the lower number, but not necessary.

example:

Company    Cohort    Session Count    Group
--------------------------------------------
Acme       Jan2019   4                Foo
Acme       Jan2019   3                Bar (this row gets highlighted)
Fairview   Jan2019   3                Baz
Fairview   Jan2019   3                Buzz
Warner     Feb2019   2                Buzz

Can I accomplish this with Conditional Formatting, or do I need to write some JS to process this? I am not savvy with conditional formatting and would love some help. I can, of course, solve it with JS but would prefer not to if possible.

1
  • you will need to establish an auxiliary column and then set up conditional formatting from there:

={"AUX"; ARRAYFORMULA(REGEXREPLACE(IFERROR(VLOOKUP(A2:A&B2:B&" "&C2:C,
 TRANSPOSE(QUERY(TRANSPOSE(VLOOKUP(QUERY(QUERY(QUERY(QUERY(
 {INDEX(IF(LEN(A2:A), IF(COUNTIF(A2:A&B2:B, A2:A&B2:B)>1=TRUE, A2:C, ), ), , 1)&
  INDEX(IF(LEN(A2:A), IF(COUNTIF(A2:A&B2:B, A2:A&B2:B)>1=TRUE, A2:C, ), ), , 2),
  INDEX(IF(LEN(A2:A), IF(COUNTIF(A2:A&B2:B, A2:A&B2:B)>1=TRUE, A2:C, ), ), , 3)}, 
 "select Col1,Col2,count(Col1) where Col1 is not null group by Col1,Col2"),
 "where Col3 <2"), "select Col1,count(Col1) group by Col1"), "select Col1"),
 QUERY(QUERY(
 {INDEX(IF(LEN(A2:A), IF(COUNTIF(A2:A&B2:B, A2:A&B2:B)>1=TRUE, A2:C, ), ), , 1)&
  INDEX(IF(LEN(A2:A), IF(COUNTIF(A2:A&B2:B, A2:A&B2:B)>1=TRUE, A2:C, ), ), , 2),
  INDEX(IF(LEN(A2:A), IF(COUNTIF(A2:A&B2:B, A2:A&B2:B)>1=TRUE, A2:C, ), ), , 3)}, 
 "select Col1,Col2,count(Col1) where Col1 is not null group by Col1,Col2"),
 "where Col3 <2"), {1, 2}, 0)), , 99999)), 1, 0), ), ".+", "1"))}

  • custom formula is: =$H1="1"
  • 1
    You are an absolute wizard! Thank you so much for providing the exact answer. Super helpful, and worked perfectly!!!! – theartofbeing Feb 28 at 16:46

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.