Search code examples
regexgoogle-sheetsflattengoogle-query-languagetextjoin

How to collapse sheet by pivoting rows into csv data

I have a set of data where an account id can have multiple rows of country. I'm looking for an array function that will give me a unique list of accounts with the countries in the second column as csv values e.g. country1,country1,country3.

If I unique the accounts, this query will do it per row but I'm really looking for an array so I don't have to maintain it as the number of rows grows.

enter image description here

=TEXTJOIN(",",1,UNIQUE(QUERY(A:B,"select B where A = '"&D2&"'",0)))

I have a sample sheet here.

Solution

  • try:

    =INDEX(REGEXREPLACE(TRIM(SPLIT(FLATTEN(QUERY(QUERY(
 IF(A2:A="",,{A2:A&"×", B2:B&","}),
 "select max(Col2)
  where not Col2 matches '^×|^$'
  group by Col2
  pivot Col1"),,9^9)), "×")), ",$", ))

    enter image description here