Search code examples
regexgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query

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

Example

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.

Solution

  • 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))), ", ", ","), ",$", )})

    enter image description here