Few issues relating to data sorting etc

[Calza]

Hi,

First off - sorry if this is the wrong section. It could equally probably be in VBA & Macro's or Formula's & Functions (I think).

My knowledge is moderate for excel, but more advanced functions and VBA are where it falls apart, and I think this is what I need for the sheet I'm having to develop / manage (attached) and I'm eager to learn!

To give you a brief overlay, a product (car) comes in with unique Product & order numbers in columns A & B. Each chargeable item for a vehicle is on a new row, so potentially 10-20 rows for the same item with only a few unique pieces of information per row - all of which need to be combined into one row.

(This is a simplified re-written exert of the data of course)

Problem 1:

Occasionally a product will be duplicated with two or more order numbers (an input error out of my control), I only want to keep the most recent order number, so deleting all the duplicated rows associated with lower order numbers associated to the same product number.

Problem 2:

As said, the data is split over anywhere from a few to 20 rows, with each option etc entered as a new row. I want it to be only one row, so combining all the rows into one (grouped by column A). I accept this is going to lead to a very wide table with a lot of blank cells as possible options will be blank.

Problem 3:

Connected to problem two, the variable bit of the row (the options with code/description/cost associated) can fall into different categories which is defined by the code. So when a group of rows is combined, it needs intelligence to decide which column it should move the data too (category 1/ 2/3) etc..

I hope that makes sense, I understand it's probably a lot more complex than most people ask on here?

To give an idea of the data, it's around 4000 rows a week, which in turn is around 280 unique vehicles and I need to be able to manage this as it comes in weekly!