Consolidate multiple order lines

[dunlopoil]

Hi,
The sales record I am working on contains many lines per order for the same part number, e.g.:

Order: 121212
Part: 12345, Qty 1
Part: 12345, Qty 1
Part: 12345, Qty 1

Instead of:
Order: 121212
Part: 12345, Qty 3.

I want to consolidate this data. I've tried subtotalling on order, then subtotalling on part number. This works, but the resultant appearance is messy and hard to follow.

Can anyone suggest a better method?

Many thanks,
Doug.

 

[Mike Blackman]

Hi,

Possibly run an advance filter on the data with the Unique Items Box checked

Data - Filter - Advanced Filter

And then in the Column B the following Formula Copied down.

=COUNTIF(Sheet1!A:A,Sheet1!A1)

Obviously changing the sheet name to fit.

 

[Mike Blackman]

No ignore that I missed the order part. Sorry.

How many orders are there likely to be on one sheet?

 

[dunlopoil]

No ignore that I missed the order part. Sorry.

How many orders are there likely to be on one sheet?

The complete sheet with the duplicate lines is 18,684 rows. When I extract the order column and filter for unique records only it is 5,816 rows. You can see why I am keen to make the data more manageable!

 

[Mike Blackman]

Hi Again,

So i'm assuming there is multiple orders as well as line items on this sheet. Are the Order Numbers above or beside the part numbers? I am assuming you want to keep all the line items within there set orders but I dont understand what you mean by;

When I extract the order column

From this would assume the Orders are beside the Part Numbers but I want to be clear on this, can you maybe post a sample of the data?

 

[dunlopoil]

Hi Again,

So i'm assuming there is multiple orders as well as line items on this sheet. Are the Order Numbers above or beside the part numbers? I am assuming you want to keep all the line items within there set orders but I dont understand what you mean by;



From this would assume the Orders are beside the Part Numbers but I want to be clear on this, can you maybe post a sample of the data?

Hi,
Sorry, can't post the data but I'll try explaining it better. Forget the comment about extracting the order column, it was just to illustrate the point about the volume of data that is produced.

The data comes from our order entry system, which is based on shipping lines. Therefore, for an order for one product type, this may appear on the Excel download as qty 3, new row, qty 3, new row, qty 3, instead of one row with a quantity of 9 which is all I am interested in. I am trying to find a way of consolidating this apart from using nested subtotals. As I said, this does work but the way the data is displayed is quite hard to follow.

Thanks,
Doug.

 

[cornflakegirl]

How about a pivot table?

 

[dunlopoil]

How about a pivot table?

Thanks Emma.
There are 34 columns in total on the download. My hope was that I could deal with this anomaly before querying the data in whatever way after that. I presume creating a pivot table at this stage would only be ok to cross-reference a few fields?

 

[dunlopoil]

can you maybe post a sample of the data?

Now posted at http://www.box.net/shared/3lu8h8ls8k

 

[cornflakegirl]

From the help menu, most pivot table limitations are due to available memory, rather than built in to Excel.

Are you actually planning to analyse all 34 columns at one time?

It's difficult to be helpful without knowing what you're really trying to do. But, if you use a pivot table later, then it may not matter that the source data doesn't look as pretty as you would like.