Excel function for consolidating a bunch of rows of nearly identical data?
May 3, 2012 2:01 PM Subscribe
I need help sorting out a bunch of data in Excel where the row data is almost identical except for one column that I need to retain.
posted by marylynn to Computers & Internet (13 answers total) 2 users marked this as a favorite
I have a set of data like this in an Excel file: (the / indicates a column)
1234-2345 / ABC / Title1 / Date1 / Date2
1234-2345 / DEF / Title1 / Date1 / Date2
1234-2345 / GHI / Title1 / Date1 / Date2
1234-2345 / LMN / Title1 / Date1 / Date2
1234-2345 / XYZ / Title1 / Date1 / Date2
So basically every row may have another row that is almost identical EXCEPT for the 2nd column which is going to be different. There are many thousands of sets of these.
What I'd like to end up with is something that's set up like this:
1234-2345 / ABC / DEF / GHI / LMN / XYZ / Title1 / Date1 / Date2
I know the column A data (1234-2345) is going to be the key to match these all up.
How do I consolidate these in Excel without a ton of cutting and pasting? I'm not even sure how to Google for instructions for something like this. I can struggle through a VLookup and a simple two column pivot table but that's about the extent of my skills.