Inserting subtotals very slow
 

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?

Inserting subtotals very slow
 

gary wrote:

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?

Are the taxes, penalties, and costs generated programmatically, or entered by
hand from somewhere else?

--
Yes, I'm quoting myself. I'm just that important.

Inserting subtotals very slow
 

On Jan 24, 5:23*pm, "Auric__" wrote:
gary wrote:
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?

Are the taxes, penalties, and costs generated programmatically, or entered by
hand from somewhere else?

--
Yes, I'm quoting myself. I'm just that important.- Hide quoted text -

- Show quoted text -



The amoujnts are manually entered into the cells.

Inserting subtotals very slow
 

gary wrote:

On Jan 24, 5:23*pm, "Auric__" wrote:
gary wrote:
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?

Are the taxes, penalties, and costs generated programmatically, or
entered by hand from somewhere else?

The amoujnts are manually entered into the cells.

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

--
- It won't stop me from trying.
- No, but it'll stop you from succeeding.

Inserting subtotals very slow
 

gary formulated the question :
On Jan 24, 5:23*pm, "Auric__" wrote:
gary wrote:
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?

Are the taxes, penalties, and costs generated programmatically, or entered
by hand from somewhere else?

--
Yes, I'm quoting myself. I'm just that important.- Hide quoted text -

- Show quoted text -



The amoujnts are manually entered into the cells.

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 at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Inserting subtotals very slow
 

On Jan 24, 8:23*pm, GS wrote:
gary formulated the question :





On Jan 24, 5:23*pm, "Auric__" wrote:
gary wrote:
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?

Are the taxes, penalties, and costs generated programmatically, or entered
by hand from somewhere else?

--
Yes, I'm quoting myself. I'm just that important.- Hide quoted text -

- Show quoted text -

The amoujnts are manually entered into the cells.

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 -

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.

Inserting subtotals very slow
 

On Jan 24, 8:23*pm, GS wrote:
gary formulated the question :





On Jan 24, 5:23*pm, "Auric__" wrote:
gary wrote:
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?

Are the taxes, penalties, and costs generated programmatically, or entered
by hand from somewhere else?

--
Yes, I'm quoting myself. I'm just that important.- Hide quoted text -

- Show quoted text -

The amoujnts are manually entered into the cells.

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 -

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.

Inserting subtotals very slow
 

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.

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 at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Inserting subtotals very slow
 

On Jan 24, 10:25*pm, GS wrote:
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.

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

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

Inserting subtotals very slow
 

gary presented the following explanation :
On Jan 24, 10:25*pm, GS wrote:
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.

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

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

I can't answer because I never use that feature. Did you try the code?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Inserting subtotals very slow
 

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/assistan...lconPT101.aspx

On 01/24/2012 17:17, gary wrote:
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?




--
Dave Peterson