Comparing two columns with comma separated string values (match) and output: matching string/text

[mchina1]

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 row in column B. E.g. in row 2, there is a match between value in column 1 (US1111) and column 2.

Ideally the output in column C would be US1111.

At the moment, I have created the following: =IF(OR(ISNUMBER(XMATCH(TEXTSPLIT(A2, "|"), TEXTSPLIT(B2, "|")))), “CYCLIC”, "") but my output is a generic term "CYCLIC" to outline matching string value(s) whereas I would like to know the actual ID/string that is present in both column A and B (i.e US1111).

This would ideally work with multiple comma separated values in column A and B

many thanks

Book1
	A	B	C
1	Backward Citations	Forward Citations	Match?
2	US435 | US756 | US2305 | US1111	US1111
3	US435 | US433 | US1012	US1012
4	US878 | JP545 | EN987	US777
5	JP233 | EN435	US666
6	US123	US555
7	US1111 | US1018	US1018
8	US4777 | EN768 | JP747	EN747 | US377
9	US4777 | EU23331 | US233	JP5444
10	EP0239409A1|EP0291292A2|EP0595291A1|FR2845889A1	EP1255403A2|EP1632181A1|FR2845889A1
Sheet1

 

[hagia_sofia]

Hello, as TEXTSPLIT is not available in Excel 2021 I presume that you are working in Excel 365; if it is so, please test this:

=MAP(A2:A10,B2:B10,LAMBDA(x,y,
LET(
a,TRIM(TEXTSPLIT(x,"|")),
b,TRIM(TEXTSPLIT(y,"|")),
TEXTJOIN(" | ",,XLOOKUP(b,a,a,"")))))

 

[kvsrinivasamurthy]

UDF in C2 copied down

=GetMatch(B2,A2,"|")

For 2021 version UDF code.

Function GetMatch(cri As String, Rng As Range, delim As String)
Dim M, temp$, T&
M = Split(cri, delim)
For T = 0 To UBound(M)
If InStr(1, Rng, Trim(M(T))) > 0 Then
temp = temp & delim & " " & M(T)
End If
Next T
If temp <> "" Then GetMatch = Mid(temp, 2) Else GetMatch = ""

End Function

How to Use UDF code:
In the developer tab click--> Visual Basic
VB window opens
Insert--> Module
Paste the code.
Close the VB window.
Now UDF is available in Function List
This function can be used like other functions in excel.
Save file as .xlsm

 

[mchina1]

=MAP(A2:A10,B2:B10,LAMBDA(x,y, LET( a,TRIM(TEXTSPLIT(x,"|")), b,TRIM(TEXTSPLIT(y,"|")), TEXTJOIN(" | ",,XLOOKUP(b,a,a,"")))))

Thank you! Simple solution.

One final question,
My main problem (column E) is I want to have a way of identifying if the value in column D (i.e US 1111) is present in column A (list of original patent IDs). If so, I wanted an automated quick method of highlighting the corresponding row number in column A containing the matching citation in column D. The intention is to delete these matching patent IDs (i.e delete row 22 US1012).



Overall example,

D2 identifies US 1111 is present in both B2 and C2 and lists the matching citation (US1111)
E2 would search column A for value D2 and if present, would output "Delete" in the corresponding row (column E). E.g. E22 in the screenshot attached.

I have tried the following but it does not seem to work:

=LET(
filteredValues, FILTER($D$2:$D$100, $D$2:$D$100<>""),
IF(ISNUMBER(MATCH(A22, filteredValues, 0)), "DELETE", ""))

Any help is appreciated.

zchahin (1).xlsx
	A	B	C	D	E
1	Patent Number	Backward Citations	Forward Citations	Matching Citations	Delete
2	US1000	US435 | US756 | US2305 | US1111	JP233 | EN435| US1111	US1111
3	US1001	US435 | US433 | US1012	US1012
4	US1002	US878 | JP545 | EN987	US777
5	US1003	JP233 | EN435	US666
6	US1004	US123	US555
7	US1005	US1111 | US1018	JP254 | US435| US1018	US1018
8	US1006	US4777 | EN768 | JP747	EN747 | US377
9	US1007	US4777 | EU23331 | US233	JP5444
10	US2013244820A1	EP0239409A1|EP0291292A2|EP0595291A1|FR2845889A1	EP1255403A2|EP1632181A1|FR2845889A1	FR2845889A1
11	US1009
12	US1010
13	US1011
14	US1012
15	US1013
16	US1014
17	US1015
18	US1016
19	US1017
20	US1018
21	US1019
22	US1111
Sheet1

Cell Formulas
Range	Formula
D2:D10	D2	=XLOOKUP(TRUE,ISNUMBER(XMATCH(TEXTSPLIT(C2,"|"),TEXTSPLIT(B2,"|"))),TEXTSPLIT(C2,"|"),"")
E2:E22	E2	=LET( filteredValues, FILTER($D$2:$D$100, $D$2:$D$100<>""), IF(ISNUMBER(MATCH(A2, filteredValues, 0)), "DELETE", "") )
D11:D22	D11	=IF(ISNUMBER(SEARCH(C11, B11)), C11, "")

Many thanks

 

[Fluff]

Change the formula in col D to

=LET(a,TRIM(TEXTSPLIT(C2,"|")),XLOOKUP(TRUE,ISNUMBER(XMATCH(a,TRIM(TEXTSPLIT(B2,"|")))),a,""))

and then your formula in E should work.

 

[mchina1]

Brilliant - thank you very much!

One final question:

Is there a way to achieve the following below which is related to the problem above.:

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).

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.

zchahin.xlsx
	A	B	C	D
1	Patent Number	Backward Citations	Forward Citations	Backward citations minus match
2	US1000	US435 | US756 | US2305 | US1111	JP233 | EN435| US1111	US435 | US756 | US2305
3	US1001	US435 | US433 | US1012	US1012	US435 | US433
4	US1002	US878 | JP545 | EN987	US777	US878 | JP545 | EN987
5	US1003	JP233 | EN435	US666
6	US1004	US123	US555
7	US1005	US1111 | US1018	JP254 | US435| US1018
8	US1006	US4777 | EN768 | JP747	EN747 | US377
9	US1007	US4777 | EU23331 | US233	JP5444
10	US1008	EP0239409A1|EP0291292A2|EP0595291A1|FR2845889A1	EP1255403A2|EP1632181A1|FR2845889A1
Sheet1

Many many thanks!

 

[Fluff]

As this is now a significantly different question, it needs a new thread.
Thanks