Google Sheets: Arrayformula for summarizing textjoined arrays (query/filter...)

Question

I have a problem in Google Sheets summarizing values from a column in one single cell using an Arrayformula.

I made an easy example how my data looks like (approximately) and how I want to summarize it. In table1 you can see project_members stuffed on different projects. Table2 shows how I want to visualize my data. In column D the unique project_ids are listed, so far so good. Now I want column E to show every member stuffed on the project I can find next to them.

[Example][1] [1]: https://i.sstatic.net/1v1mk.png

I have managed to get what I want using this:

=TEXTJOIN(" , ";1;FILTER(B:B;A:A=D2))

or

=TEXTJOIN(" , ";1;QUERY(A:D;"Select B where A contains '"& D2 &"' ";0))

Now my actual dataset is way bigger than this example and data is beeing added constantly. This is why I need the formula in column E to be automated. I have tried this:

=ARRAYFORMULA(TEXTJOIN(" , ";1;QUERY(A:D;"Select B where A contains '"& D:D &"' ";0)))

But that doesnt work. I´m thankful for help!

Answer 1

solution

Try this unique arrayformula that will take into account added lines (German Notation)

=ARRAY_CONSTRAIN( transpose({transpose(unique(A2:A));arrayformula(trim(query(arrayformula(if(A2:A=transpose(unique(A2:A));B2:B&",";));;9^9)))});counta(unique(A2:A));2)

explanation about the construction

the most important step is in D2 (US notation)=arrayformula(if(A2:A10=transpose(unique(A2:A10)),B2:B10,)), then apply query(,,9*9) to gather all items in each column