Forum Discussion

zchahin's avatar
zchahin
Copper Contributor
Jul 27, 2024
Solved

Comparing two columns with comma separated string values (match)

Hi,    I currently have two columns of reference data. Each cell contains comma separated values.  My aim is to find a match between any of the patent numbers in column A within the corresponding ...
excel
Excel on Mac
Formulas and Functions
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Jul 27, 2024

    zchahin 

    In C2:

    =IF(OR(ISNUMBER(XMATCH(TEXTSPLIT(A2, "|"), TEXTSPLIT(B2, "|")))), "Match", "")

zchahin's avatar
zchahin
Copper Contributor
Aug 28, 2024

Hi HansVogelaar , thank you for a swift response. 

 

Attached below the dummy data.

 

Essentially, I want to find the matching citation between column B and column C. So, between B2 and C2 this would be US1111. Ideally, my output in D2 would be B2 minus US1111 so overall my intention is to identify if there is a matching citation in both B and C. If there is, the output in column D is all the unique string values in B minus the identified matching citation. If there is no matching citation between column B and C, then the output in column D is essentially identical to column B (as no citations to remove). 

 

Have attached dummy data with intended output in column D, which hopefully we can write a formula for.

 

E.g. Row 2: B2 and C2 have a matching citation (US1111). Output D2: all the citations in B2 but removed US1111

Row 3: B3 and C3 have a matching citation (US1012). Output D3: all the citations in B3 but removed US1012

Row 4: B4 and C4 have no matching citations. Output D4: essentially all the same values in B4 as no citation to remove. 

HansVogelaar's avatar
HansVogelaar
MVP
Aug 28, 2024

zchahin 

Thanks. Try

 

=LET(a, TEXTSPLIT(SUBSTITUTE(B2, " ", ""), "|"), b, TEXTSPLIT(SUBSTITUTE(C2, " ", ""), "|"), TEXTJOIN(" | ", TRUE, FILTER(a, ISERROR(XMATCH(a, b)))))

Resources