Optimize calculation speed

[2Ruff4U]

I have a rather large spreadsheet that takes a very long time to calculate once the new data is added. One of the many things I need to do is look to see if a unique value in range 1 is also in range 2. If it is, return some data (vlookup), if it's not, then I want a "0", not #NA. My question is, which of these two methods will result in a faster calculation (if at all):

Option 1: Do it in one step
=IF(ISNA(VLOOKUP($A3,LY,3,FALSE)),0,VLOOKUP($A3,LY,3,FALSE))

Or option 2: Do it in two steps:
Column N formula:
=VLOOKUP($A3,LY,3,FALSE)

Column N+1 formula:
=IF(ISNA(Column N value),0,=column N value)

I have many columns of data using formulas in option 1, so if I have coded this badly that could be my problem...

Thank you for your insights!

 

[schielrn]

My first question is what is the range for the named range LY? Also can LY be sorted in ascending order, or is that not feasible?

 

[2Ruff4U]

LY is data from last year. The lookup formula is in the "this year" section and checks to see if a unique identifier this year also appears last year. The last year data is 5 columns wide (as is the this year data) and can range from 1,000 to 100,000 rows, depending on the client.
Thank you for your reply...

 

[Aladin Akyurek]

What is the actual range LY stands for, and is this range sorted in ascending order on its first column?

 

[2Ruff4U]

The Range for LY is always A3:Ex on a separate tab (where "x" can be anywhere from 1,000 to 100,00). The data file I import does not come sorted, but I could sort it either before or after the import...

 

[Aladin Akyurek]

The Range for LY is always A3:Ex on a separate tab (where "x" can be anywhere from 1,000 to 100,00). The data file I import does not come sorted, but I could sort it either before or after the import...

If you can sort it in the said manner and keep the data that way, then invoke:

=IF(LOOKUP($A3,TableTab!A:A)=$A3,LOOKUP($A3,TableTab!A:A,TableTab!C:C),0)

instead of:

=IF(ISNA(VLOOKUP($A3,LY,3,FALSE)),0,VLOOKUP($A3,LY,3,FALSE))

 

[2Ruff4U]

Thank you for your reply. I assume both of our solutions ultimately accomplish the same thing, are you suggesting that your formula will be less of a drag on the processing time of the original formula? If so, why?

 

[Aladin Akyurek]

Thank you for your reply. I assume both of our solutions ultimately accomplish the same thing, are you suggesting that your formula will be less of a drag on the processing time of the original formula? If so, why?

The LOOKUP version with the sorted data will be very fast for it will run on a binary search algorithm, while the VLOOKUP version has to use a linear search and moreover must compute things twice.

 

[2Ruff4U]

Again, thank you for your insights - I shall now make use of the F2 key...

 

[Fazza]

Hi,

You mention a data file import. This suggests there might be totally different approaches that are suitable - and potentially much faster. Such as not importing all the data and using SQL to do the work currently done by formulas. Or even if the data is imported entirely, still using database type approaches - that is not using formulas. With large amounts of data large number of formulas can be slow.

Of course these are only very general comments. To be able to give more specific advice requires an understanding of the tasks being done.

I recall at least one example in past forum threads where formulaic approaches took over an hour to calculate whereas non-formula database type approaches took about a second for the same result.

Regards, Fazza