Slow Calcualtion Time...need tips

[jjaeger]

I have a worksheet with about 30-60k rows of data. The workbook has 5 vlookups. Each time my worksheet calculates, it takes 1-2 minutes. I know that I can turn the calculations to manual, but I am looking for any tips that might help speed this up. Any help is appreciated.

Thanks,
Jay

 

[Aladin Akyurek]

I have a worksheet with about 30-60k rows of data. The workbook has 5 vlookups. Each time my worksheet calculates, it takes 1-2 minutes. I know that I can turn the calculations to manual, but I am looking for any tips that might help speed this up. Any help is appreciated.

Thanks,
Jay

Would you post your VLOOKUP formulas which seem to slow down your wb?

 

[jjaeger]

=IF(L16="drug",K16,VLOOKUP(C16,'PIVOT 2'!$A$4:$B$21885,2,FALSE))
=VLOOKUP(C17,'PIVOT 1'!$A$4:$B$19512,2,FALSE)
=VLOOKUP(C17,'PIVOT 3'!$A$4:$B$25000,2,FALSE)
=VLOOKUP(M20,'Drug Lookup'!$A$3:$B$1621,2,FALSE)
=VLOOKUP(K21,Drug_Supply!$A$2:$C$31072,3,FALSE)

 

[T. Valko]

=IF(L16="drug",K16,VLOOKUP(C16,'PIVOT 2'!$A$4:$B$21885,2,FALSE))
=VLOOKUP(C17,'PIVOT 1'!$A$4:$B$19512,2,FALSE)
=VLOOKUP(C17,'PIVOT 3'!$A$4:$B$25000,2,FALSE)
=VLOOKUP(M20,'Drug Lookup'!$A$3:$B$1621,2,FALSE)
=VLOOKUP(K21,Drug_Supply!$A$2:$C$31072,3,FALSE)

Using INDEX/MATCH will be faster to calculate on large ranges.

Try these...

=IF(L16="drug",K16,INDEX('PIVOT 2'!$B$4:$B$21885,MATCH(C16,'PIVOT 2'!$A$4:$A$21885,0)))

=INDEX('PIVOT 1'!$B$4:$B$25000,MATCH(C17,'PIVOT 1'!$A$4:$A$25000,0))

=INDEX('PIVOT 3'!$B$4:$B$19512,MATCH(C17,'PIVOT 3'!$A$4:$A$19512,0))

=INDEX('Drug Lookup'!$B$3:$B$1621,MATCH(M20,'Drug Lookup'!$A$3:$A$1621,0))

=INDEX(Drug_Supply!$C$2:$C$31072,MATCH(K21,Drug_Supply!$A$2:$A$31072,0))

For tips on calculation efficiency see:

http://www.decisionmodels.com/

 

[Aladin Akyurek]

=IF(L16="drug",K16,VLOOKUP(C16,'PIVOT 2'!$A$4:$B$21885,2,FALSE))
=VLOOKUP(C17,'PIVOT 1'!$A$4:$B$19512,2,FALSE)
=VLOOKUP(C17,'PIVOT 3'!$A$4:$B$25000,2,FALSE)
=VLOOKUP(M20,'Drug Lookup'!$A$3:$B$1621,2,FALSE)
=VLOOKUP(K21,Drug_Supply!$A$2:$C$31072,3,FALSE)

If you can sort 'PIVOT 2'!$A$4:$B$21885 and other tables in ascending order on column A, a much faster look up formula is possible.

1.

=IF(L16="drug",K16,LOOKUP(C16,'PIVOT 2'!$A$4:$A$21885)=C16,
   LOOKUP(C16,'PIVOT 2'!$A$4:$A$21885,'PIVOT 2'!$B$4:$B$21885),"")

2.

=IF(LOOKUP(C17,'PIVOT 1'!$A$4:$A$19512)=C17,
   LOOKUP(C17,'PIVOT 1'!$A$4:$A$19512,'PIVOT 1'!$B$4:$B$19512),"")

3.

=IF(LOOKUP(C17,'PIVOT 3'!$A$4:$A$25000)=C17,
   LOOKUP(C17,'PIVOT 3'!$A$4:$A$25000,'PIVOT 3'!$B$4:$B$25000),"")

4.

=IF(LOOKUP(M20,'Drug Lookup'!$A$3:$A$1621)=M20,
   LOOKUP(M20,'Drug Lookup'!$A$3:$A$1621,'Drug Lookup'!$B$3:$B$1621),
   "")

5.

=IF(LOOKUP(K21,Drug_Supply!$A$2:$A$31072)=K21,
   LOOKUP(K21,Drug_Supply!$A$2:$A$31072,Drug_Supply!$C$2:$C$31072),
   "")

 

[Fazza]

Another option might be to replace the formulas with UPDATE queries. Is that of interest?