Subtotals in Excel 2007

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

gary - 24 Apr 2008 22:37 GMT

A1 contains the column heading "NAME"
A2 thru A700000 contain names (each name can occur 1 thru 25 times).

B1 contains the column heading "AMOUNT"
B2 thru B700000 contain amounts

In the SUBTOTALS dialog box:
In the "AT EACH CHANGE IN" box, I select NAME
In the "USE FUNCTION" box, I select SUM
In the "ADD SUBTOTAL TO" box, I click NAME and AMOUNT

When I click OK, why does it take at least several HOURS to compute
the subtotals for the 80,000 unique names? How can I speed up the
process?

Reply to this Message

Bob Flanagan - 24 Apr 2008 23:06 GMT

Have you considered using a pivot table instead? Sounds like a perfect
match.

One way you may speed up the process is to first turn calculation to manual.
The keystrokes ALT T O may open the options dialog and avoid a search for
that option.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

> A1 contains the column heading "NAME"
> A2 thru A700000 contain names (each name can occur 1 thru 25 times).
[quoted text clipped - 10 lines]
> the subtotals for the 80,000 unique names? How can I speed up the
> process?

Reply to this Message

Peo Sjoblom - 24 Apr 2008 23:09 GMT

I don't think you can speed up the process if indeed you have 700,000 rows
with data.
I would use Access or some other database for a task like this.

You could try to see if a pivot table would be faster, put the name header
in the row field and the amount header in the data field.

Signature

Regards,

Peo Sjoblom

Reply to this Message