Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Mar 2016
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    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.
    Attached Files Attached Files

  3. #3
    New Lounger
    Join Date
    Mar 2016
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you so much for your effort. I will give this a try when I get to work. Take care

  4. #4
    New Lounger rollis13's Avatar
    Join Date
    Sep 2014
    Location
    Cordenons
    Posts
    7
    Thanks
    0
    Thanked 1 Time in 1 Post

Posting Permissions

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