Google-sheets – Conditionally format rows with partial matches

conditional formattinggoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-queryvlookup

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.

Best Answer

  • 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"
Related Topic