4

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.

player0
  • 124,011
  • 12
  • 67
  • 124
dean2020
  • 645
  • 2
  • 8
  • 25

2 Answers2

3

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

player0
  • 124,011
  • 12
  • 67
  • 124
0

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

enter image description here

JPV
  • 26,499
  • 4
  • 33
  • 48
  • Thank you for this, it works for the short example, but when I apply this formula to long lists I get this error 'Function ARRAY_ROW parameter 2 has mismatched row size. Expected: 5704. Actual: 1.' I make sure both column A and B have the same length, but that doesn't solve the issue. – dean2020 Oct 15 '19 at 21:40