CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Teed Ferguson
Teed Ferguson

Reputation: 317

Using an Array Formula with Filter in Sheets

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:

Database

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

Output

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

Upvotes: 0

Views: 942

Answers (1)

player0
player0

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

enter image description here

Upvotes: 1

Related Questions