Handling Large Files
 

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...

Handling Large Files
 

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.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"RJB" wrote in message
...
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...

Handling Large Files
 

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
--
HTH...

Jim Thomlinson


"RJB" wrote:

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...

Handling Large Files
 

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?

"Jim Thomlinson" wrote:

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
--
HTH...

Jim Thomlinson


"RJB" wrote:

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...