View Full Version : Solved: Multi stage sorting and subtotaling of Excel Data Base

K. Georgiadis
04-16-2007, 04:03 PM
Hi folks! I don't think this is a VBA question but I am desperate because my post in the Microsoft Discussion Group went unanswered and I thought that yoiu may at least be able to direct me to another discussion group or resource. Here is what I posted on MS:

I have inherited 4 databases (for 2003-2006), each with 21 columns and 25230 rows containing sales information by distributor, shipping point, quantity, price, $ value, etc. etc. The problem is that (a) the database is unsorted by distributor (the key parameter for the management report that I am trying to produce) and (b) there is one complete row of data for each transaction (i.e.,shipment) of the product made to each distributor, very much like an Access table. In some cases, where a distributor buys the product in small increments several times a year, there may be over 50 rows of data where names, addresses, codes numbers, etc. are repeated over and over again.

What I need to do is:

(a) sort the database alphabetically so that I can group all distributors
together (this I can do!)
(b) then to subtotal sales of the same product to the same distributor, so
that I have a single total for each product, regardless of how many separate
shipments have been made during the year.
(c) then total sales for each distributor, with a subtotal for each product
according to (b)
(d) finally, to be able to have as visible cells total sales by product and
by distributor so that I can copy them to a new workbook for the creation of a Pivot Table.

I tried to do this empirically but I have failed to have as visible cells
the ones that I really need. Your help with a logical protocol would be
GREATLY appreciated!

Many Thanks!!!

04-16-2007, 06:27 PM
Have you considered using a Pivot Table instead?

K. Georgiadis
04-17-2007, 04:28 PM
A Pivot Table is my end goal but I have to paste together 4 databases of 24.4K rows each, one below the other (because I can't figure out how I could match them horizontally) which far exceeds Excel's 64.4 K row limit. That's why I'm trying to "condense" the database through subtotals, collapse the detal, and copy the visible cells only on to a new workbook so that I can stay within the 64.4K row limit.

04-17-2007, 05:28 PM
Excel 2007 (I believe) handles a million rows. Also, could you put the data into Access and access it directly for the Pivot Table?

K. Georgiadis
04-17-2007, 05:45 PM
I could put it on Access but the combined data bases have to go back to a client and I'm not sure if they can work with Access.

K. Georgiadis
04-17-2007, 08:54 PM
Problem solved! I trasnferred the databases to Access, created a query eliminating unnecessary columns, transferred the query to Excel and created the Pivot Table. As a bonus, the file is small enought to attach to an email. Thanks for putting me on the right track!