5

I need a formula/function to concatenate cell values from one column and multiple rows. The matching criteria is applied to a different column. Here is my example of what I have to do:

Islington | "Bunhill"   | EC2M  
Islington | "Bunhill"   | EC2Y  
Islington | "Bunhill"   | N1  
Barnet    | "Burnt Oak" | HA8  
Barnet    | "Burnt Oak" | NW7  
Barnet    | "Burnt Oak" | NW9  

The end result needs to look like this:

Islington | "Bunhill"   | EC2M, EC2Y, N1  
Barnet    | "Burnt Oak" | HA8, NW7, NW9 

Basically, I need to remove all duplicates from the second column, but save the data from the third column that is paired with each of the duplicates, and concatenate it in one cell.

pnuts
  • 58,317
  • 11
  • 87
  • 139
jimmy334
  • 61
  • 1
  • 2
  • You could add a user defined menu that runs a program when you choose the menu item. The code is written in JavaScript and you'd need to use a loop. Do you want the original data saved, or deleted? You'd need to learn some programming for this solution. – Alan Wells Jan 27 '15 at 19:14
  • Will "Bunhill" always be matched to "Islington"? And the same for all other possibilities? Is the first column really immaterial to the match? That effects how the processing needs to be done. – Alan Wells Jan 27 '15 at 19:41
  • I need the original data saved. I don't have experience with Google Spreadsheets at all, except for the parts where things match with Excel. I'm a student and I have experience in programming on C++/C# and Java. I was hoping for a quicker solution with no requirements to read all the Google Spreadsheets Scripts documentation in order to write that javascript code. – jimmy334 Jan 27 '15 at 19:42
  • Yes Bunhill will always be matched, I'm sorry I just copied more columns than needed. – jimmy334 Jan 27 '15 at 19:43

3 Answers3

6

You can go through a process of steps using functions. Start with the UNIQUE function. Put this in a cell where it is convenient to list all the unique values of column B:

=UNIQUE(B:B)

Gets all the unique values in column B. Google Support - Unique Function

The result from the UNIQUE function will look like this:

unique function

Now that you have all the unique values from column B, you can use the FILTER function to retrieve all the rows that match that unique value.

=FILTER(D1:D6, B1:B6=A8)

The FILTER function lists all the results down the column, but you can use the CONCATENATE function to avoid that.

Results of FILTER function:

FILTER function

Results of CONCATENATE:

Concatenate function

You will need to adjust the FILTER function to now use column D, rather than column C.

=CONCATENATE(FILTER(D1:D6, B1:B6=A8))

This solves the problem of getting data in multiple rows, but now there is no separator between the values.

To get around that problem, you can create a fourth column with a function that adds a comma to the end:

Added a column with a coma

There is a problem with an extra comma on the end, which you can get rid of with the LEFT function:

Left Function

Alan Wells
  • 30,746
  • 15
  • 104
  • 152
1

If not required too often it is quite practical without a script. Assuming EC2M is in C2, D1 is blank, and your data is sorted, in D2:

=if(B1=B2,D1&", "&C2,C2) 

and in E2, both formulae copied down to suit:

=B2=B3 

Select all, Ctrl+c, Edit, Paste special, Paste values only over the top and filter to select and delete rows with TRUE in ColumnE.

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • The formula =if(B1=B2,D1"&C2, C2) for some reason it doesnt work. – jimmy334 Jan 27 '15 at 19:27
  • The next thing is, that if I paste the formula on the second row, it will become something like this =if(B2=B3,D2"&C3,C3). In this case the value that is in D2 will be concatenated with C3. Basically, it would make the concatenation on each row and it won't add all of them, just the first one and the second one. I need all the values from column C that match the same duplicate value from B to be concatenated into one cell. And then continue for the rest of the different duplicated values from column B and concatenate their respective values in column C, as shown in the example result. – jimmy334 Jan 27 '15 at 19:38
  • I'm sorry, I've just tested it and I see your idea now. It might actually work. I need to test it on my original spreadsheet. (btw, any Ideas on how can I remove the first "," ? – jimmy334 Jan 27 '15 at 19:46
  • The problem I'm getting now, is that - when it gets to the last duplicated cell in the B column, because the "if" checks if it is the same with the next one (and it isn't), it only shows the the value from the last cell and it's not concatenated with the rest. – jimmy334 Jan 27 '15 at 20:00
  • I made a mistake while translating that formula on my original spreadsheet. >.< I fixed it, and it works like a charm, thank you so much! – jimmy334 Jan 27 '15 at 20:12
0

TEXTJOIN has 2 advantages over CONCATENATE: (1) customizable delimiter, and (2) can skip blanks.

Example:

AA | BB | CC | __ | EE

=TEXTJOIN(",",TRUE,A1:E1)

Will produce: AA,BB,CC,EE

(skipping the blank DD and putting a comma in between every term except last)

TomJones999
  • 775
  • 2
  • 13
  • 30