Calculating subtotals

G

gary

(I'm using Excel 2007)

My spreadsheet has column labels: key amt1 amt2 amt3
amt4 amt5

At each change in "key", I need the sums of "amt1", "amt2", "amt3",
"amt4" "amt5".

My speadsheet contains 232846 rows with amounts.
Between each "key", there's an empty row (i.e., 262062 empty rows)

When I use the Subtotal command in the Outline group on the Data tab,
it takes more than 10 HOURS to calculate the subtotals and grand
totals.

Is there a faster way to compute the subtotals and grand totals?
 
J

James Ravenswood

Do you want the subtotals inserted in the empty rows in the
appropriate columns??
 
G

gary

Do you want the subtotals inserted in the empty rows in the
appropriate columns??
Click to expand...

After running for 10 1/2 hours, the "progress bar indicator" (below
the spreadsheet) has moved only 1/4 of the way acoss the box. Since
I can't wait 42 hours for Excel to finish, I pressed Esc and found
that the last cell containing subtotals is 298,202.

In my spreadsheet, some "keys" exist twice. The empty row ensures
that I get subtotals for both "keys" (even though the subtotals will
be the same). It doesn't matter if the subtotals are inserted in the
empty rows or in new rows as long as I get subtotals whenever the
"key" changes.

I'm desperate for a solution; I've been working on this project now
for 4 weekends and the project is due in two days.
 
G

gary

Do you want the subtotals inserted in the empty rows in the
appropriate columns??
Click to expand...

After running for 10 1/2 hours, the "progress bar indicator" (below
the spreadsheet) has moved only 1/4 of the way acoss the box. Since
I can't wait 42 hours for Excel to finish, I pressed Esc and found
that the last row containing subtotals is 298,202.

In my spreadsheet, some "keys" exist twice. The empty row ensures
that I get subtotals for both "keys" (even though the subtotals will
be the same). It doesn't matter if the subtotals are inserted in the
empty rows or in new rows as long as I get subtotals whenever the
"key" changes.


I'm desperate for a solution; I've been working on this project now
for 4 weekends and the project is due in two days.
 
G

gary

Do you want the subtotals inserted in the empty rows in the
appropriate columns??
Click to expand...

After running for 10 1/2 hours, the progress indicator (below the
spreadsheet) has moved only 1/4 of the way across the bar. At that
rate, it'll take 42 hours to finish. Since I can't wait that long, I
pressed Esc. Subtotals have been inserted only down to row 304000.

Because some of the "keys" exist twice, the empty rows ensure that
subtotals are computed for both keys (although the subtotals will be
the same). It doesn't matter it the subtotals are put inthe empty
rows or in new rows, as long as I get subtotals whenever the "key"
changes).

I'm getting desperate for a solution. I've been working on this for 4
weekends and the project is due in two days!!!!!!
 
D

Don Guillett

After running for 10 1/2 hours, the progress indicator (below the
spreadsheet) has moved only 1/4 of the way across the bar.  At that
rate, it'll take 42 hours to finish.  Since I can't wait that long, I
pressed Esc. Subtotals have been inserted only down to row 304000.

Because some of the "keys" exist twice, the empty rows ensure that
subtotals are computed for both keys (although the subtotals will be
the same).   It doesn't matter it the subtotals are put inthe empty
rows or in new rows, as long as I get subtotals whenever the "key"
changes).

I'm getting desperate for a solution. I've been working on this for 4
weekends and the project is due in two days!!!!!!
Click to expand...

If desired, send me a file with most of the rows deleted.
dguillett1 @gmail.com
 
M

merjet

Try removing the SUBTOTAL functions from the worksheet and do them by
macro.
For example, instead of having the formula =SUBTOTAL(9,B2:B4) in cell
B5 and similar
in C5 and D5, use

Sub MySubtotals()
For iCol = 2 to 4
Range("B5") = Application.Sum("B2:B4)

End Sub
 
M

merjet

Oops. I accidentally posted before finishing. Make that:

Sub MySubtotals()
For iCol = 2 To 4
Cells(5, iCol) = Application.Sum(Range(Cells(2, iCol),
Cells(4, iCol)))
Next iCol
End Sub
 
G

gary

Oops. I accidentally posted before finishing. Make that:

Sub MySubtotals()
    For iCol = 2 To 4
        Cells(5, iCol) = Application.Sum(Range(Cells(2, iCol),
Cells(4, iCol)))
    Next iCol
End Sub
Click to expand...

I've sent my spreadsheet to Don.
 
G

gary

Hi Don,

The attachment on my first and second e-mails may have been too large
so I just sent you a much smaller one.

Gary
 
D

Don Guillett

If anyone can help me, I can e-mail a portion of my spreadsheet to
you.
Click to expand...
================
On 40000 rows, using subtotal took 245 seconds and this took less than
2 seconds.

Option Explicit
Sub DoSubtotalsforEachBlankSAS()
Dim st As Long
Dim lr As Long
Dim r As Long
Dim br As Long
'========
st = Timer ' start timing
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
r = 2
doit:
'MsgBox r
br = Columns(2).Find(What:="", After:=Cells(r, 2), _
 LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
 SearchDirection:=xlNext).Row - 1
'MsgBox br
With Range("b" & br + 1 & ":f" & br + 1)
 .Formula = "=sum(b" & r & ":b" & br & ")" ' sum 5 columns
 .Value = .Value ' convert to values
End With
r = br + 2
If br > lr Then
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox Format(Timer - st, "0.00 \s\ec")
Exit Sub
End If
GoTo doit
End Sub
 
G

gary

================
On 40000 rows, using subtotal took 245 seconds and this took less than
2 seconds.

Option Explicit
Sub DoSubtotalsforEachBlankSAS()
Dim st As Long
Dim lr As Long
Dim r As Long
Dim br As Long
'========
st = Timer ' start timing
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
r = 2
doit:
   'MsgBox r
br = Columns(2).Find(What:="", After:=Cells(r, 2), _
 LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
 SearchDirection:=xlNext).Row - 1
  'MsgBox br
With Range("b" & br + 1 & ":f" & br + 1)
 .Formula = "=sum(b" & r & ":b" & br & ")" ' sum 5 columns
 .Value = .Value   ' convert to values
End With
r = br + 2
If br > lr Then
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox Format(Timer - st, "0.00 \s\ec")
Exit Sub
End If
GoTo doit
End Sub
Click to expand...

Hi Don,

Thank you very much. It's blazingly fast. How can your macro be
changed so the "keys' are on the row as the totals.
 
G

gary

================
On 40000 rows, using subtotal took 245 seconds and this took less than
2 seconds.

Option Explicit
Sub DoSubtotalsforEachBlankSAS()
Dim st As Long
Dim lr As Long
Dim r As Long
Dim br As Long
'========
st = Timer ' start timing
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
r = 2
doit:
   'MsgBox r
br = Columns(2).Find(What:="", After:=Cells(r, 2), _
 LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
 SearchDirection:=xlNext).Row - 1
  'MsgBox br
With Range("b" & br + 1 & ":f" & br + 1)
 .Formula = "=sum(b" & r & ":b" & br & ")" ' sum 5 columns
 .Value = .Value   ' convert to values
End With
r = br + 2
If br > lr Then
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox Format(Timer - st, "0.00 \s\ec")
Exit Sub
End If
GoTo doit
End Sub
Click to expand...

Hi Don,

Thank you very much. It's blazingly fast. Can your macro be
changed so the "keys' and "total" are on the rows as the totals?.

Gary
 
D

Don Guillett

Hi Don,

Thank you very much.  It's blazingly fast.  Can your macro be
changed so the "keys' and "total" are on the rows as the totals?.

                        Gary
Click to expand...

Sure, Just add a line

'MsgBox br
Cells(br + 1, 1) = "          Keys Total" ' add this line
With Range("b" & br + 1 & ":f" & br + 1)
 
G

gary

Sure, Just add a line

'MsgBox br
          Cells(br + 1, 1) = "          Keys Total"  ' add this line
With Range("b" & br + 1 & ":f" & br + 1)- Hide quoted text -

- Show quoted text -
Click to expand...



Hi Don,

Where do I "just add a line" in your macro?

Gary
 
G

gary

You didn't tell me where in your macro to "just add a line" so I tried
putting in various places but, each time, I got a compile error when
running the macro.

I then split my spreadsheet into 10 smaller spreadsheets (each with
50,000 rows) and used Excel's Subtotal command (in the Outline group
on the Data tab). I started it on the first spreadsheet at 2 PM.
Immediatly after each spreadsheet was subtotaled, I started the next
one. By 1 AM, 9 of the spreadsheets had been subtotaled. I then
started the subtotal process on the last spreadsheet and went to bed.
I guess it finished about 2:30 AM. It took about 12 1/2 hours to get
subtotals on all the spreadsheets!!!!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Crazy Query needs help 1
query on linked tables 1
Function?? 1
Problem with same file on one of two computers 5
IIF function in Report Footer 5
Suppress sub totals on pivot table 2
subtotals 2
Subtotals & Conditional Formatting Part 2 1

Top