Handling Large Files

[RJB]

I have a file with a data dump.

It has seven worksheets, each about 30 columns by 21,000 rows. Most of the
info is text, although I have about three columns with Vlookups, and four
others with some basic multiplication formulas.

As you may have guessed, it's about 100MB and realllllyyyy
sssssllllloooowwww - to load, to save, to think.

I've stripped out as many formulas as I can.

The only thing I can think of is to break it into seven individual files,
all referencing the same lookup file. And then have a separate summary file?
Will that speed things up any?

Thanks...

 

[Don Guillett]

Probably not. Try breaking up the lookup range, if possible. I did this sort
of thing recently for a school system. If all else fails, you may send to me
with a complete explanation.

 

[Jim Thomlinson]

From your description no matter what you do you are going to have problems.
XL is not designed to do this kind of thing. You are wanting to tow a semi
trailer with a bicycle. The only way to get good performace will be to swtich
to using a database such as Access which is designed for the task you
describe. If that is not an option then you will have to live with the
performace issues. Here is a good link to some information that will help you
to understand how to tweak your spreadsheet to get the best performance...

http://www.decisionmodels.com/index.htm

 

[RJB]

Yeah, kind of what I figured.

I know a smidge about Access; I know how to create relationships, a la
VLOOKUPS. Can I do things like SUMPRODUCT in Access?