Reputation: 1778

Join range of row using arrayformula

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 ?

Upvotes: 0

Answers (3)

MattKing

Reputation: 7773

One more possibility that I learned from player0 and surprised he didn't suggest...

=ARRAYFORMULA(SPLIT(TRANSPOSE(SUBSTITUTE(TRIM(QUERY(QUERY(A2:B&{"|",CHAR(10)},"select MAX(Col2) where Col1<>'|' group by Col2 pivot Col1"),,100)),CHAR(10),",")),"| ",0))

Upvotes: 2

z..

Reputation: 12873

You could also try:

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

Upvotes: 2

player0

Reputation: 1

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), "×")), ",$", ))

Upvotes: 2

Join range of row using arrayformula

Answers (3)

Related Questions