Using an Array Formula with Filter in Sheets

Question

I have a Sheet with a database of names, the group they are in, and the color they have earned (that's the short explanation). It looks like this:

I want to create a tab where the colors for each group are placed in one cell with a comma in between, like this:

I can get this output by using this formula [in B2 on the output sheet]:

=IF(A2:A<>"",JOIN(", ",FILTER(Sheet1!C2:C,Sheet1!B2:B =A2)),"")

I would like to take this formula above and turn it into an Array so that it will automatically apply down the whole column as groups are added or taken off the input database. I tried using this formula in B1, but the condition in the filter function, meant to apply to each row, is just outputting everything.

={"Colors"; ARRAYFORMULA(IF(A2:A<>"",JOIN(", ",FILTER(Sheet1!C2:C,Sheet1!B2:B =A2:A)),""))}

Suggestions to make the Array Formula output like shown in the second picture? TIA

EDIT: In the actual database, there is several columns of data for each person (not just color) and I want to be able to return the array format for each. So, on the output tab there might be an array of colors for each group in Col B, but then an array of ages in C, heights in D, etc. Here is a link to my example

share a copy / sample of your sheet with an example of the desired output — player0
– player0, Commented Jun 14, 2022 at 23:43

player0 · Accepted Answer · 2022-06-14 23:15:07Z

1

use:

=ARRAYFORMULA(REGEXREPLACE(TRIM(SPLIT(FLATTEN(QUERY(QUERY({B1:B&"×", C1:C&",", ROW(B1:B)}, 
 "select max(Col2) where Col1 <> '×' group by Col3 pivot Col1"),,9^9)), "×")), ",$", ))

answered Jun 14, 2022 at 23:15

player0

131k14 gold badges91 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Teed Ferguson Over a year ago

This works great for the sample, but I just updated my OP to clarify. there are multiple columns of data that I want to put into array format for each group.

player0 Over a year ago

@TeedFerguson like this: i.sstatic.net/asGTM.png

Teed Ferguson Over a year ago

Not quite. I'll add a link to my sample spreadsheet in the OP

Collectives™ on Stack Overflow

Using an Array Formula with Filter in Sheets

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related