Excel help please

Figment

I receive a number of spreadsheets on a daily basis, containing:

Account No, Account Name, Order No, Items, Weight, Depot, Route, Drop (there are other columns, but these are the ones I need)

An account may have placed more than one order - each shown on a separate row.

I have to collate the information from all sheets, filter the results to only show those orders coming to my depot and compute the total items/weight per account. Then I need to hide the individual order rows, allocate a route and drop number to each sub-total row, and finally sort the sheet into by route / drop order to produce loading lists.

When I attempt to do this manually, I get as far as hiding the individual order rows but this causes the sub-total rows to show 0 items and 0 weight instead of the values inserted during sub-total calculation.

Can anyone suggest how I can automate the merge/filter/sub-total/hide so that I end up with the info I need for each delivery point (Account Name/Number, Total Items, Total Weight) that I can then add route/drop info to before sorting into route/drop order. (The route/drop info will always be added manually as these details can change from day to day.)

Thanks in advance.

Figment

90+ views and not one suggestion? :huh:

victor2

Does it have to be Excel spreadsheets?
Where do the originators get their data from? Sounds like it should ideally be a centralised database.

Figment

Unfortunately the data is only available on Excel spreadsheets.

victor2

Sounds like you need to use the excel database functions then. Not something I'm familiar with, but I would think it could do what you want - or have Access set up to use Excel as the data source? That might work if you already combine the source data into one main spreadsheet.

somethingcorporate

Difficult to envisage what you mean, any chance you could upload a sample sheet?

Sounds like pivots could solve it or certainly a simple VBA macro if you can write them.

EchoLocation

In terms of the Filtering and Totals, try a Pivot Table to display the details you need. Then you could copy, either manually or with some VB code, the rows to a new sheet and add the route / drop numbers, again either manually (entered or formulas) or with some VB code.

What's your process so far?