2

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.

player0
  • 124,011
  • 12
  • 67
  • 124
Chris Rosendin
  • 305
  • 2
  • 14

1 Answers1

2

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

player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    you're gonna start confusing folks if you keep using INDEX() instead of Arrayformula() just because it's shorter :) – MattKing Aug 28 '21 at 17:56
  • Many thanks @player0! Your solution works great. Would you mind explaining how ^×|^$ and 9^9 work? – Chris Rosendin Aug 30 '21 at 17:08
  • 1
    @ChrisRosendin sure, ^×|^$ means exactly: starts ^ with × or | starts ^ and ends $ with nothing in between (in other words skip blank cells and cells containing × only - remember we join A column with × even if A column contains blank cell, thats why we regex it out from output). – player0 Aug 30 '21 at 18:26
  • 1
    @ChrisRosendin 9^9 means huge number - can be any number you wish it just needs to be larger than total rows of the sheet so if the seet has 10 row you can use 11 instead of 9^9 (=387420489). ,,9^9 is used to join all columns into one single row. see about query smash here: https://i.stack.imgur.com/1840C.png ---- https://stackoverflow.com/a/65435321/5632629 – player0 Aug 30 '21 at 18:26
  • @MattKing exactly :) – player0 Aug 30 '21 at 18:28