Forum Discussion
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 ...
- Hi,
Just a follow-up question to this.
I was wondering if the output in cell C2 instead of showing Match, could in fact show the matching citation itself.
E.g. in row 2, there is a match between value in column 1 (CN117860385A) and column 2. In column C, the output would be the matching citation: CN117860385A as opposed to "Match".
many many thanksHow about
=LET(a, TEXTSPLIT(A2, "|"), b, TEXTSPLIT(B2, "|"), IFERROR(FILTER(a, ISNUMBER(XMATCH(a, b))), ""))
Brilliant, thank you very much for your help.
I have one final question to seek support on.
Is it possible to have a formula in column F which is able to identify if the values in column E (matching citations) are present in column A (original patents). If so, they output the term "Delete" in the corresponding row in column F. An e.g. would be in column E US1111 is a matching citation. The formula would search column A for US1111 (A28) and if present would output Delete in column F (F28 in the example below).
