+ Reply to Thread
Results 1 to 5 of 5

Merge Rows of duplicates??

  1. #1
    Registered User
    Join Date
    06-25-2011
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    2

    Exclamation Merge Rows of duplicates??

    Hi,

    I am trying to merge rows of duplicate entries, say I have the following information in a excel spreadsheet:

    Column A: Name
    Column B: First Line of Address
    Column C: Area
    Column D: County
    Column E: Post Code
    Column F: Tel Number

    A|B|C|D|E|F
    Dave | Rice Rd | Liverpool | Merseyside | L45 7HT | 6381754
    Tony | Hill Grove | Runcorn | Merseyside | L78 9JU | 6527897
    Mark | Rice Rd | Liverpool | Merseyside | L45 7HT | 6381754
    John | Lime Ave | Runcorn | Merseyside | L34 9HF | 7248853


    If I have the data above in my spreadsheet I want to basically search and look at columns B,C,D,E and F and then if there are any duplicates of these for that row anywhere on the sheet I want it to merge the Data for those duplicate as shown below:

    A|B|C|D|E|F
    Dave, Mark | Rice Rd | Liverpool | Merseyside | L45 7HT | 6381754
    Tony | Hill Grove | Runcorn | Merseyside | L78 9JU | 6527897
    John | Lime Ave | Runcorn | Merseyside | L34 9HF | 7248853

    I have managed to find a macro that is shown below however this macro only looks at 1 column and then merges the data based on that which is not what I want. Usually I would just create this myself if it where in any other language however I cant seem to get this to work and would really appreciate the help.

    Please Login or Register  to view this content.
    Thanks
    Last edited by anixan; 06-25-2011 at 02:22 PM.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Merge Rows of duplicates??

    Hi anixan and welcome to the forum.

    It looks like you want to Delete Duplicates, a newer feature in Excel 2007 but can be done in 2003 using Advanced Filters and the Unique check box.

    See
    http://www.lytebyte.com/2008/10/30/h...in-excel-2003/
    or http://www.youtube.com/watch?v=vvgMlI4Sf90
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    06-25-2011
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Merge Rows of duplicates??

    Hi,

    I have now edited my post to highlight what I want. I don't just want to delete the duplicates as I have already done that. I need to look at specific columns to see if they match and then if they do merge them with the other matches and delete the duplicate information within the cell.

    Thanks

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Merge Rows of duplicates??

    Hi anixan,

    I have an answer for you without needing to do VBA. See the attached.
    You will create a helping Index column and sort by it.
    Then a simple formula in H and copy the values only over the formula.
    Then because you know how to delete duplicate you can do it again using the Index column only and will get your answer.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-13-2012
    Location
    Miami
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Merge Rows of duplicates??

    Hi MarvinP,

    Your solution works when we know what column we want to merge (ie names) but if we don't know which column should be merged as in the example of a spreadsheet where two different users have been adding different data beginning with the same table and for a record on each row, each user has been adding data to different columns. I combined tables created by both users but ended up with duplicate records with new data in different columns. How would I go about merging these "duplicate" records but keeping the data both users added? Thanks.

    EduD

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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