Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating subtotals
Do you want the subtotals inserted in the empty rows in the
appropriate columns?? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!!!!!! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating subtotals
If anyone can help me, I can e-mail a portion of my spreadsheet to
you. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating subtotals
Where do I "just add a line" in Don's macro? Gary |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!!!!! |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#23
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating subtotals | Excel Programming | |||
calculating subtotals for sequential blank cells in 1 column | Excel Worksheet Functions | |||
Subtotals problem: Excel 2003 (not, AFAIK, the nested subtotals bug) | Excel Discussion (Misc queries) | |||
Calculating percentages in pivot tables on subtotals | Excel Worksheet Functions | |||
Problem with nested subtotals, placing secondary subtotals BELOW . | Excel Discussion (Misc queries) |