Thread: Concatenate multiple cells based on separate identical cells

1. Concatenate multiple cells based on separate identical cells

Hello, thank you for reading. I am in need of a formula to help me with excel 2010. I need to concatenate a column based on the data in another column.

I work for a volunteer organization and we are sent a spreadsheet with the names of our canvassers and their assigned routes. Unfortunately if a canvasser has more than one route they appear multiple times on the spreadsheet.

So, the A column will have the names and the D column will have their assigned routes. It will look something like this:

A1 Joe smith D1 route 1
A2 Joe smith D2 route 2
A3 Joe smith D3 route 3
A4 Bob brown D4 route 4
A5 Tina green D5 route 5
A6 George white D6 route 6
A7 George white D7 route 7
A8 Nancy blue D8 route 8
A9 Bill orange D9 route 9
A10 Bill orange D10 route 10
A11 Bill orange D11 route 11
A12 Bill orange D12 route 12

I need a formula that will automatically check the A column for identical names and then concatenate all of the corresponding cells in the D column. Also removing the rows that are no longer needed.

So my above example would turn in to this:

A1 Joe smith D1 route 1, route 2, route 3
A2 Bob brown D2 route 4
A3 Tina green D3 route 5
A4 George white D4 route 6, route 7
A5 Nancy blue D5 route 8
A6 Bill orange D6 route 9, route 10, route 11, route 12

I know it probably involves an if statement of some sort as well as the concatenate function, but for the life of me I can not figure it out or find a similar thread.

Thank you for your time and your help. I hope I have provided enough information. If there are any questions please feel free to ask!

Have a great holiday,
Colin

2. I'm sure there is a better UDF that'll handle this.

I tried a few approaches. There should be a built-in function that does this, I think.

See what you think of the options in the attached.

3. Thank you so much for your effort. I will give this a try when I get to work. Take care

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•