Google-sheets – Comparing two cells and adding text to matching values

formulasgoogle sheetsgoogle-sheets-arrayformularegexregexextract

I have two columns, one with all items in a set and another with the items I currently have. I would like to compare those and for the matching results to have a bit of text added if they match.

So in that example, Column A is the set number, Column B is the set values, Column C is what I have currently, and Column D is the expected result.

So I want to compare B to C and if there are names matching, then I want B to have the word 'CHECK' appended to the matching name.

I thought about doing this to get the word 'CHECK' added 

=ARRAYFORMULA(B2:B & " -CHECK")

But do I also need a LOOKUP and LEN function here?

Would apps script maybe be easier?

Best Answer

  • paste in D2 cell and drag down:

    =ARRAYFORMULA(TEXTJOIN(", ", 1, REGEXREPLACE(TRIM(IFERROR(SPLIT(B2, ","))), 
 TEXTJOIN("|", 1, IF(REGEXMATCH(TRIM(IFERROR(SPLIT(B2, ","))), 
 TEXTJOIN("|", 1, TRIM(IFERROR(SPLIT(C2, ","))))), 
 TRIM(IFERROR(SPLIT(B2, ","))), )), TRIM(IFERROR(SPLIT(B2, ",")))&" - CHECK")))

    0

=ARRAYFORMULA(SUBSTITUTE(SUBSTITUTE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(SUBSTITUTE(IF(ISNUMBER(
 QUERY(QUERY(IFERROR(SPLIT(IF(IF(REGEXMATCH(TRIM(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(
 TRANSPOSE(IFERROR(IF(SPLIT(B2:B, ",")<>"", "♠"&A2:A&"♦"&TRIM(IFERROR(SPLIT(B2:B, ","))), )))
 ,,999^99)),,999^99), "♠"))),SUBSTITUTE(TRIM(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IFERROR(IF(
 SPLIT(C2:C, ",")<>"", A2:A&"♦"&TRIM(IFERROR(SPLIT(C2:C, ","))), ))),,999^99)),,999^99))
 , " ", "|")), TRIM(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IFERROR(IF(
 SPLIT(B2:B, ",")<>"", "♠"&A2:A&"♦"&TRIM(IFERROR(SPLIT(B2:B, ","))), ))),,999^99)),,999^99),
 "♠")))&" - CHECK", )<>"", IF(REGEXMATCH(TRIM(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IFERROR(IF(SPLIT(B2:B, ",")<>"", "♠"&A2:A&"♦"&TRIM(IFERROR(SPLIT(B2:B, ","))), )))
 ,,999^99)),,999^99), "♠"))),SUBSTITUTE(TRIM(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IFERROR(IF(
 SPLIT(C2:C, ",")<>"", A2:A&"♦"&TRIM(IFERROR(SPLIT(C2:C, ","))), ))),,999^99)),,999^99)),
 " ", "|")), TRIM(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IFERROR(IF(
 SPLIT(B2:B, ",")<>"", "♠"&A2:A&"♦"&TRIM(IFERROR(SPLIT(B2:B, ","))), ))),,999^99)),,999^99), 
 "♠")))&" - CHECK", ), TRIM(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IFERROR(IF(
 SPLIT(B2:B, ",")<>"", "♠"&A2:A&"♦"&TRIM(IFERROR(SPLIT(B2:B, ","))), ))),,999^99)),,999^99),
 "♠")))), "♦")), "select count(Col1) where Col1 is not null group by Col1 pivot Col2", 0), 
 "offset 1", 0)), INDEX(QUERY(IFERROR(SPLIT(IF(IF(REGEXMATCH(TRIM(TRANSPOSE(SPLIT(QUERY(
 TRANSPOSE(QUERY(TRANSPOSE(IFERROR(IF(SPLIT(B2:B, ",")<>"", "♠"&A2:A&"♦"&TRIM(IFERROR(
 SPLIT(B2:B, ","))), ))),,999^99)),,999^99), "♠"))),SUBSTITUTE(TRIM(QUERY(TRANSPOSE(QUERY(
 TRANSPOSE(IFERROR(IF(SPLIT(C2:C, ",")<>"", A2:A&"♦"&TRIM(IFERROR(SPLIT(C2:C, ","))), )))
 ,,999^99)),,999^99)), " ", "|")), TRIM(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IFERROR(IF(SPLIT(B2:B, ",")<>"", "♠"&A2:A&"♦"&TRIM(IFERROR(SPLIT(B2:B, ","))), )))
 ,,999^99)),,999^99), "♠")))&" - CHECK", )<>"", IF(REGEXMATCH(TRIM(TRANSPOSE(SPLIT(QUERY(
 TRANSPOSE(QUERY(TRANSPOSE(IFERROR(IF(SPLIT(B2:B, ",")<>"", "♠"&A2:A&"♦"&TRIM(IFERROR(
 SPLIT(B2:B, ","))), ))),,999^99)),,999^99), "♠"))),SUBSTITUTE(TRIM(QUERY(TRANSPOSE(QUERY(
 TRANSPOSE(IFERROR(IF(SPLIT(C2:C, ",")<>"", A2:A&"♦"&TRIM(IFERROR(SPLIT(C2:C, ","))), )))
 ,,999^99)),,999^99)), " ", "|")), TRIM(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IFERROR(IF(SPLIT(B2:B, ",")<>"", "♠"&A2:A&"♦"&TRIM(IFERROR(SPLIT(B2:B, ","))), )))
 ,,999^99)),,999^99), "♠")))&" - CHECK", ), TRIM(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(
 TRANSPOSE(IFERROR(IF(SPLIT(B2:B, ",")<>"", "♠"&A2:A&"♦"&TRIM(IFERROR(SPLIT(B2:B, ","))), )))
 ,,999^99)),,999^99), "♠")))), "♦")), "select count(Col1) where Col1 is not null group by Col1 
 pivot Col2", 0), 1, ), ), " ", "♀")),,999^99))), " ", ", "), "♀", " "))

0

Related Topic