3

Sample sheet: https://docs.google.com/spreadsheets/d/1AeP0sxDi0-3aaesUdCNTKfricIimjTMFaKO-FX9_g50/edit?usp=sharing

I am trying to find a formula that will group a table on a column and concat the values from all the rows in another column.

For example, if this is my table:

| name  | value   |
|-------|---------|
| one   | alpha   |
| two   | bravo   |
| three | charlie |
| one   | delta   |
| two   | echo    |
| four  | foxtrot |
| two   | golf    |
| three | hotel   |
| four  | india   |

This is what I want the formula to output:

| one   | alpha, delta      |
| two   | bravo, echo, golf |
| three | charlie, hotel    |
| four  | foxtrot, india    |

I wish I could share some formula that gets me close but I can't find anything. I thought maybe this formula but, as you can see from the sample sheet, it does not work.

=ARRAYFORMULA(JOIN(", ", TRANSPOSE(FILTER(B2:B, A2:A = {UNIQUE(A2:A)}))))

My thought was, get a unique list of values in the name column, and then use arrayformula to get a list of values in the value column where the name column equals each value in the unique list. :/

player0
  • 124,011
  • 12
  • 67
  • 124
IMTheNachoMan
  • 5,343
  • 5
  • 40
  • 89

1 Answers1

5

try:

=ARRAYFORMULA(REGEXREPLACE(TRIM(SPLIT(TRANSPOSE(
 QUERY(QUERY({A2:A&"♦", B2:B&","}, 
 "select max(Col2) 
  where Col1 !='' 
  group by Col2 
  pivot Col1"),,999^99)), "♦")), ",$", ))

0

or:

=ARRAYFORMULA(IFNA(VLOOKUP(UNIQUE(A2:A), 
 REGEXREPLACE(TRIM(SPLIT(TRANSPOSE(
 QUERY(QUERY({A2:A&"♦", B2:B&","}, 
 "select max(Col2) 
  where Col1 !='' 
  group by Col2 
  pivot Col1"),,999^99)), "♦")), ",$", ), {1, 2}, 0)))

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • I was trying to achive the same, but I have more columns that need combining ID | ValueB | ValueC | .... ValueX But I can't figure how to make it happen, how would this work if there are more collumns? – Mikec Oct 17 '22 at 21:17
  • @Mikec as the answer for your case would differ I recon to [ask](https://stackoverflow.com/questions/ask) the question – player0 Oct 17 '22 at 21:48
  • @player0 How about this one ? https://stackoverflow.com/questions/76700918/google-sheets-arrayformula-query-function-to-group-and-concat-rows-before-date – Aleister Tanek Javas Mraz Jul 17 '23 at 00:30