Calc Speed & Formula simplification

[Ken]

Excel2003 ... 1st order of the day? Ask assistance from those intimate with
Excel (at least it started out as the 1st order of the day).

As an Excel hack (my Co is actually sending me to an Excel1 class this
Monday) ... I often muddle through the Excel Help Screens & these great
boards to come up with Formulas that work without much regard for how they
work, the order in which they work, or their calculation efficiency ...
Definitely, a recognized short-coming on my part ... so my apologies for
those times when I fail to use the proper Excel jargon in my post.

Above said ... the following is simply beyond my Excel skills.

I created a mini template (4 WorkSheets with 1000-2000 records each) and all
formulas worked fine, but when I expanded the template to the size I need (4
WorkSheets with 10000-40000 records each) ... CALCULATIONS choked. I have
experimented with INDEX/MATCH, SUMPRODUCT, VLOOKUP & SUM(IF ... but have not
been able to write to calculate efficiently. Consequently, CALCULATIONS
continue to choke.

Helper Cols may be an alternative, but since I avoid them like the plague
(another short-coming) I need guidance from this board on what best to do ...
& I will certainly entertain the use of Helper Cols if this is what is
required & if a little instruction is provided.

What I have ... 4 WorkSheets (CCs, SWCs, ACTs & BTs):

Note: ACTs & BTs have no Formulas ... SWCs pulls in 2 Cols from ACTs ...
CCs pulls in & calculates Cols from SWCs & BTs.

CCs 10000 records (Range A1:N10000 ... Row 1 = Header)
SWCs 40000 records (Range A1:AF40000... Row 1 = Header)
ACTs 40000 records (Range A1:G40000 ... Row 1 = Header)
BTs 10000 records (Range A1:J10000 ... Row 1 = Header)

WorkSheet ... CCs ... (10000 records (Range A1:N10000) ... Row 1 = Header)

Formulas in Cols A:M

What I think my “choking†formulas might be ... Looking for options to
simply?:

Col B ... Range (B3:B10000) ... nice “array†formula from this board
(Ragdyer) pulls in “unique†records from BTs ... so I do not have to use
“Advance Auto-Filter†... I am willing to return to use of “Advanced
Auto-Filter†if necessary.

{=IF(ISERROR(MATCH(0,COUNTIF(B$2:B2,BTs!$C$2:$C$10000&""),0)),"",INDEX(IF(ISBLANK(BTs!$C$2:$C$10000),"",BTs!$C$2:$C$10000),MATCH(0,COUNTIF(B$2:B2,BTs!$C$2:$C$10000&""),0))) }

Col C ... Range (C2:C10000):

=IF(OR($B2="",ISNA(MATCH(CCs!$B2,BTs!$C$1:$C$10000,0))),"",INDEX(BTs!$E$1:$E$10000,MATCH(CCs!$B2,BTs!$C$1:$C$10000,0)))

Col D ... Range (D210000)

=IF($B2="","",IF(OR(ISNA(MATCH(CCs!$B2,BTs!$C$1:$C$10000,0)),INDEX(BTs!$F$1:$F$10000,MATCH(CCs!$B2,BTs!$C$1:$C$10000,0))=0),"",INDEX(BTs!$F$1:$F$10000,MATCH(CCs!$B2,BTs!$C$1:$C$10000,0))))

Col E ... Range (E2:E10000 ... same as Col C ... different target)

Col F ... Range (F2:F10000)

=IF($B2="","",COUNTIF(BTs!$C$2:$C$10000,CCs!$B2)) ... (I think ok?)

Col G ... Range (G2:G10000)

=IF($B2="","",IF(ISERROR(SUMPRODUCT(--(SWCs!$G$2:$G$40000=CCs!$A2),--(SWCs!$H$2:$H$40000=CCs!$C2),--(SWCs!$I$2:$I$40000=CCs!$D2),--(SWCs!$E$2:$E$40000<>"A"),--(SWCs!$X$2:$X$40000<>""),(SWCs!$AB$2:$AB$40000))),"--",SUMPRODUCT(--(SWCs!$G$2:$G$40000=CCs!$A2),--(SWCs!$H$2:$H$40000=CCs!$C2),--(SWCs!$I$2:$I$40000=CCs!$D2),--(SWCs!$E$2:$E$40000<>"A"),--(SWCs!$X$2:$X$40000<>""),(SWCs!$AB$2:$AB$40000))))

Cols H & I ... Cell H2 down & Cell I2 down ... (same as Col G ... different
target)

WorkSheet ... SWCs ... (40000 records (Range A1:AF40000) ... Row 1 = Header)

Formulas in Cols O:AF (excluding Cols Q, T & AA which are Blank)

What I think my “choking†formulas might be ... Looking for options to
simply?:

Col U ... Range (U2:U40000)

=IF(OR($B2="",SUMPRODUCT(--(ACTs!$A$2:$A$40000=SWCs!$B2),--(ACTs!$B$2:$B$40000=SWCs!$C2),(ACTs!$E$2:$E$40000))=0),"",SUMPRODUCT(--(ACTs!$A$2:$A$40000=SWCs!$B2),--(ACTs!$B$2:$B$40000=SWCs!$C2),(ACTs!$E$2:$E$40000)))

Col V ... Range (V2:V40000) ... (same as Col U ... different target)

My "Thanks" to all who support these boards & provide so much “magic†... Kha

 

[Pete_UK]

One thing to bear in mind is that XL2003 maintains a table of up to
64,000 dependencies (i.e. cells which depend on others for their
values), so that in a small(ish) workbook a change in one cell will
only cause those cells which are dependent to be recalculated. Thus
such a "small" workbook can appear to recalculate very quickly.
However, where there are more than 64,000 dependent cells, XL will
then recalculate the whole workbook, and so large workbooks can appear
to recalculate much more slowly. This will happen in your case.

Another thing to consider is if you have a formula like:

=if(isna(MATCH(...)),"",MATCH(...))

then this is inherrently inefficient, as you are evaluating the MATCH
twice - better to put the MATCH part in a helper column to calculate
once and then amend your formula to:

=if(isna(helper_value),"",helper_value)

The helper column can be hidden from view. Your formula in column C is
like this, and indeed those in D, E and F - it's only the INDEX part
that changes, so you can have something like:

=if(isna(helper_value),"",INDEX(...,helper_value,...))

A third thing to bear in mind is that array formulae (including
SUMPRODUCT) can be very slow for large ranges and many conditions, and
in column G you have one with a 40,000 row range and 5 conditions,
which you seem to be calculating twice anyway because of the IF. SUMIF
is a much faster alternative, and by re-designing your data (eg by
having a helper column in SWCs sheet which joins together G2&H2&I2 etc
and a similar column in the CCs sheet), you might be able to change
the formula to SUMIF.

Well, this should give you some pointers - hope it helps.

Pete

 

[Ken]

Pete ... (Good morning)

Thank you for responding to this post ... Last week I had a class to take &
a vaca day to burn (still 18 to burn by end of year) so I am just getting
back to you know ... I am reworking this spread sheet to incorporate some of
your suggestions. I have been able to take out many of the "check" portions
of my formulas ... I have added a certain amount of "Helper Columns" (still
reviewing/revising) & I have successfully combined Cols G&H&I all as you
recommended.

Above said ... this is still a work in process, but I am getting there ...
Thank you for the guidance ... Kha