What aggregation function to concatenate string like sum() for number in google sheet?

Question

Basically I would like to convert the following table

Name	Team
A	X
B	X
A	Y
B	Z
C	X
A	Z

to

Name	Team
A	X,Y,Z
B	X,Z
C	X

It is something like sum(B) group by A but for strings rather than numbers

What kind of Google Sheet formula or Query() can do this? Thanks!

Answer 1

=ArrayFormula(
 REGEXREPLACE(
  SPLIT(
   TRANSPOSE(
    QUERY(
     QUERY(
      SPLIT(
       FILTER(A2:A&"♦♥"&B2:B,A2:A<>"")&",",
      "♥"
      ),
      "SELECT MAX(Col2) WHERE Col2 IS NOT NULL GROUP BY Col2 PIVOT Col1",0
     ),,10^7
    )
   ),
   "♦"
  ),
  "^(?:,?\s*)+|(?:,\s*)$|(,)\s*(?:,\s*)*","$1"
 )
)

Answer 2

Use this formula-

={UNIQUE(A2:INDEX(A2:A,COUNTA(A2:A))),BYROW(UNIQUE(A2:INDEX(A2:A,COUNTA(A2:A))),LAMBDA(x,JOIN(",",FILTER(B2:B,A2:A=x))))}

• Here A2:INDEX(A2:A,COUNTA(A2:A)) will return a array of values as well cell reference from A2 to last non empty cell in column A (Assume you do not have any blank rows inside data). If you have blank row, then you have to use different approach. See this post by @TheMaster

• Then LAMBDA() will apply for each unique value return by INDEX() function to gather corresponded values from B column.

• Inside lambda, JOIN() will concat those filtered values into one single string.

• And finally, by {} results will be combined into on array for output.