Speed up code, SUMIF replacement

[nikio8]

Hi all. Thanks MrExcell for teaching me to macro.

Essentially I was practicing to convert quite a few files from PDF to excel. Say, create invoice templates.
The other day i had over 30,000 lines of PDF. Times 15 columns. It would just take too long to do anything.

I was wandering how to speed up code:
-Does excel calculate column by column, row by row, or uses value change event to calculate formulas?
Say if dat is copied to column 1 (A). In column 2 (B) we use very good formula to find my main data lines (containing product codes) from column 1 (A). Then the rest of columns can start with if(B<>"","",Formula). If I have this criteria formula in column 10, i was thinking excel may calculate the first 9 columns, go to 10, and then recalculate all. Use Search or Find?
What would be the best way to arrange formulas?

-SUMIF. I do not know any other way to make sure total is correct, as invoice is divided in rows. One column will get total from PDF, another will use SUMIF.
If(SUMIF_Col - PDF_Total = 0,"Balanced", SUMIF_Col). However, this formula seems to be the major bottleneck. Would SUMIFS be quicker, however I need to run SUMIF twice, total excluding tax + tax?

Thank you

 

[thisoldman]

-Does excel calculate column by column, row by row, or uses value change event to calculate formulas?

Charles Williams at Decision Models, http://www.decisionmodels.com/calcsecretsc.htm, says this:

Excel does not calculate cells in a fixed order, or by Row or Column. Instead, Excel dynamically determines and remembers its own internal calculation sequence, which is based on dependencies and dependency trees and chains.

Overall Excel has a two step calculation process:

Step 1: Build Dependency Trees and flag cells as uncalculated.
Step 2: Determine the dependency tree calculation sequence, calculate the uncalculated formulae according to the calculation sequence, and remember the calculation sequence for the next recalculation.

Mr.Williams site is good for exploring Excel internals—but too much information to absorb in one reading.


No one, that I could find quickly, compares SUMIF to SUMIFS. Microsoft recommends SUMIF and SUMIFS over SUMPRODUCT and array formulas. They also recommend splitting out multiple conditions into helper columns. https://msdn.microsoft.com/en-us/library/mt709003.aspx#Array Formulas and SUMPRODUCT

I don't want to miss mentioning the msdn article, Excel Recalculation: https://msdn.microsoft.com/en-us/library/office/bb687891.aspx

 

[thisoldman]

I don't know if this is any faster than your

=IF(SUMIF_Col - PDF_Total = 0,"Balanced", SUMIF_Col)

but you can try

=CHOOSE((SUMIF_Col<>PDF_Total)+1,"Balanced",SUMIF_Col)