Need subtotals

G

gary

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

Ron Rosenfeld

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!!
Click to expand...

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

gary

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 -
Click to expand...

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

(See my newer post tin this group).




..
 
R

Ron Rosenfeld

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

(See my newer post tin this group).
Click to expand...

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

gary

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.
Click to expand...

I ended up using the Subtotal function.
 
G

gary

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

Don Guillett

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.
Click to expand...


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 & ")"
 
G

gary

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 & ")"
Click to expand...

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

GS

gary submitted this idea :
Thanks. But where in your macro do I insert the line?
Click to expand...

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
 
G

gary

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

gary

gary submitted this idea :







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 -
Click to expand...

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
 
R

Ron Rosenfeld

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
Click to expand...

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
Click to expand...

Line2: Extra code with a notation at the end that reads ' add this line
Cells(br + 1, 1) = " Keys Total" ' add this line
Click to expand...

Line 3. Another line of code from the original macro
With Range("b" & br + 1 & ":f" & br + 1)
Click to expand...

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

GS

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).
Click to expand...

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
 
G

Gord Dibben

You're too soft Ron<g>


Gord

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


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.
Click to expand...
 

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

Need subtotals 0

Top