excel function

Question

hello, please help me.

i have table like this

30,29,28,27,26,25,24,23,22,21,20,19,18,17	15,16,17,18,19,30,29	28,27,26,25,24,23,22,21,20
1,2,3,8,9,10	1,2,8	3,9,10
5,6,9,11,12,13,15	9,11,12	5,6,13,15
30,29,28,27,26,1,2,3	30,29,27,26,1	28,2,3

column A (left) data text, column B (middle) random data text take from column A. and column C is the rest from A. what excel function that i have to used for column C?

thank you

Answer 1

In C2:

=LET(all, TEXTSPLIT(A2, ","), some, TEXTSPLIT(B2, ","), rest, FILTER(all, ISERROR(XMATCH(all, some)), ""), TEXTJOIN(", ", TRUE, rest))

Fill down.

Answer 2

Hi,

In Power Query, this M code will work

let

Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], 

#"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Combine(List.RemoveMatchingItems(Text.Split([All],","),Text.Split([Criteria],",")),",")) 

in

#"Added Custom"

Hope this helps.

Answer 3

Hi,

In cell C2, enter this formula

=MAP(A2:A5,B2:B5,LAMBDA(x,y,LET(a,TEXTSPLIT(x,,","),TEXTJOIN(",",TRUE,FILTER(a,ISERROR(XMATCH(a,TEXTSPLIT(y,,","))))))))

Hope this helps.

Answer 4

@arthika pusparini

Please enter the below formula in C1

=TEXTJOIN(",",,LET(mn,TEXTSPLIT(A1,","),SRC,TEXTSPLIT(B1,","),LKUP,XLOOKUP(mn,SRC,SRC,"NF"),FILTER(mn,LKUP="NF")))