Concatenate multiple cells based on separate identical cells

[Lolpan]

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

 

[Hui]

Colin

Firstly, Welcome to the Chandoo.org Forums

You will need some VBA to assist you here

Have a look at the Concatif() function
Goto:
http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/

Then search for Concatif,

When you copy teh code you will need to retype the " marks within the code

If you have problems with that I'd suggest posting a sample file here to enable us to give you a more targeted response

 

[jindon]

This should do

Sub test()
    Dim r As Range
    With CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
            .Item(r.Value) = .Item(r.Value) & IIf(.Item(r.Value) <> "", ", ", "") & r(, 4).Value
        Next
        With Range("a1", Range("a" & Rows.Count))
            .ClearContents: .Columns("d").ClearContents
        End With
        [a1].Resize(.Count).Value = Application.Transpose(.keys)
        [d1].Resize(.Count).Value = Application.Transpose(.items)
    End With
End Sub

 

[Lolpan]

Colin

Firstly, Welcome to the Chandoo.org Forums

You will need some VBA to assist you here

Have a look at the Concatif() function
Goto:
http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/

Then search for Concatif,

When you copy teh code you will need to retype the " marks within the code

If you have problems with that I'd suggest posting a sample file here to enable us to give you a more targeted response

Thank you so much for the help. I will give this a try!

 

[Lolpan]

This should do

Sub test()
    Dim r As Range
    With CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
            .Item(r.Value) = .Item(r.Value) & IIf(.Item(r.Value) <> "", ", ", "") & r(, 4).Value
        Next
        With Range("a1", Range("a" & Rows.Count))
            .ClearContents: .Columns("d").ClearContents
        End With
        [a1].Resize(.Count).Value = Application.Transpose(.keys)
        [d1].Resize(.Count).Value = Application.Transpose(.items)
    End With
End Sub

Wow thank you for all the effort. I will test this out tomorrow!