CODEWITHSUNDEEP
splitgoogle-sheets-formulatrimflattengoogle-query-language
Aleister Tanek Javas Mraz
Aleister Tanek Javas Mraz

Reputation: 517

Group everything by Column A and have Column B be a comma-delimited list of values

I've got a Google Sheets worksheet with data like this:

Product Attribute
Product A Cyan
Product B Cyan
Product C Cyan
Product A Magenta
Product C Magenta
Product B Yellow
Product C Yellow
Product A Black
Product B Black

What I'd like to do group everything by Column A and have Column B be a comma-delimited list of values that share Column A in common, like so:

Product Attribute
Product A Cyan,Magenta,Black
Product B Cyan,Yellow,Black
Product C Cyan,Magenta,Yellow,Black

Query ? Arrayformula ? Both ?

Upvotes: 1

Views: 908

Answers (2)

player0
player0

Reputation: 1

try:

={UNIQUE(FILTER(A:A, B:B<>"")), BYROW(UNIQUE(FILTER(A:A, B:B<>"")), 
 LAMBDA(aa, JOIN(", ", FILTER(B:B, A:A=aa))))}

enter image description here

Upvotes: 2

player0
player0

Reputation: 1

use:

=ARRAYFORMULA(REGEXREPLACE(TRIM(SPLIT(FLATTEN(
 QUERY(QUERY({ROW(A1:A), A1:A&"×", B1:B&","}, 
 "select max(Col3) where not Col2 starts with '×' 
  group by Col1 pivot Col2"),,9^9)), "×")), ",$", ))

enter image description here

Upvotes: 1

Related Questions