Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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). |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add Subtotals after inserting row | Excel Programming | |||
Extremely slow subtotals in Excel 2003 | Excel Discussion (Misc queries) | |||
Concerning subtotals and inserting | Excel Discussion (Misc queries) | |||
Subtotals and inserting formulae | Excel Programming | |||
Why are subtotals so slow in Excel 2002 SP-2? | Excel Discussion (Misc queries) |