Google Spreadsheets - Join or Concatenate text from multiple rows that have matching values in one of the columns

2
votes

As the title says, I need to join or concatenate text from multiple rows that have matching values in one of the columns. (Using latest Firefox, Widows 7).

In Sheet1 I have a table that looks like this (sorry if it looks messy - I don't know how to do a neat table on SO):

David | 1 | Blue
Steve | 1 | Green
James | 2 |
Chris | 1 |
Steve | 3 | Blue
Jack | 1 |
Ben | 1 |
James | 1 |
Jack | 2 | Yellow
George | 4 |

In Sheet2, I would like a table that looks like this:

David |1| Blue
Steve |4| Green & Blue
James |3|
Chris |1|
Jack |3| Yellow
Ben |1|
George |4|

Column A show unique values (names) and column B shows the sum for that name.

I have successfully generated columns A and B but I'm having difficulty generating the third column which should return 'Green & Blue' for Steve and 'Yellow' for Jack etc.

Does anyone know how to do this? Your help would be much appreciated.

I have generated Sheet2 columns A and B with the follwing formulas:
Column A: =UNIQUE(Sheet1!A:A)
Column B: =SUMIF(Sheet1!A:A,A1,Sheet1!B:B).

Here is the spreadsheet: https://docs.google.com/spreadsheet/ccc?key=0Avg34WusBUlRdGY1X3BSRlYxaWVkSmo3eVQzYlNvMXc


Thanks for looking.

1
google-sheetsformula

1 Answers

1
votes

Use the JOIN function combined with the FILTER function So in Column C:

=JOIN(" & ",filter(Sheet1!C:C,Sheet1!A:A=A1))

Hope it helps.