Excel 2K3 calculation crashing file.

[BJZeak]

I have been spreadsheet power user for 30 years ... but ... this one has me stumped ... I am attempting to consolidate some SQL data with records in the range of 30k each ... 30K is well within the 256K limits of 2k3 ... I am currently analizing 2 tables at a time by Generating 6 columns of vlookups at 30K each followed by one column for final validation ... this exercise either hangs (> 10 minutes), crashes after several minutes, or takes several minutes to complete then crashes when I attempt to save.

All the lookup regions are sorted and in the same workbook ... the base formula being used in each of the 6 columns is:
=IF(ISNA(VLOOKUP(MID(E1,1,4),'worksheet1'!$G$2:$G$30000,1,FALSE)),0,1)

Looking for some suggestions?

Right now I am having to painfully calculate a column at a time ... convert the results back to values ... save and repeat ... I have never had to resort to anything like this before.

Wouldn't be so bad to do this for one consolidation ... unfortunately, I have around 100 tables to consolidate all with different issues (not something that can easily be resolved with a program as each iteration requires some analisys based on the calculated results)

My machine has 4G of ram (XP only uses 3G), a 2.7 GHz quad core intel processor running with XP PRO SP3 and ofs 2k3 pro

At this point I am lost for a solution ... I had suspected a hardware and or software issue ... but have now checked:

1) ram
2) mother board
3) hard drive
4) viruses
5) malware
6) MS Installations both OS and OFFICE
7) recreated the spreadsheet from scratch

all with no luck

 

[Gavona]

Check Edit,Links to ensure that your formulae don't point to an external or closed workbook. I have found formulae pointing to the version of the workbook on disk rather than the one open in memory.

saved
Right now I am having to painfully calculate a column at a time ... convert the results back to values ... save and repeat ... I have never had to resort to anything like this before.

Long ago I had this sort of issue and resolved it with a general purpose macro. I have one copy of the formula. The macro switches off calculation, copies the formula down all rows in the data table, calculates this area only, converts to values and switches calculation back to manual. This speeds things up no end.
thread707-1637165

Gavin

 

[xlbo]

vlookupsare particularly memory intensive - 6 columns of 30k rows would certainly be near an upper limit for sensible calc times

I have done similar to Gavin before - but it might be worth a try converting your vlookup to an index/match formula which is actually more efficient. In fact, you probably don;t need the index bit as you are just checking for existence by the looks of it:
=IF(ISNA(MATCH(MID(E1,1,4),'worksheet1'!$G$2:$G$30000,1,FALSE)),0,1)


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

wouldn't that be, for MATCH
[tt]
=IF(ISNA(MATCH(MID(E1,1,4),'worksheet1'!$G$2:$G$30000[highlight],0[/highlight])),0,1)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Furthermore, I might be inclined to do the comparison in MS Query, rather than lookup formulas.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

Thanks Skip - of course it should!

personally I would load G2:G30000 (or whatever your lookup data is) into a database table and do it all in the database...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question