Forum Discussion

Brass Contributor

Jan 11, 2022

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

Sample excel sheet 3.xlsx9 KB

BI & Data Analysis

excel

Formulas and Functions

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

Sample excel sheet 3.xlsx10 KB

6 Replies

SergeiBaklan
Diamond Contributor
Jan 11, 2022
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) ) )
Sample excel sheet 3.xlsx10 KB
- Riny_van_Eekelen
  Platinum Contributor
  Jan 11, 2022
  SergeiBaklan Interesting!! Though I went for PQ (as attached).
  
  Sample excel sheet 3.xlsx19 KB
  - Arun Chandramouli
    Brass Contributor
    Jan 11, 2022
    Thanks SergeiBaklan and Riny_van_Eekelen !..Really appreciate your help...
SergeiBaklan
Diamond Contributor
Jan 11, 2022
Arun Chandramouli

Is Power Query an option?