Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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... |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting Large XML Files into CSV Files for Excel | Excel Discussion (Misc queries) | |||
Help with handling large amounts of data - determine "MIN IF" ? | Excel Worksheet Functions | |||
Modifying large CSV files | Excel Discussion (Misc queries) | |||
Handling large amounts of text in an Excel spreadsheet | Excel Discussion (Misc queries) | |||
Using large excel files | Excel Worksheet Functions |