Search code examples
excelvbaexcel-formulaexcel-365

Filter out parts of text strings using whole String in Excel Table

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.

enter image description here

https://docs.google.com/spreadsheets/d/1nQxYcrj_jnv2xf-QX9hwKCjdWdso2QM7/edit?usp=sharing&ouid=101738555398870704584&rtpof=true&sd=true

Solution

  • Right, if I do understand correctly now; the following might work:

    enter image description here

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