Why excel crashing when I drag down.

[bernardmorrison]

When I try to drag down a formula it is causing excel to crash.

I have 400,000 rows of data and the formula contains a vlookup, so this is quite intensive procedure.

However, if I write a vba sub which breaks the dragging down into 100 row bitesize pieces, it works fine. Takes a while but no crashing issues.

I am wondering is there anything I could have done to make the dragging down less intensive for excel and avoided the crashes. Apart from going to the hassle of having to write the VBA.

I am using excel 2010

 

[Trevor G]

Have you tried double click auto fill

 

[Jonmo1]

Maybe the formula itself could be made more efficient?
Would you care to post it?

 

[bernardmorrison]

Maybe the formula itself could be made more efficient?
Would you care to post it?

This is the formula:

=ISERROR(MATCH(C2,C3:$C$147969,0))

 

[Jonmo1]

I thought the formula contained VLOOKUP ?

Anyway...
Can the data in column C be sorted in Ascending order ?
If so, this would be MUCH more efficient.

=LOOKUP(C2,C3:$C$147969)<>C2

 

[bernardmorrison]

Yes, that column is sorted.

Out of interest, how come using the lookup function is more efficient than using the iserror match combo?

 

[Jonmo1]

Match with 0 as the 3rd argument is an Exact Match search.
Forcing Excel to look at every single cell in the range.

Lookup does a binary search. A little difficult to explain but I'll try.
Don't quote me, this is probably not exactly right, but close enough to get the point..

Lookup assumes the data is sorted ascending.
It doesn't check if it actually is sorted, just proceeds as if it is.

It only looks at very small fraction of values in the range.
It basically cuts the range in half, and tests if the last value of the first half is Less or Equal to the lookup value.
And proceeds to either of the 2 halves depending on result of that test.
Continuing a process of elimination by testing the last value of each half as it goes.