Excel/Google sheets - duplicating array and extending by value from different array-CodePudding

I have some problem with extending some array by values. The easiest way to explain will be show some example, so:

A       B       C
==================
A       11      A
B       22      B
C       33      C
                11
                A
                B
                C
                22
                A
                B
                C
                33

Of course I can do sth like below:

=({A1:A3;B1;A1:A3;B2;A1:A3;B3})

But it's not flexible at all.

The main problem is that I want to filter some table by value:

A       B       filter key
==================
A       11      11
B       11      33
B       33      
B       33      
C       33
F       44
G       55

So I want to get all A column filtered by filter key, but I want also sum of results. So in above example filtering by 11 is A,B by 33 is B, C. Final result should be ABBC

And yeah, this is possible to do like below:

=({UNIQUE(FILTER(A1:A10;B1:B10=C1));UNIQUE(FILTER(A1:A10;B1:B10=C2))})

Do you have any idea?

Have a nice Sunday!

CodePudding user response：

=INDEX(FLATTEN(SPLIT(JOIN("×", TEXTJOIN("×", 1, A2:A)&"×"&FILTER(B2:B, B2:B<>"")), "×")))

=FILTER(A2:A, REGEXMATCH(B2:B&"", TEXTJOIN("|", 1, C2:C&"")))

update:

=FILTER(INDEX(UNIQUE(A:B);;1);
 REGEXMATCH(INDEX(UNIQUE(A:B);;2)&""; TEXTJOIN("|"; 1; ""&C:C)))