trying to add up disparate transaction data
 

Hi All,

This is my first post to the Excel help forum, so hopefully I am at the
right place. I was perusing it, and it seemed so!

I am a small biz owner and have uploaded a bunch of data out of my Point of
Sales system, which is based on an Oracle db. The data is proprietary and 'I
get what I get'.

I have a bunch of transaction data sorted by each individual product that I
sold, but not by the actual transaction and its total. Meaning, I know how
many individual SKU's I sold, but I am trying to get an average sale by
transaction, so I know what my customer buys, on average.

I have sorted all my product data into one spreadsheet and sorted by the
transaction number, so while the individual total is not (yet) there, the
sales are grouped in the data sort I have now. What I am trying to do is

A DSUM of each of those groups (each trx has a unique 6 number ID, but I
have over 6,000 of them), which would give me that average sale by
transaction number.

I am sure this isnt too difficult but I am struggling with it.

data as follows:

column A - date,
column B - time,
column C - Trx number
column D - quantity
column E - price
column F - extended price

Thanks!
-Bill

clarification
 

a bit more data to the above request for help.

Some of my unique id's have just a single SKU in the transaction, some have
multiple. The sheet looks roughly like this:

DATE TIME TRX ID Quantity Price Ext Price
Category
20060403 1108AM 143973 2 9.49 18.98 10
20060403 1158AM 143982 1 13.99 13.99 20
20060403 1230PM 143991 1 17.49 17.49 10
20060403 121PM 144008 1 15.49 15.49 10
20060403 121PM 144008 1 15.49 15.49 10
20060403 146PM 144017 3 6.99 20.97 20
20060403 146PM 144017 3 15.00 45.00 25
20060403 146PM 144017 2 6.99 13.98 10
20060403 146PM 144017 1 17.99 17.99 20

So the first three Transactions are 1 iSKU (even if multiples of this item)
sales (each has a unique trx id), and the last two are multiple SKU sales,
144008 has 2 different SKU's, 144017 has 4 SKU's (some with multiple items
within those SKU's).

Ultimately I am trying to create a summary list that adds up each
transaction, so the meta project would be something along the lines of this:

DATE TIME TRX ID Quantity Trx Total
20060403 1108AM 143973 2 18.98
20060403 1158AM 143982 1 13.99
20060403 1230PM 143991 1 17.49
20060403 121PM 144008 2 30.98
20060403 146PM 144017 9 97.94

where the total number of items in that transaction (no matter what the
code) are summed up in quantity, and the total price of all the items under
that TRX ID are summed up in Trx Total.

Any ideas on this? DSUM?

clarification
 

Sounds like a Data Pivot Table is what you want.

Below is one example of the output from your sample data - the word
wrap doesn't make it look very pretty, but I hope you get the idea.

Just put the cursor anywhere in your table of data and select
DATA--PivotTable Next Next select Layout, drag the Date, Time and
TRX ID to the row headings, and the Quantity and ExT Price to the Data
area.

Play with dragging stuff around to see how easy it is to re-order
summaries and totals.

HTH




DATE TIME TRX ID Data Total
20060403 121PM 144008 Sum of Quantity 2
Sum of Ext 30.98
121PM Sum of Quantity 2
121PM Sum of Ext 30.98
146PM 144017 Sum of Quantity 9
Sum of Ext 97.94
146PM Sum of Quantity 9
146PM Sum of Ext 97.94
1108AM 143973 Sum of Quantity 2
Sum of Ext 18.98
1108AM Sum of Quantity 2
1108AM Sum of Ext 18.98
1158AM 143982 Sum of Quantity 1
Sum of Ext 13.99
1158AM Sum of Quantity 1
1158AM Sum of Ext 13.99
1230PM 143991 Sum of Quantity 1
Sum of Ext 17.49
1230PM Sum of Quantity 1
1230PM Sum of Ext 17.49
20060403 Sum of Quantity 15
20060403 Sum of Ext 179.38
Total Sum of Quantity 15
Total Sum of Ext 179.38




On Sun, 17 Sep 2006 14:44:01 -0700, bill adkins
wrote:

a bit more data to the above request for help.

Some of my unique id's have just a single SKU in the transaction, some have
multiple. The sheet looks roughly like this:

DATE TIME TRX ID Quantity Price Ext Price
Category
20060403 1108AM 143973 2 9.49 18.98 10
20060403 1158AM 143982 1 13.99 13.99 20
20060403 1230PM 143991 1 17.49 17.49 10
20060403 121PM 144008 1 15.49 15.49 10
20060403 121PM 144008 1 15.49 15.49 10
20060403 146PM 144017 3 6.99 20.97 20
20060403 146PM 144017 3 15.00 45.00 25
20060403 146PM 144017 2 6.99 13.98 10
20060403 146PM 144017 1 17.99 17.99 20

So the first three Transactions are 1 iSKU (even if multiples of this item)
sales (each has a unique trx id), and the last two are multiple SKU sales,
144008 has 2 different SKU's, 144017 has 4 SKU's (some with multiple items
within those SKU's).

Ultimately I am trying to create a summary list that adds up each
transaction, so the meta project would be something along the lines of this:

DATE TIME TRX ID Quantity Trx Total
20060403 1108AM 143973 2 18.98
20060403 1158AM 143982 1 13.99
20060403 1230PM 143991 1 17.49
20060403 121PM 144008 2 30.98
20060403 146PM 144017 9 97.94

where the total number of items in that transaction (no matter what the
code) are summed up in quantity, and the total price of all the items under
that TRX ID are summed up in Trx Total.

Any ideas on this? DSUM?

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________