I would like to filter out Column B (Code strings combinations separately in rows in a long list) using whole text string from Column A.
What I did is filter out table manually in Column B for couple of strings, but in future I will be getting lots of strings and I need to do that swiftly and efficiently.
In Column C are the results of all the combinations from Column B per row, and there I need values as they are, so no unique values. Because if you look at Column B and combinations, there some of them that have more than single code combined (and that is fine), exact combinations I need to have in results column.
In Column B and C you can see "basis values" in rows, they are predifined and should be there.
I tweaked a bit using this function:
=FILTER(Table1;ISNUMBER(SEARCH("CSxxxFX_CE1,CE2_Dxx_Exx_FB0_FMx_GP3_IT0_JH0_LB0_MV1_MW0_NZ0_OZ0_QS0";Table1[Code String])))
But it cant feed whole string inside actually...only parts. Similar I did in Power Query....
I would accept VBA solution too..
The Data and workbook are on the link below.
Formula in E2
:
=VSTACK(B2:B3,UNIQUE(FILTER(B4:B374,BYROW(IFERROR(SEARCH(","&TEXTSPLIT(A2,{"_",","})&",",","&B4:B374&","),0),LAMBDA(a,SUM(a))),"None Found")))
TEXTSPLIT()
and VSTACK()
are rather new functions currently available to users in the ms365 insiders BETA-channels.
Without access one could use the following in C4
(assuming you will bring over B2:B3
statically:
=UNIQUE(FILTER(B4:B374,LET(X,TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A2,",","_"),"_","</s><s>")&"</s></t>","//s")),MMULT(IFERROR(FIND(","&X&",",","&B4:B374&","),0),SEQUENCE(COUNTA(X),,,0))),"None Found"))