Using subtotals as single data entries

[Bob]

Sorry about the subject--I couldn't figure out how to describe it
simply.

I have a large file (16,000 records) of amounts billed by roughly
10,000 service providers. A number of these providers have multiple
office locations, so each record is unique to a specific office
location. In other words, a provider who billed from 3 different
office locations will have 3 entries. Each provider has a unique
provider ID number, which stays the same regardless of which office
location he is billing from.

I want to be able to subtotal the amount billed by each provider for
all their office locations combined, and then assign those subtotals
to each provider who has multiple offices, so that I will be able to
sort the providers by the total amount they billed, e.g., how many
unique providers billed a total of $1,000 or more, etc., regardless of
how many offices they had. Is there a way to do this?

Thanks for any help.

 

[Bob]

OK, I tried the Pivot Table route, but there are too many records
(>16,000) for it to work. I tried splitting the file in half and doing
pivot tables on each half, which works fine, but it may be missing
some values that appear in each half. Is there some way around this
problem, or of "adding" 2 pivot tables?

 

[Guest]

Bob
I didn't realize there was a limit on the pivot table function. Live and learn

I put together the following procedure which should do the trick. Post back if you have any problems

1) Select ID colum

2) This will require that the first row of the data columns is a header

Data>Filter>Advance
Check “Copy to another locationâ€
List: Already filled i
Criteria: Select header cell of ID colum
Copy to: Select first cell of a blank colum
Check “Unique records onlyâ€

3) Assuming ID in column A, bill amounts in B, and filtered data in D, enter this formula in E2

=SUMPRODUCT(($A$2:$A$30000=D2)*($B$2:$B$30000)

This will check for each occurrence of the ID number in D2 in column A and sum up the corresponding values in column B. The Sumproduct function cannot use column references (ex. A:A) so I just used more cells then you should need. The ranges are absolute references so they won’t change when you copy the formula

4) Copy down to end of filtered list. This can be done very quickly by selecting E2 and double clicking on the fill handle (black square in lower right corner of selection)

5) Go get a cup of coffee. I tested this with 25000 records on 12000 ID numbers. It took about 10 minutes. You will see “CALCULATE†at the bottom of the Excel window for about 1 minute, and then Calculating ##% for the rest of the time. Be patient. If you do anything else with your computer while this is calculating it will take much longer

6) Edit>Copy column E and Edit>Paste_Special>Values on top of itself. This will replace the formulas with the actual numbers. Otherwise whenever you change a cell Excel will recalculate the entire sheet

Now you should have the table you need

Good Luck
Mark Graesse
(e-mail address removed)


----- Bob wrote: ----

OK, I tried the Pivot Table route, but there are too many record
(>16,000) for it to work. I tried splitting the file in half and doin
pivot tables on each half, which works fine, but it may be missin
some values that appear in each half. Is there some way around thi
problem, or of "adding" 2 pivot tables

 

[Bob]

Bob,
I didn't realize there was a limit on the pivot table function. Live and learn.

I put together the following procedure which should do the trick. Post back if you have any problems.

Excellent! That did the trick. Thanks very much!