Calculating subtotals
 

(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?

Calculating subtotals
 

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

Calculating subtotals
 

Haven't tried this but try turning calculation off first.
Maybe a FINDNEXT

On Feb 11, 10:17*pm, gary wrote:
(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?

Calculating subtotals
 

On Feb 12, 3:35*am, James Ravenswood
wrote:
Do you want the subtotals inserted in the empty rows in the
appropriate columns??

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.

Calculating subtotals
 

On Feb 12, 3:35*am, James Ravenswood
wrote:
Do you want the subtotals inserted in the empty rows in the
appropriate columns??

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.

Calculating subtotals
 

On Feb 12, 3:35*am, James Ravenswood
wrote:
Do you want the subtotals inserted in the empty rows in the
appropriate columns??

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!!!!!!

Calculating subtotals
 

On Feb 12, 9:18*am, gary wrote:
On Feb 12, 3:35*am, James Ravenswood
wrote:

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

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!!!!!!

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

Calculating subtotals
 

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

Calculating subtotals
 

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

Calculating subtotals
 

On Feb 12, 8:21*am, merjet wrote:
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

I've sent my spreadsheet to Don.

Calculating subtotals
 

On Feb 12, 3:39*pm, gary wrote:
On Feb 12, 8:21*am, merjet wrote:

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

I've sent my spreadsheet to Don.

Didn't get it. did you send to dguillett1 @gmail.com

Calculating subtotals
 

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

Calculating subtotals
 

If anyone can help me, I can e-mail a portion of my spreadsheet to
you.

Calculating subtotals
 

On Feb 12, 8:34*pm, gary wrote:
If anyone can help me, I can e-mail a portion of my spreadsheet to
you.
================
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

Calculating subtotals
 

On Feb 13, 9:07*am, Don Guillett wrote:
On Feb 12, 8:34*pm, gary wrote: If anyone can help me, I can e-mail a portion of my spreadsheet to
you.

================
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

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.

Calculating subtotals
 

On Feb 13, 9:07*am, Don Guillett wrote:
On Feb 12, 8:34*pm, gary wrote: If anyone can help me, I can e-mail a portion of my spreadsheet to
you.

================
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

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

Calculating subtotals
 

On Feb 13, 5:53*pm, gary wrote:
On Feb 13, 9:07*am, Don Guillett wrote:









On Feb 12, 8:34*pm, gary wrote: If anyone can help me, I can e-mail a portion of my spreadsheet to
you.

================
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

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

Sure, Just add a line

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

Calculating subtotals
 

On Feb 14, 4:59*am, Don Guillett wrote:
On Feb 13, 5:53*pm, gary wrote:





On Feb 13, 9:07*am, Don Guillett wrote:

On Feb 12, 8:34*pm, gary wrote: If anyone can help me, I can e-mail a portion of my spreadsheet to
you.

================
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

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

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 -



Hi Don,

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

Gary

Calculating subtotals
 

Where do I "just add a line" in Don's macro?


Gary

Calculating subtotals
 

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!!!!!

Calculating subtotals
 

Hi Don,

This is in response to a comment made by Ron Rosenfield under my "Need
subtotals" posting.

I guess I was under the false impression that Google Groups could be
used to solicit help from "experts".


Since Excel Subtotals command wasn't doing what I needed to do very
quickly, my OP asked for assistance and Don graciously provided the
macro. He later suggested that I "Just add a line" but he provided
three lines:

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

Since I'm not at all familiar with macros, I didn't know where, in
Don's original macro, to add the line(s).

Gary

Calculating subtotals
 

Hi Don,

This is in response to a comment made by Ron Rosenfield under my "Need
subtotals" posting.


I guess I was under the false impression that Google Groups could be
used to solicit help from "experts".


Since Excel Subtotals command wasn't doing what I needed to do very
quickly and I'm not familiar with Pivot Tables, my OP asked for
assistance and Don graciously provided the macro. He later suggested
that I "Just add a line" but he provided three lines:


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


Since I'm not at all familiar with macros, I didn't know where, in
Don's original macro, to add the line(s).


Gary

Calculating subtotals
 

Hi Don,

Can you include the value of the "key" on the total line? For
example:

000000002-2 1957 Keys Total 19.96 1.59 0.00

Thanks,

gary