Forum Discussion
Solved
Help with Excel Formula to get the intersecting codes
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
If 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) ) )
If 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) ) )SergeiBaklan Interesting!! Though I went for PQ (as attached).
- Thanks SergeiBaklan and Riny_van_Eekelen !..Really appreciate your help...
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.
