Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pull data from another sheet based on certain criteria | Excel Discussion (Misc queries) | |||
Inserting a new line in spreadsheet | Excel Discussion (Misc queries) | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Sort pages? | Excel Discussion (Misc queries) | |||
Line Graph Data Recognition | Charts and Charting in Excel |