|
Need subtotals
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!! |
Need subtotals
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? |
Need subtotals
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). .. |
Need subtotals
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. |
Need subtotals
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. |
Need subtotals
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. |
Need subtotals
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 & ")" |
Need subtotals
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? |
Need subtotals
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 |
Need subtotals
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). |
Need subtotals
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 |
Need subtotals
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). |
Need subtotals
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. |
Need subtotals
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 |
Need subtotals
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. |
Need subtotals
<bg
-- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Need subtotals
On Tue, 21 Feb 2012 20:18:20 -0800, Gord Dibben wrote:
You're too soft Ron<g Gord Depends on my mood :-) |
| All times are GMT +1. The time now is 10:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com