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

 

[Micron]

Some people do amazing things with formulas but I'm not one of them. I do believe this is doable with vba, like most things are.

That would be, (more or less):
- Split() B2 into an array
- loop array elements with Instr() to see if it exists in C2. If not, move on to next row
- if one exists, then assign B2 to a variable and remove it [Replace() the found element with ""]
- then set D2 to that variable value
- repeat for subsequent rows

This begs the question as to whether or not a value can repeat repeating values:
US435 | US756 | US2305 | US1111 | US1111

OR if there can be multiple pairs:
US435 | US756 | US2305 | US2305 | US1111 | US1111

or both

 

[dreid1011]

Does Office 2021 for Mac have BYCOL, TEXTSPLIT, TEXTBEFORE? I tried looking it up, but it looks like all of them are only on MS 365 for Mac?

 

[hagia_sofia]

Hello, something like this?

=LET(
a,TRIM(FILTERXML("<a><b>"&SUBSTITUTE(B2,"|","</b><b>")&"</b></a>","//b")),
b,TRIM(FILTERXML("<a><b>"&SUBSTITUTE(C2,"|","</b><b>")&"</b></a>","//b")),
TEXTJOIN(" | ",,UNIQUE(FILTER(a,ISERROR(XLOOKUP(a,b,b))))))

 

[dreid1011]

Does Office 2021 for Mac have BYCOL, TEXTSPLIT, TEXTBEFORE? I tried looking it up, but it looks like all of them are only on MS 365 for Mac?

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

 

[dreid1011]

Hello, something like this?

=LET(
a,TRIM(FILTERXML("<a><b>"&SUBSTITUTE(B2,"|","</b><b>")&"</b></a>","//b")),
b,TRIM(FILTERXML("<a><b>"&SUBSTITUTE(C2,"|","</b><b>")&"</b></a>","//b")),
TEXTJOIN(" | ",,UNIQUE(FILTER(a,ISERROR(XLOOKUP(a,b,b))))))

In an effort to learn a little more about FILTERXML, it looks like it may not work for the OP as they are on a Mac.

 

[hagia_sofia]

In an effort to learn a little more about FILTERXML, it looks like it may not work for the OP as they are on a Mac.

View attachment 116092

Thanks, my bad that I did not notice the question should be addressed on a Mac...

 

[dreid1011]

Thanks, my bad that I did not notice the question should be addressed on a Mac...

Not a problem at all. I frequently forget to look before I jump in trying to figure it out.

 

[Micron]

Q: does vba ever care if it's a Mac or PC? Asking 'cause I don't know.
EDIT - that's about vba that does not use wb or sheet or application functions.

 

[dreid1011]

Q: does vba ever care if it's a Mac or PC? Asking 'cause I don't know.
EDIT - that's about vba that does not use wb or sheet or application functions.

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