Comparing (comma seperated) string values between two columns

[mchina1]

Hi! and thank you in advance

Is there a way to achieve the following below which is related to a problem I have found when reviewing citation data:

Essentially, I want to find the matching citation/string value 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 (between col B and C). If there is no matching citation between column B and C, then the output in column D is essentially identical to column B (as there are no matching citations to remove).

Have inputed some desired output in column D below.
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

 

[mchina1]

If you're able to use these functions, then perhaps this:

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

Cell Formulas
Range	Formula
F2:F10	F2	=LET(s,TAKE(BYCOL(TRIM(TEXTSPLIT(B2,"|")),LAMBDA(c,IF(ISNUMBER(SEARCH(c,C2)),SUBSTITUTE(B2,c,""),B2))),1,-1),TRIM(TEXTBEFORE(TRIM(s)&"@","|@",,,,s)))

Brilliant - works perfectly and thank you very much for your help!

 

[dreid1011]

Brilliant - works perfectly and thank you very much for your help!

You're welcome, and happy to help.

 

[felixstraube]

If @mchina1 can use LAMBDA, TEXTBEFORE and so forth then it is the 365 version not 2021.

 

[Eric W]

Probably not, though it may seem VBA for Mac is less comprehensive than VBA for Windows.

One significant difference is that VBA for Mac does not support the Scripting.Dictionary.

 

[FormR]

HI, here's another way to do it if interested (assuming XL365 is being used).

Book1
	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	JP233 | EN435
6	US1004	US123	US555	US123
7	US1005	US1111 | US1018	JP254 | US435| US1018	US1111
8	US1006	US4777 | EN768 | JP747	EN747 | US377	US4777 | EN768 | JP747
9	US1007	US4777 | EU23331 | US233	JP5444	US4777 | EU23331 | US233
10	US1008	EP0239409A1|EP0291292A2|EP0595291A1|FR2845889A1	EP1255403A2|EP1632181A1|FR2845889A1	EP0239409A1 | EP0291292A2 | EP0595291A1
Sheet1

Cell Formulas
Range	Formula
D2:D10	D2	=LET(TS,TRIM(TEXTSPLIT(B2,"|")),TEXTJOIN(" | ",1,FILTER(TS,ISNA(MATCH(TS,TRIM(TEXTSPLIT(C2,"|")),0)))))