Nov 10 2022 07:08 PM
Hi,
I have sample data as below, in the 3rd column would want to filter out all text that matching between 1st and 2nd column. Is there any formula function that can help to achieve this?
Appreciate everyone advise. TQ.
1st column | 2nd column | matching |
a,b,c | a,c,z | a,c |
c,z.d | c,z | c,z |
d,e,e | e,d,k | d,e |
b,a,c | a,b | a,b |
a,b,c | a,c,z | a,c |
c,z.d | c,z | c,z |
d,e,e | e,d,k | d,e |
b,a,c | a,b | a,b |
a,b,c | a,c,z | a,c |
c,z.d | c,z | c,z |
d,e,e | e,d,k | d,e |
b,a,c | a,b | a,b |
Nov 10 2022 07:35 PM
Nov 11 2022 10:51 AM
Solution=TEXTJOIN(",",,UNIQUE(FILTER(TRANSPOSE(TEXTSPLIT(B2,",")),TRANSPOSE(ISNUMBER(SEARCH(TEXTSPLIT(B2,","),A2))))))
An alternative could be this formula.
Nov 13 2022 06:08 PM
Nov 13 2022 06:13 PM
@HZ2506 Oh! That's okay. You may try below formula. Check the attached file.
=LET(x,TOCOL(TEXTSPLIT(A2,{",","."})),y,COUNTIFS(B2,"*"& x &"*"),z,FILTER(x,y),TEXTJOIN(",",TRUE,UNIQUE(z)))
Nov 13 2022 06:16 PM
Nov 11 2022 10:51 AM
Solution=TEXTJOIN(",",,UNIQUE(FILTER(TRANSPOSE(TEXTSPLIT(B2,",")),TRANSPOSE(ISNUMBER(SEARCH(TEXTSPLIT(B2,","),A2))))))
An alternative could be this formula.