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!

[aydeegee]

Hi Calza

Just a few questions:

Problem 1
"I only want to keep the most recent order number" but in your example all the order numbers are the same - so how can we distinguish the oldest?

Problem 2
" with a lot of blank cells " - does this imply that you want 20 Option Type columns, 20 Option Codes .... etc?

Problem 3
"different categories which is defined by the code" how, exactly will this be defined?
" it needs intelligence to decide " It should be borne in mind that computers are essentially stupid - so do not raise your hopes too much

I think that the best way forward is for you to attach another spreadsheet showing show how your example will appear in your desired layout.

I am intrigued as to what you will be doing with the data once you have got it arranged as you want!

Regards
Alastair

[Calza]

Hi Alastair,

Thanks for taking the time to have a look at this. I've (attemped to) answer your questions below:

Hi Calza

Just a few questions:

Problem 1
"I only want to keep the most recent order number" but in your example all the order numbers are the same - so how can we distinguish the oldest?

There should be 4 unique order numbers on there, to go with 3 unique product numbers. If you look at rows 12-32 they are the same product number, but with two different order numbers (12-21 are 3232459 & 22-32 are 3232460. So that's the example where the first grouping is redundant (will always be the lower order numbers)

Problem 2
" with a lot of blank cells " - does this imply that you want 20 Option Type columns, 20 Option Codes .... etc?
Yes, pretty much. I don't neccessarily want that many columns as it's a bit preposterous isn't it? But It needs to be able to handle the unlikely scenario there are 20 options attached to an item

Problem 3
"different categories which is defined by the code" how, exactly will this be defined?
" it needs intelligence to decide " It should be borne in mind that computers are essentially stupid - so do not raise your hopes too much
The code will be manually defined (I'm still comping that list as so far I'm upto 246 unique codes!) So for example 'TAHY' goes to this column range, 'PREPA' goes to that column range. I suppose when I said intelligence I'm using the wrong word, just the ability to read a code then copy the range to whatever column the code is dictated as.

I think that the best way forward is for you to attach another spreadsheet showing show how your example will appear in your desired layout.

I am intrigued as to what you will be doing with the data once you have got it arranged as you want!

Regards
Alastair

I'll put together another spreadsheet with my planned output - no problem, will need to do it on Monday though. Keep in mind the output isn't set in stone .. it's just the best I can think of to handle it.

Well two functions. First is a consistent way to store and reference the data as it builds up each week. Off the back off that reports will be created on differing criteria.