+ Reply to Thread
Results 1 to 6 of 6

Combine duplicate rows into a single row in Excel

  1. #1
    Registered User
    Join Date
    08-19-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2008
    Posts
    3

    Exclamation Combine duplicate rows into a single row in Excel

    I have searched this forum and online generally but have not been able to successfully solve this for myself. I have seen it solved for others in similar situations so I know it is doable, just I am not doing it right.

    I have a table that contains around 83,000 rows showing order detail information. For orders with multiple items on them, a new row is created to record the individual item information. I would like to find those duplicates and move them all to a single row. A simplistic example would be;
    Order Id Product Code Qty
    35007 XYZ 1
    35007 ABC 1
    35008 ASD 2
    35009 TYU 1
    35009 XYZ 2
    35009 ABC 1

    And I would like that to become:
    35007 XYZ 1 ABC 1
    35008 ASD 2
    35009 TYU 1 XYZ 2 ABC 1

    The spaces representing columns.

    I have attached the data I am working with to this post (a sampling of rows due to the file size). If anyone can help me, it would be most appreciated!
    If I failed to mention or include any pertinent data that is needed to complete this task, please let me know.
    Thank you,
    Karin
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Combine duplicate rows into a single row in Excel

    Hi

    Just how many of the columns of data are to be copied across? What would you do if you run out of column space?

    rylo

  3. #3
    Registered User
    Join Date
    08-19-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2008
    Posts
    3

    Re: Combine duplicate rows into a single row in Excel

    rylo - thanks for your reply. I had not thought about running out of column space, but that is a good observation. I have taken the columns down to just the critical ones so I am hoping that running out of column space is not an issue. I have attached the revised spreadsheet showing the cols. If you can help me, please let me know.

    karin
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Combine duplicate rows into a single row in Excel

    Hi

    This is based on your example file. It adds a new sheet to take the output called consol

    Please Login or Register  to view this content.
    rylo

  5. #5
    Registered User
    Join Date
    08-19-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2008
    Posts
    3

    Re: Combine duplicate rows into a single row in Excel

    It works! Thank you so much! You have no idea how much headache you have saved me.

  6. #6
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Combine duplicate rows into a single row in Excel

    If you are satisfied with the solution(s) provided, please remember to mark your thread as Solved.

    New quick method:
    Select Thread Tools (above first post on page) -> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word "Title" you will see a dropdown with the words "No prefix".
    Change to "Solved"
    Click Save

    Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ 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