G
gary
My spreadsheet contains data in 6 columns and 500,000 rows. (Below is
a portion of my spreadsheet).
At each change in A/N, I need the totals for each of the amounts in
the other five columns.
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 A/N key 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!!
a portion of my spreadsheet).
At each change in A/N, I need the totals for each of the amounts in
the other five columns.
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 A/N key 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!!