Inserting subtotals very slow

  • Thread starter Thread starter gary
  • Start date Start date
G

gary

My Excel 2007 spreadsheet looks like this:

parcel type tax penalty cost
123 0000 10.00 1.50 30.00
234 2345 5.55 .55 10.00
356 5040 11.11 1.12 3.00
945 2145 7.00 .70 1.50


(It has 135,000 different parcels)

When the parcel changes, I need subtotals for the tax, penalty and
cost.

After 30 minutes, subtotals have been inserted for only 15,000
parcels. At this rate, it'll take almost 5 hours to complete.
Is there any way to speed up the subtotalling?
 
gary said:
My Excel 2007 spreadsheet looks like this:

parcel type tax penalty cost
123 0000 10.00 1.50 30.00
234 2345 5.55 .55 10.00
356 5040 11.11 1.12 3.00
945 2145 7.00 .70 1.50


(It has 135,000 different parcels)

When the parcel changes, I need subtotals for the tax, penalty and
cost.

After 30 minutes, subtotals have been inserted for only 15,000
parcels. At this rate, it'll take almost 5 hours to complete.
Is there any way to speed up the subtotalling?
Click to expand...

Are the taxes, penalties, and costs generated programmatically, or entered by
hand from somewhere else?
 
gary said:
The amoujnts are manually entered into the cells.
Click to expand...

So what exactly are you trying to accomplish then? This sounds like a data
entry problem to me, not something that can be dealt with via programming. (I
can't think of a programming solution that can increase the speed you type.
Not with this kind of data, anyway.)

If it takes 5 hours to do, then so be it. If you don't want to do it
yourself, hire a temp. Shrug.

If you're getting these numbers from a series of papers -- a set of invoices
or whatever -- you *could* scan them in, OCR the results, and import that
into Excel... but I'd bet that doing so would take *significantly* more than
5 hours to get working (not to mention the high likelihood of OCR errors).
 
gary formulated the question :
The amoujnts are manually entered into the cells.
Click to expand...

Sorry but I just have to ask...

Are the subtotals for each parcel? If so, why doesn't the sheet calc
this automatically when/as amounts are entered? Are the subtotals for
the tax/penalty/cost columns? If so, why doesn't the sheet calc this
automatically when/as amounts are entered?
 
gary formulated the question :







Sorry but I just have to ask...

Are the subtotals for each parcel? If so, why doesn't the sheet calc
this automatically when/as amounts are entered? Are the subtotals for
the tax/penalty/cost columns? If so, why doesn't the sheet calc this
automatically when/as amounts are entered?

--
Garry

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

- Show quoted text -
Click to expand...

The parcels, types and amounts were imported into the Excel 2007
spreadsheet from a text file.
Now, I need to get each the subtotals of each parcel's tax, penalty
and cost amounts
I began the subtotaling process over 3 hours ago. At the bottom of
the spreadsheet, the green "Inserting Subtotals" "progress bar" is
only half way across the bar. At that rate, it'll take another 4
hours or so before all the subtotals have been inserted into the
spreasheet.
 
gary formulated the question :







Sorry but I just have to ask...

Are the subtotals for each parcel? If so, why doesn't the sheet calc
this automatically when/as amounts are entered? Are the subtotals for
the tax/penalty/cost columns? If so, why doesn't the sheet calc this
automatically when/as amounts are entered?

--
Garry

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

- Show quoted text -
Click to expand...

The parcels, types and amounts were imported into the Excel 2007
spreadsheet from a text file.

(There are one to four rows of amounts for each parcel).

Now, I need to get each the subtotals of each parcel's tax, penalty
and cost amounts.

I began the subtotaling process over 3 hours ago. At the bottom of
the spreadsheet, the green "Inserting Subtotals" "progress bar" is
only half way across the bar. At that rate, it'll take another 4
hours before all the subtotals have been inserted into the
spreasheet.
 
gary presented the following explanation :
The parcels, types and amounts were imported into the Excel 2007
spreadsheet from a text file.
Now, I need to get each the subtotals of each parcel's tax, penalty
and cost amounts
I began the subtotaling process over 3 hours ago. At the bottom of
the spreadsheet, the green "Inserting Subtotals" "progress bar" is
only half way across the bar. At that rate, it'll take another 4
hours or so before all the subtotals have been inserted into the
spreasheet.
Click to expand...

Ok, that's more helpful! How is the data being imported? If by VBA then
the subtotals can be added fairly easily in that process. If using the
Import Wizard OR just opening a CSV, you can add the subtotals just as
easily by a macro. Assuming the latter...

Sub InsertSubtotals()
Dim lTargetCol As Long, lRows As Long
With ActiveSheet.UsedRange
lTargetCol = .Columns.Count + 1: lRows = .Rows.Count - 1
End With
With Cells(2, lTargetCol).Resize(lRows)
.NumberFormat = "0.00": .Formula = "=SUM($C2:$E2)": .Value = .Value
End With
End Sub

...where colF is empty to receive the subtotals. If you don't want the
formulas converted to constant values then omit the last part assigning
the .Value to the range.
 
gary presented the following explanation :


Ok, that's more helpful! How is the data being imported? If by VBA then
the subtotals can be added fairly easily in that process. If using the
Import Wizard OR just opening a CSV, you can add the subtotals just as
easily by a macro. Assuming the latter...

Sub InsertSubtotals()
  Dim lTargetCol As Long, lRows As Long
  With ActiveSheet.UsedRange
    lTargetCol = .Columns.Count + 1: lRows = .Rows.Count - 1
  End With
  With Cells(2, lTargetCol).Resize(lRows)
    .NumberFormat = "0.00": .Formula = "=SUM($C2:$E2)": .Value = .Value
  End With
End Sub

..where colF is empty to receive the subtotals. If you don't want the
formulas converted to constant values then omit the last part assigning
the .Value to the range.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
Click to expand...

Hi Garry,

Why does the DATA/SUBTOTAL method take so long? (So far, it's taken
5 1/2 hrs and the green "Inserting Subtotals" "progress bar" is only
about 3/4 of the way across the bar).
 
gary presented the following explanation :
Hi Garry,

Why does the DATA/SUBTOTAL method take so long? (So far, it's taken
5 1/2 hrs and the green "Inserting Subtotals" "progress bar" is only
about 3/4 of the way across the bar).
Click to expand...

I can't answer because I never use that feature. Did you try the code?
 
I've found that pivottables can get created much faster than data|subtotals with
large sets of data.

You may want to try them.

If you've never used pivottables, here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx
 
The problem is the recalculate feature. I have had the same issue trying to data > subtotal a 16,000 row spreadsheet with only 4 columns, none of them formula. 40 minutes and still churning away. Use Options > formula and switch recalculation to manual. Did data > subtotals and all complete in 10 seconds.

Gotta love these new fangled spreadsheets
 
Back
Top