| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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). .. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Subtotals problem: Excel 2003 (not, AFAIK, the nested subtotals bug) | Excel Discussion (Misc queries) | |||
| Original subtotals should not be within nested subtotals in excel | Excel Worksheet Functions | |||
| Subtotals function offset subtotals summary | Excel Programming | |||
| How do I copy an outline w/ subtotals & paste just the subtotals | Excel Discussion (Misc queries) | |||
| Problem with nested subtotals, placing secondary subtotals BELOW . | Excel Discussion (Misc queries) | |||
Hybrid Mode
