Forum Discussion
Comparing two columns with comma separated string values (match)
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 thanks
How 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).
Brilliant. Thank you. I am very grateful.
One additional question to seek your help on (this is my final question).
What kind of formula could be used in column C to output: all the unique values from the corresponding cell in column A, minus the matching citation identified between column A and B . An e.g would be in the table above, Row 3: column C would output a single patent WO20221... as CN117860.. is a citation present in both column A and B. Therefore the output in column C is all values from column A but excluding the citation that are present in both column A and B.
many many thanks again.
I appreciate your help.
