Join range of row using arrayformula-CodePudding

I have a range of data on column A and B. In Column D , i have a reference for the shotID. I want to make a list for the artist involved for specific shotID.

In E2 i use this :

=JOIN( "," , FILTER($B$2:$B, $A$2:$A= D2))

then copy down to E3,E4. It works as i expected, but i want to do it using array formula. So only use single formula in E2 and that doesn't work that simple :

=arrayformula( JOIN( "," , FILTER($B$2:$B, $A$2:$A= D2:D4)) )

How can i do this ?

CodePudding user response：

take:

=ARRAYFORMULA(REGEXREPLACE(TRIM(SPLIT(SUBSTITUTE(
 FLATTEN(QUERY(TRANSPOSE(QUERY(QUERY(SPLIT(
 FLATTEN(A2:A&"×"&B2:B&","&"×"&B2:B), "×"), 
 "select Col1,max(Col2) where Col2 is not null group by Col1 pivot Col3"), 
 "offset 1", 0)),,9^9)), " ", "×", 1), "×")), ",$", ))

CodePudding user response：

You could also try:

={unique(A2:A),arrayformula(transpose(substitute(trim(query(if(A2:A<>transpose(unique(A2:A)),,B2:B),,9^9))," ",", ")))}