+ Reply to Thread
Results 1 to 7 of 7

Collapse multiple rows of data for a common item and delete redundant data

  1. #1
    Registered User
    Join Date
    01-27-2014
    Location
    Minneapolis, MN, USA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Smile Collapse multiple rows of data for a common item and delete redundant data

    I have a lot of data that comes to me like the attached example. I am trying to find a quick way to collapse all of the data into one row.

    Figuring out a "quick" way is taking me a long time and I'm not having much success!

    • There are multiple rows about Thing1;
    • In some rows there is data in ColumnX in other rows there is data in ColumnY, ColumnZ, etc.
    • The information in the columns is not in any order, not always ColumnX then Y then Z.
    • It is important that the information stay in the column where it is in the original file.
    • Thing1, Thing2, etc. may have different numbers of rows depending upon how many data points there are.
    • Multipile columns of fully-populated duplicate data exist for instances of Thing1.
    • I need to keep one set of that information with the row for Thing1.


    There are generally not columns that have multiple values for Thing1 in the the same column over multiple rows (not saying there never will be, but that would be extremely rare).

    See attached example.
    I have added shading to distinguish one Thing from another, but don't need there to be any formatting in the end result.

    I am posting in this forum because I believe VBA/Macro would be best for solving it. There are quite a few similar posts, but I am not finding anything that is quite like this... if there is, I'll be happy to refer to another thread!

    Thank you for any assistance you are able to provide.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Collapse multiple rows of data for a common item and delete redundant data

    try the macro. It is applied to sheet3 just change the name of the worksheet (infile)

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    01-27-2014
    Location
    Minneapolis, MN, USA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Collapse multiple rows of data for a common item and delete redundant data

    FANTASTIC! It works!

    Only thing I noticed is that R2 and R3 didn't collapse. I changed the data around so I am pretty sure it isn't the way the data is in the rows. Since it does the thousands of rows after those two, I am perfectly fine with doing those two by hand.

    Thank you so much... you have no idea how much time this is going to save me... today, tomorrow and for years to come!

  4. #4
    Registered User
    Join Date
    01-27-2014
    Location
    Minneapolis, MN, USA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Collapse multiple rows of data for a common item and delete redundant data

    Oh, and it wanted the sheet to be named "Sheet3"... maybe I misunderstood the part about "infile"...

  5. #5
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Collapse multiple rows of data for a common item and delete redundant data

    you can mark it as solved if you dont have any other issues

  6. #6
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Collapse multiple rows of data for a common item and delete redundant data

    There is a button on sheet1. When you press it, the output is on sheet3.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    01-27-2014
    Location
    Minneapolis, MN, USA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Collapse multiple rows of data for a common item and delete redundant data

    Wow! That's cool! This one also works for me! I really appreciate your help!

    A thousand thanks to you and also rcm.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to combine data from multiple rows when common column headings exist?
    By pkoury in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 04-01-2013, 10:43 AM
  2. Replies: 11
    Last Post: 07-24-2012, 02:42 AM
  3. Add Quantity of Redundant Rows, then Delete Redundant Rows
    By alexwgordon in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-04-2012, 12:12 AM
  4. How to delete redundant info/data rows
    By gendis in forum Excel General
    Replies: 2
    Last Post: 12-05-2007, 04:11 PM
  5. Finding common data in multiple columns and rows in Excel
    By sparham in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-12-2005, 01:06 AM

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