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