Consolidate multiple rows of data in Excel?

Question

Hello,

I have an Excel spreadsheet with 40 columns and 9000 rows. The columns include "Order number", "Customer name", "Item", "Amount paid", "Date of purchase", etc. Customers can add to the order optional items on top of the main item requested. Here's an example :

Line 1 : 123, Joe Smith, Car (main item), 20000 $, 2012-05-01
Line 2 : 123, Joe Smith, Sound system for the car (option), 500 $, 2012-05-01
Line 3 : 123, Joe Smith, Boat (main item), 50000 $, 2011-04-02
Line 4 : 123, Joe Smith, Insurance for the boat (option), 1000 $, 2011-04-02
Line 5 : 456, Maria Doe, Bicycle (main item), 500 $, 2012-03-08

I need to consolidate the data by order number and by type of item. For example all the "Car" data (main item + options) should be on the same record for each order. This would give :

Line 1 : 123, Joe Smith, Car (main item), 20000 $, 2012-05-01, Sound system for the car (option), 500 $, 2012-05-01
Line 2 : 123, Joe Smith, Boat, 50000 $, 2011-04-02, Insurance for the boat (option), 1000 $, 2011-04-02
Line 3 : 456, Maria Doe, Bicycle (main item), 500 $, 2012-03-08

Does anyone have an idea on how to get started ?
Thanks for your help !

Answer 1

If you were to add a column before the Item for Item ID then this exercise would be quite simple. Eg. if cars were Item ID 100 then the options could be any number between 101 and 199 and the boat could be 200 and boat options would be 201 to 299.



Then your data could be converted to a table and you would be able to filter your records by customer, order number, etc, just by choosing what you want from each column filter.

Answer 2

you ought to first convert columns to rows. replica B2, B3 and B4 (as a single block). Paste particular/Transpose into C1. Delete rows 2-4. Now do the comparable factor to Ed and each physique else.

Answer 3

Sounds like you want to split up the data into it's own column. Investigate the left$ (left string), Mid string, right string functions. I usually do this in Access in a query, but it'll work in Excel.