G

#### Guest

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