Pulling data out of lists / a database

[sej]

I have a long lists of various accounts. Each account has many entries. I am trying to find a way to sum up all of the entries for each account without having to manually input the formula.

I am then trying to pull out the totals for each account to do some analytics.

I've put together a brief example of what I'm trying to do below (except that I'm dealing with several hundred accounts):

Sorry for the poor formating, this is my first attempt at posting. It should be easier to follow in the attached file.

Starting Data

Customer Purchases ($)
1 15
1 25
1 8
1 65
1 12
1 20
1 33
2 8
2 15
2 8
3 10
3 15
3 20
3 25
3 10
3 10
4 2
4 20
4 25
5 15
Total 361

Step 1

Customer Purchases ($)
1 15
1 25
1 8
1 65
1 12
1 20
1 33
Subtotal 1 178

2 8
2 15
2 8
Subtotal 2 31

3 10
3 15
3 20
3 25
3 10
3 10
Subtotal 3 90

4 2
4 20
4 25
Subtotal 4 47

5 35
Subtotal 5 35

Total 381



Final Product

Customer Purchases ($) % Total
1 178 46.7%
2 31 8.1%
3 90 23.6%
4 47 12.3%
5 35 9.2%
Total 381 100.0%

[mdbct]

You can use Excel's built in Subtotal functionality.

1. Select a cell within the data you want to create the subtotals
2. go to Data/Subtotals... and create your subtotals
3. After creating the subtotals click on the 2 in the Outline area on the left side of the screen
4. select all of the categories and their totals
5. Press Alt + ; to select the visible cell.
6. Copy the cells
7. Click on the 1 in the Outline area
8. Paste the data somewhere.
9. Perform your calculations for the final percentages

[sej]

Very helpful. Thanks very much.

Couple comments:

The Subtotal fx works fine but only after I sorted the list so that all like accounts would be grouped together. I assume that's what you anticipated.

Also, the fx won't pick up that the same items may be listed with different abbreviations or formats or mispellings (ie John Smith & Smith, John, Georgia & GA, Atlanta & Atlana, etc). I found it useful to scan through the output for duplicates then go back and fix in the original data then repeat the process. Just an idea for anyone else doing similar task.