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.

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

Please Login or Register  to view this content.

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!

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

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

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

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

A thousand thanks to you and also rcm.