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!!!
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!!!