Help with Excel Formula to get the intersecting codes

[Arun Chandramouli]

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

[#2]

@Arun Chandramouli 

Is Power Query an option?

[#3 · Accepted solution]

@Arun Chandramouli 

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

[#4]

@Sergei Baklan Interesting!! Though I went for PQ (as attached).

[#5]

Thanks @Sergei Baklan and @Riny_van_Eekelen !..Really appreciate your help...

[#6]

@Arun Chandramouli 

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.

[#7]

Sure @ Sergei Baklan!...I will definitely do that..