Jan 11 2022 02:16 AM
Hi All,
Hope you are doing well!.. I am looking to extract the intersecting codes from two columns of data...Please find the data attached ..Can you please help me here...
Thanks,
Arun
Jan 11 2022 02:47 AM
Is Power Query an option?
Jan 11 2022 03:08 AM
SolutionIf with formula
=LET(
a, FILTERXML( "<t><s>"&SUBSTITUTE(A2,",","</s><s>")&"</s></t>", "//s"),
b, FILTERXML( "<t><s>"&SUBSTITUTE(B2,",","</s><s>")&"</s></t>", "//s"),
m, IFNA( XMATCH(a,b), 0 ),
TEXTJOIN(",", 1, FILTER( a, m) ) )
Jan 11 2022 03:31 AM
@Sergei Baklan Interesting!! Though I went for PQ (as attached).
Jan 11 2022 05:23 AM
Jan 11 2022 08:10 AM
You are welcome. With next question it will be great if you specify on which Excel version / platform you are and which type of solution is most preferable (VBA, formulas, Power Query, etc). Thanks.
Jan 11 2022 08:48 AM
Jan 11 2022 03:08 AM
SolutionIf with formula
=LET(
a, FILTERXML( "<t><s>"&SUBSTITUTE(A2,",","</s><s>")&"</s></t>", "//s"),
b, FILTERXML( "<t><s>"&SUBSTITUTE(B2,",","</s><s>")&"</s></t>", "//s"),
m, IFNA( XMATCH(a,b), 0 ),
TEXTJOIN(",", 1, FILTER( a, m) ) )