Forum Discussion
Automate: Find matching pairs between 2 columns with comma separated values & then vlookup + delete
zchahin
It could be done using FILTER function to sort out first the returned values in column D, then using the ISNUMBER and MATCH function from Column D, to identify which Patent Numbers was matched on column D.
formula for E2=LET(filteredValues, FILTER($D$2:$D$100, $D$2:$D$100<>""),IF(ISNUMBER(MATCH(A2, filteredValues, 0)), "DELETE", ""))
Sample file: zchahin.xlsxIf you're using an older version of excel
you'll need to add another column to achieve this, (or this could also be done by creating name range)array formula in E2:
=IFERROR(INDEX($D$2:$D$100, SMALL(IF($D$2:$D$100<>"", ROW($D$2:$D$100)-ROW($D$2)+1), ROW(1:1))), "")formula in F2:
=IF(ISNUMBER(MATCH(A2, $E$2:$E$100, 0)), "DELETE", "")
zchahin
It could be done using FILTER function to sort out first the returned values in column D, then using the ISNUMBER and MATCH function from Column D, to identify which Patent Numbers was matched on column D.
formula for E2
Sample file: zchahin.xlsx
you'll need to add another column to achieve this, (or this could also be done by creating name range)
array formula in E2:
=IFERROR(INDEX($D$2:$D$100, SMALL(IF($D$2:$D$100<>"", ROW($D$2:$D$100)-ROW($D$2)+1), ROW(1:1))), "")
formula in F2:
=IF(ISNUMBER(MATCH(A2, $E$2:$E$100, 0)), "DELETE", "")
Excellent - thank you very much for a simple solution to my problem.
I wanted to clarify how to adjust the formula in column D so that if there are comma separated values in column C (spreadsheet initially attached contained single patent IDs in column C), it will return in column D the ID of the matching patent (between column B and C).
E.g. in the screenshot below citation FR2845889A1 is a match between column B and C but is not displayed in cell D10 (Column D formula you have provided works great when single citation is present in column C, which we are checking for a match in column B).
=XLOOKUP(TRUE,ISNUMBER(XMATCH(TEXTSPLIT(C2,"|"),TEXTSPLIT(B2,"|"))),TEXTSPLIT(C2,"|"),"")
Does this formula return the intended result?
Works perfectly, thank you!
Only issue is my formula in column E no longer recognises the values in column D and therefore does not identify that they are present in column A and output delete in the corresponding row in corresponding column E (please see above).
Very grateful for your help so far.
