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


6 Replies

@Arun Chandramouli 

Is Power Query an option?

best response confirmed by Arun Chandramouli (Contributor)

@Arun Chandramouli 

If with formula

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

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


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

@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.

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