1

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?

1
  • 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

  • Is it possible to do the above function without copying the formula? My sheet gets data from a form. Not essential to change but would be handy. – littlecoder Jun 13 at 16:26
  • in this state no, but it could be done – user0 Jun 13 at 19:57
  • @littlecoder answer updated – user0 Jun 13 at 20:24

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.