+ Reply to Thread
Results 1 to 5 of 5

Combine text from many cells if adjacent duplicates and macro.

  1. #1
    Registered User
    Join Date
    01-05-2011
    Location
    nh
    MS-Off Ver
    Excel 2010
    Posts
    4

    Question Combine text from many cells if adjacent duplicates and macro.

    I would like to create a function, and subsequently a macro that would solve the following issue:

    Column A has IDs with many duplicates. Column B has details for each ID in A. Column B's content is from standardized responses, so for each ID, there may be many responses, and these responses appear repeatedly through Column B.

    Example:

    Column A | Column B
    12345 Cat
    12345 Dog
    12345 Bird
    67890 Cat
    67890 Bird


    I would like to combine the contents of Column B for each ID into one cell.

    Example:

    Column A | Column B
    12345 Cat, Dog, Bird
    67890 Cat, Bird

    I understand the function of =B2 & ", " & B3 & ", " & B4, but the trick here is to automate it for hundreds of IDs and thousands of lines such that for instances of duplicates in the ID column A, it runs the formula/script to combine the contents of B into one cell for adjacent to the ID, deleting the duplicate rows containing the IDs and their B data (now combined into one cell in the remaining row for the ID).

    Please help, you'd save me literally about 2.5 hours a week for work.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Combine text from many cells if adjacent duplicates and macro.

    Here's a macro for merging rows of data to one row matching for column A.This puts the values in the same row, but not the same cell, which might be more useful. Give it a try, there's a sample workbook on that page to help you test it.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Combine text from many cells if adjacent duplicates and macro.

    And here's an idea to do it with functions but jb's code is far easier!
    Attached Files Attached Files
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Registered User
    Join Date
    01-05-2011
    Location
    nh
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Combine text from many cells if adjacent duplicates and macro.

    Thank you guys so much! I expected junk...these forums never seem to work out for me. I will try to tack on something else to the macro to combine the contents of the new cells, separated by a comma. Thank you very much! You just saved me about 2 hours a week.

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Combine text from many cells if adjacent duplicates and macro.

    Replace this section to make it one cell in column B:

    Please Login or Register  to view this content.

    Also, in that case, you can remove the "Add Titles" section near the end, too.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1