Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ideas for an alternate (faster) approach to sumproduct?
I have a colleague's workbook (XL07) that had about 15K sumproduct formulas,
each referencing a sheet of raw data that has about 80K rows (and grows by 20K rows per month, so it will only get slower). I removed the sumproduct formulas, thinking that untimely recalculations might be part of what slows down the overall workbook. I replaced it with application.evaluate(sumproduct(my conditions)) then pasted the resulting value into the target cells. This eliminates any possible recalculation of those cells outside of the macro. Unfortunately, running that macro is slower than molasses, so I'm looking for a better approach. I *do* turn off calculation and screenupdating at the top of the macro. My next step is to load the source data into two arrays and do my comparisons there (it should save me some time), but I'm not sure it will be as fast as I'd like. I could pre-sort the raw data so that all the target data for each sumproduct is co-located, but I'd still have to figure out the range of rows to feed into the sumproduct - maybe use a match statement to figure out where to start, but how to figure out the last row to use for that data set? I appreciate any ideas you might have! Thank you, Keith |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ideas for an alternate (faster) approach to sumproduct?
I'd do some experiments with pivottables.
In fact, I'd do some basic experiments with helper columns and plain (non-array entered) formulas. ker_01 wrote: I have a colleague's workbook (XL07) that had about 15K sumproduct formulas, each referencing a sheet of raw data that has about 80K rows (and grows by 20K rows per month, so it will only get slower). I removed the sumproduct formulas, thinking that untimely recalculations might be part of what slows down the overall workbook. I replaced it with application.evaluate(sumproduct(my conditions)) then pasted the resulting value into the target cells. This eliminates any possible recalculation of those cells outside of the macro. Unfortunately, running that macro is slower than molasses, so I'm looking for a better approach. I *do* turn off calculation and screenupdating at the top of the macro. My next step is to load the source data into two arrays and do my comparisons there (it should save me some time), but I'm not sure it will be as fast as I'd like. I could pre-sort the raw data so that all the target data for each sumproduct is co-located, but I'd still have to figure out the range of rows to feed into the sumproduct - maybe use a match statement to figure out where to start, but how to figure out the last row to use for that data set? I appreciate any ideas you might have! Thank you, Keith -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
new approach | Excel Programming | |||
Which is faster sum(if) as an array or sumproduct? | New Users to Excel | |||
Which is faster? SUMPRODUCT or VLOOKUP, or another alternative? | Excel Worksheet Functions | |||
New approach | Excel Programming | |||
Can faster CPU+larger/faster RAM significantly speed up recalulati | Excel Discussion (Misc queries) |