My spreadsheet contains data in 6 columns and 500,000 rows. At each
change in AN, I need the totals for each of the amounts in the other
five columns. (Below is a portion of my spreadsheet).

Excel 2007's Subtotal command in the Outline group on the Data tab
takes MANY HOURS. Please provide a macro that will do this faster.
(Note: the AN must be on each "total" line).)

A/N
009709320-2 2001 49.59 4.95 49.59 4.95 10.00

009709320-2 2002 49.58 4.95 49.58 4.95 20.00

009709320-2 2003 49.41 4.94 49.41 4.94 20.00

009709320-2 2004 48.01 4.80 48.01 4.80 20.00

009709320-2 2005 49.59 4.95 49.59 4.95 20.00

009709320-2 2006 49.62 4.96 49.62 4.96 20.00

009709325-7 2000 35.70 3.57 35.70 3.57 10.00

009709327-9 1996 35.69 3.56 35.69 3.56 10.00

009709327-9 2006 173.55 17.35 173.55 17.35 20.00

009709329-1 2008 479.36 0.00 479.36 47.93 31.00
009709329-1 2008 24.00 0.00 24.00 2.40 0.00
009709329-1 2008 135.00 0.00 135.00 13.50 0.00

009709333-4 2008 146.87 14.68 146.87 14.68 31.00

009709336-7 2006 159.02 15.90 159.02 15.90 20.00

009709338-9 1998 60.00 6.00 60.00 6.00 10.00

009709338-9 1999 86.57 8.65 86.57 8.65 10.00

009709338-9 2000 88.30 8.83 88.30 8.83 10.00

009709338-9 2001 90.07 9.00 90.07 9.00 10.00

009709338-9 2002 91.87 9.18 91.87 9.18 20.00

009709338-9 2003 93.70 9.37 93.70 9.37 20.00

009709338-9 2004 95.45 9.54 95.45 9.54 20.00

009709338-9 2005 97.35 9.73 97.35 9.73 20.00

009709338-9 2006 99.30 9.93 99.30 9.93 20.00

009709338-9 2007 101.28 10.12 101.28 10.12 20.00

009709338-9 2008 103.31 10.33 103.31 10.33 31.00

009709338-9 2009 105.37 10.53 105.37 10.53 28.00

009709340-0 2002 114.06 11.40 114.06 11.40 20.00
009709340-0 2002 33.00 3.30 33.00 3.30 0.00

009709340-0 2004 115.55 11.55 115.55 11.55 20.00
009709340-0 2004 33.00 3.30 33.00 3.30 0.00

009709340-0 2005 118.74 11.87 118.74 11.87 20.00
009709340-0 2005 33.00 3.30 33.00 3.30 0.00

009709344-4 2000 105.60 10.56 105.60 10.56 10.00

009709345-5 2000 155.00 15.50 155.00 15.50 10.00

009709345-5 2005 309.38 0.00 309.38 30.93 20.00

Thank you!!

On Sun, 19 Feb 2012 12:02:01 -0800 (PST), gary wrote:

My spreadsheet contains data in 6 columns and 500,000 rows. At each
change in AN, I need the totals for each of the amounts in the other
five columns. (Below is a portion of my spreadsheet).

Excel 2007's Subtotal command in the Outline group on the Data tab
takes MANY HOURS. Please provide a macro that will do this faster.
(Note: the AN must be on each "total" line).)

A/N
009709320-2 2001 49.59 4.95 49.59 4.95 10.00

009709320-2 2002 49.58 4.95 49.58 4.95 20.00

009709320-2 2003 49.41 4.94 49.41 4.94 20.00

009709320-2 2004 48.01 4.80 48.01 4.80 20.00

009709320-2 2005 49.59 4.95 49.59 4.95 20.00

009709320-2 2006 49.62 4.96 49.62 4.96 20.00

009709325-7 2000 35.70 3.57 35.70 3.57 10.00

009709327-9 1996 35.69 3.56 35.69 3.56 10.00

009709327-9 2006 173.55 17.35 173.55 17.35 20.00

009709329-1 2008 479.36 0.00 479.36 47.93 31.00
009709329-1 2008 24.00 0.00 24.00 2.40 0.00
009709329-1 2008 135.00 0.00 135.00 13.50 0.00

009709333-4 2008 146.87 14.68 146.87 14.68 31.00

009709336-7 2006 159.02 15.90 159.02 15.90 20.00

009709338-9 1998 60.00 6.00 60.00 6.00 10.00

009709338-9 1999 86.57 8.65 86.57 8.65 10.00

009709338-9 2000 88.30 8.83 88.30 8.83 10.00

009709338-9 2001 90.07 9.00 90.07 9.00 10.00

009709338-9 2002 91.87 9.18 91.87 9.18 20.00

009709338-9 2003 93.70 9.37 93.70 9.37 20.00

009709338-9 2004 95.45 9.54 95.45 9.54 20.00

009709338-9 2005 97.35 9.73 97.35 9.73 20.00

009709338-9 2006 99.30 9.93 99.30 9.93 20.00

009709338-9 2007 101.28 10.12 101.28 10.12 20.00

009709338-9 2008 103.31 10.33 103.31 10.33 31.00

009709338-9 2009 105.37 10.53 105.37 10.53 28.00

009709340-0 2002 114.06 11.40 114.06 11.40 20.00
009709340-0 2002 33.00 3.30 33.00 3.30 0.00

009709340-0 2004 115.55 11.55 115.55 11.55 20.00
009709340-0 2004 33.00 3.30 33.00 3.30 0.00

009709340-0 2005 118.74 11.87 118.74 11.87 20.00
009709340-0 2005 33.00 3.30 33.00 3.30 0.00

009709344-4 2000 105.60 10.56 105.60 10.56 10.00

009709345-5 2000 155.00 15.50 155.00 15.50 10.00

009709345-5 2005 309.38 0.00 309.38 30.93 20.00

Thank you!!

How does this question differ from the one you posted on 11 Feb, and for which you have already received responses?

On Feb 19, 12:31*pm, Ron Rosenfeld wrote:
On Sun, 19 Feb 2012 12:02:01 -0800 (PST), gary wrote:
My spreadsheet contains data in 6 columns and 500,000 rows. *At each
change in AN, I need the totals for each of the amounts in the other
five columns. (Below is a portion of my spreadsheet).

Excel 2007's Subtotal command in the Outline group on the Data tab
takes MANY HOURS. *Please provide a macro that will do this faster.
(Note: the AN must be on each "total" line).)

* * * * * * * *A/N
009709320-2 2001 * *49.59 * 4.95 * *49.59 * 4.95 * *10.00

009709320-2 2002 * *49.58 * 4.95 * *49.58 * 4.95 * *20.00

009709320-2 2003 * *49.41 * 4.94 * *49.41 * 4.94 * *20.00

009709320-2 2004 * *48.01 * 4.80 * *48.01 * 4.80 * *20.00

009709320-2 2005 * *49.59 * 4.95 * *49.59 * 4.95 * *20.00

009709320-2 2006 * *49.62 * 4.96 * *49.62 * 4.96 * *20.00

009709325-7 2000 * *35.70 * 3.57 * *35.70 * 3.57 * *10.00

009709327-9 1996 * *35.69 * 3.56 * *35.69 * 3.56 * *10.00

009709327-9 2006 * *173.55 *17.35 * 173.55 *17.35 * 20.00

009709329-1 2008 * *479.36 *0.00 * *479.36 *47.93 * 31.00
009709329-1 2008 * *24.00 * 0.00 * *24.00 * 2.40 * *0.00
009709329-1 2008 * *135.00 *0.00 * *135.00 *13.50 * 0.00

009709333-4 2008 * *146.87 *14.68 * 146.87 *14.68 * 31.00

009709336-7 2006 * *159.02 *15.90 * 159.02 *15.90 * 20.00

009709338-9 1998 * *60.00 * 6.00 * *60.00 * 6.00 * *10.00

009709338-9 1999 * *86.57 * 8.65 * *86.57 * 8.65 * *10.00

009709338-9 2000 * *88.30 * 8.83 * *88.30 * 8.83 * *10.00

009709338-9 2001 * *90.07 * 9.00 * *90.07 * 9.00 * *10.00

009709338-9 2002 * *91.87 * 9.18 * *91.87 * 9.18 * *20.00

009709338-9 2003 * *93.70 * 9.37 * *93.70 * 9.37 * *20.00

009709338-9 2004 * *95.45 * 9.54 * *95.45 * 9.54 * *20.00

009709338-9 2005 * *97.35 * 9.73 * *97.35 * 9.73 * *20.00

009709338-9 2006 * *99.30 * 9.93 * *99.30 * 9.93 * *20.00

009709338-9 2007 * *101.28 *10.12 * 101.28 *10.12 * 20.00

009709338-9 2008 * *103.31 *10.33 * 103.31 *10.33 * 31.00

009709338-9 2009 * *105.37 *10.53 * 105.37 *10.53 * 28.00

009709340-0 2002 * *114.06 *11.40 * 114.06 *11.40 * 20.00
009709340-0 2002 * *33.00 * 3.30 * *33.00 * 3.30 * *0.00

009709340-0 2004 * *115.55 *11.55 * 115.55 *11.55 * 20.00
009709340-0 2004 * *33.00 * 3.30 * *33.00 * 3.30 * *0.00

009709340-0 2005 * *118.74 *11.87 * 118.74 *11.87 * 20.00
009709340-0 2005 * *33.00 * 3.30 * *33.00 * 3.30 * *0.00

009709344-4 2000 * *105.60 *10.56 * 105.60 *10.56 * 10.00

009709345-5 2000 * *155.00 *15.50 * 155.00 *15.50 * 10.00

009709345-5 2005 * *309.38 *0.00 * *309.38 *30.93 * 20.00

* * * * * * Thank you!!

How does this question differ from the one you posted on 11 Feb, and for which you have already received responses?- Hide quoted text -

- Show quoted text -

the total lines don't contain the key (i.e., A/N).

(See my newer post tin this group).




..

On Sun, 19 Feb 2012 12:38:18 -0800 (PST), gary wrote:

the total lines don't contain the key (i.e., A/N).

(See my newer post tin this group).

Since this seems to be just a minor modification of your original problem, rather than something completely different, it seems to me you will be more likely to obtain an appropriate response by asking in your original thread. I'm not going to recreate what Don has already provided you, but it should not take much of a modification.

On Feb 19, 4:00*pm, Ron Rosenfeld wrote:
On Sun, 19 Feb 2012 12:38:18 -0800 (PST), gary wrote:
the total lines don't contain the key (i.e., A/N).

(See my newer post tin this group).

Since this seems to be just a minor modification of your original problem, rather than something completely different, it seems to me you will be more likely to obtain an appropriate response by asking in your original thread. *I'm not going to recreate what Don has already provided you, but it should not take much of a modification.

I ended up using the Subtotal function.

I split my spreadsheet into 10 smaller spreadsheets (each with 50,000
rows).

I then used Excel's Subtotal command (in the Outline group on the Data
tab) on the first spreadsheet at 2 PM PST. 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.

On Feb 20, 9:07*am, gary wrote:
I split my spreadsheet into 10 smaller spreadsheets (each with 50,000
rows).

I then used Excel's Subtotal command (in the Outline group on the Data
tab) on the first spreadsheet at 2 PM PST. *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.


I provided the modification to add a line. If not OK, send me file.
key total
'MsgBox br
Cells(br + 1, 1) = " Keys Total"
'=========='added this line
With Range("b" & br + 1 & ":f" & br + 1)
'==============
.Formula = "=sum(b" & r & ":b" & br & ")"

On Feb 20, 9:48*am, Don Guillett wrote:
On Feb 20, wrote:

I split my spreadsheet into 10 smaller spreadsheets (each with 50,000
rows).

I then used Excel's Subtotal command (in the Outline group on the Data
tab) on the first spreadsheet at 2 PM PST. *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 getsubtotalson all the spreadsheets.

I provided the modification to add a line. If not OK, send me file.
* * *key total
'MsgBox br
Cells(br + 1, 1) = " * * * * *Keys Total"
'=========='added this line
With Range("b" & br + 1 & ":f" & br + 1)
'==============
*.Formula = "=sum(b" & r & ":b" & br & ")"

Thanks. But where in your macro do I insert the line?

gary submitted this idea :
On Feb 20, 9:48*am, Don Guillett wrote:
On Feb 20, wrote:

I split my spreadsheet into 10 smaller spreadsheets (each with 50,000
rows).

I then used Excel's Subtotal command (in the Outline group on the Data
tab) on the first spreadsheet at 2 PM PST. *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 getsubtotalson all the spreadsheets.

I provided the modification to add a line. If not OK, send me file.
* * *key total
'MsgBox br
Cells(br + 1, 1) = " * * * * *Keys Total"
'=========='added this line
With Range("b" & br + 1 & ":f" & br + 1)
'==============
*.Formula = "=sum(b" & r & ":b" & br & ")"

Thanks. But where in your macro do I insert the line?

I'm just guessing<g but I suspect between the before/after lines shown
here that match in the original code. This is the same place as before
when Don first posted the modification back when you first asked. Are
you expecting someone to repost all the code when you already have most
of it anyway? Seems to me you just want people here to plunk solutions
in your lap that don't require any effort on your part to understand
how to use them. READ Don's reply! FIND the match before/after lines in
the original code! INSERT the additional line! Sounds very much like a
simple 1-2-3 task to me.

'Git-r-done', man! (And don't forget to THANK Don for making the time
and effort!!!)

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

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).

On Feb 20, 12:46*pm, GS wrote:
gary submitted this idea :





On Feb 20, 9:48*am, Don Guillett wrote:
On Feb 20, wrote:

I split my spreadsheet into 10 smaller spreadsheets (each with 50,000
rows).

I then used Excel's Subtotal command (in the Outline group on the Data
tab) on the first spreadsheet at 2 PM PST. *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 getsubtotalson all the spreadsheets.

I provided the modification to add a line. If not OK, send me file.
* * *key total
'MsgBox br
Cells(br + 1, 1) = " * * * * *Keys Total"
'=========='added this line
With Range("b" & br + 1 & ":f" & br + 1)
'==============
*.Formula = "=sum(b" & r & ":b" & br & ")"

Thanks. *But where in your macro do I insert the line?

I'm just guessing<g but I suspect between the before/after lines shown
here that match in the original code. This is the same place as before
when Don first posted the modification back when you first asked. Are
you expecting someone to repost all the code when you already have most
of it anyway? Seems to me you just want people here to plunk solutions
in your lap that don't require any effort on your part to understand
how to use them. READ Don's reply! FIND the match before/after lines in
the original code! INSERT the additional line! Sounds very much like a
simple 1-2-3 task to me.

'Git-r-done', man! (And don't forget to THANK Don for making the time
and effort!!!)

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup!
* * comp.lang.basic.visual.misc
* * microsoft.public.vb.general.discussion- Hide quoted text -

- Show quoted text -

Hi Ron,

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

You just can't get good help these days.


Gord

On Tue, 21 Feb 2012 11:22:34 -0800 (PST), gary
wrote:


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).

On Tue, 21 Feb 2012 11:58:30 -0800 (PST), gary wrote:


Hi Ron,

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

Well, Gary. Even to us non-experts, it seems to me that a snippet of code that has three lines:

Line1: A line of code from the original macro

'MsgBox br

Line2: Extra code with a notation at the end that reads ' add this line

Cells(br + 1, 1) = " Keys Total" ' add this line

Line 3. Another line of code from the original macro

With Range("b" & br + 1 & ":f" & br + 1)

makes it pretty self-evident where to insert the extra line of code.

The fact that you were not able to decipher those three lines in that way leads me to believe that your solution of just using the Subtotal function, and letting it run as long as it required, was the best one for you.

on 2/21/2012, gary supposed :
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).

Well, I'm sure that's why Don included the existing before/after lines
from the original macro. You only need to know how to read text to 'get
it'.

The only way you'll become familiar with macros is to work with them.
Asking people here to provide you with macros on an as needed basis
isn't helping you learn to better use the tools for your job. We are
here to help *you do* that! What we expect to see from a frequent
solicitor is some evidence they want to learn & grow in their Excel
skills. The time we spend here is gratis and so we can only hope those
we help will express their gratitude. If our time helps you grow then
it's well 'invested', otherwise it's just maybe not so well 'spent'.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

You're too soft Ron<g


Gord

On Tue, 21 Feb 2012 20:50:42 -0500, Ron Rosenfeld
wrote:

On Tue, 21 Feb 2012 11:58:30 -0800 (PST), gary wrote:


Hi Ron,

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

Well, Gary. Even to us non-experts, it seems to me that a snippet of code that has three lines:

Line1: A line of code from the original macro

'MsgBox br

Line2: Extra code with a notation at the end that reads ' add this line

Cells(br + 1, 1) = " Keys Total" ' add this line

Line 3. Another line of code from the original macro

With Range("b" & br + 1 & ":f" & br + 1)

makes it pretty self-evident where to insert the extra line of code.

The fact that you were not able to decipher those three lines in that way leads me to believe that your solution of just using the Subtotal function, and letting it run as long as it required, was the best one for you.

<bg

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Tue, 21 Feb 2012 20:18:20 -0800, Gord Dibben wrote:

You're too soft Ron<g


Gord

Depends on my mood :-)