List unique values in 1 column and concatenate corresponding values in other column

Question

Column C should contain the unique values from column A, and in column D it should combine the corresponding values in column B as shown in the attached example.

Ideally using a formula with QUERY or ARRAYFORMULA (so not having to drag down the formula), since this list is generated automatically and can be very long (10000+ rows)

I had created a google script to do this, but hope to be able to do this with formula because of performance reasons.

Answer 1

try like this:

=ARRAYFORMULA({SORT(UNIQUE(FILTER(A:A, A:A<>""))),
 REGEXREPLACE(SUBSTITUTE(TRIM(TRANSPOSE(QUERY(QUERY(QUERY(
 IF(A:A<>"", {A:A, B:B&","}, ), 
 "select max(Col2) where Col1 !='' group by Col2 pivot Col1"), 
 "offset 1", 0),,999^99))), ", ", ","), ",$", )})

Answer 2

In C1, try this formula:

=arrayformula(regexreplace({unique(A1:A), trim(transpose(query(if((transpose(unique(A1:A))=A1:A)*len(A1:A),B1:B&",",),,50000)))},",$", ))