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

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.stack.imgur.com/1v1mk.png

I have managed to get what I want using this:

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

=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!

CodePudding user response：

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

CodePudding user response：

See my newly added sheet ("Erik Help") and this formula there:

=ArrayFormula({A1:B1;REGEXREPLACE(TRIM(SPLIT(FLATTEN(QUERY(QUERY({A2:A&"~"\B2:B&","}; "Select MAX(Col2) where Col2 IS NOT NULL GROUP BY Col2 PIVOT Col1");; 9^9));"~"));"[,\s] $";"")})

This formula is complex, and so I invite anyone who is interested to take it apart and put it back together for understanding.