Shrinking Size of Excel File

**Timothy100** · 01-31-2011, 04:28 PM

I have created a very large excel file that takes at least 1.5 minutes to open and .5 minutes to calcuate when refreshing using macros.

I am looking for help to reduce the size of the file which will help me to be able to access it more frequently since it takes over and freezes all other excel files and makes the entire computer run slowly.

My spead sheet is approx 330,000 MB. Not sure if I can even send that large of a file through the internet so I have not attached a link or the file on this page.

I have created an allocation system. In short, I am tying in open component purchase orders, with current inventory and running them against our production schedule to determine the date the components will be available by sales order.

The major constraints are that we have multiple sales orders that can produce using the same component on multiple lines on the same day.

To be short, the main table which uses the most memory contains the following formulas:

INDEX
IF
VLOOKUP
OR
ISNA
CONCATENATE
SUM

I use the same formulas for each part number. There are 260 parts. The primary fomula totals all the previous usages for the same component and compares it to the daily inventory.

Which is 159 columns x 152 rows per part.

**mrice** · 01-31-2011, 04:48 PM

Sounds like you might need a fresh approach here with a database type solution? It often makes sense to separate data storage from analysis.

You might try something with MS Access at the backend for storage and Excel as your analysis engine. The added benefit of storing in a database is that you can easily impose constraints to keep your data quality high (e.g. a data field must contain a date etc.)

**Richard Buttrey** · 01-31-2011, 04:50 PM

Hi,

Can you delete all but three or four parts and their contributory component details and upload a sample workbook along with some notes as to what results you expect to see.

Regards

**Timothy100** · 02-01-2011, 08:27 AM

Please disregard the first 7 parts on the "New Template Sheet". The only ones that matter are 5 digit part numbers in column B (starting with row 1077)

I expect the "New Template" sheet to contain a real date in column I that indicates the date the components will be available for that sales order taking into consideration previous orders for the same component and component deliveries.

Please note, I have included 31 parts, but I need to include 260 parts.

The sheet "New Template" is the final sheet, I copy and paste columns H and I back into the production schedule and vlookup the date by sales order into my working file.

The bulk of the calcuations are on the "First Run Through" sheet.

Please also note, I have set the "Calcuation" in the Options tab to manual because when it set to autmomatic, it shuts my computer down very often to re-calcuate.

This sheet/formulas are correct as it is in the example.

My issue is how to shrink this down from 330 MB (it's not 330,000 MB, which I originally thought) to something more managable since it takes too long/resources to open it.

Thanks in advance for your help.

http://www.sendspace.com/file/e2rqug

**Richard Buttrey** · 02-01-2011, 09:49 AM

Hi,

Even that is 78 Mb which I'm not prepared to download.

Can't you cut the rows down to a few dozen? Also do an End Home on each sheet to see where that takes you. My guess is that perhaps a formula has been copied down to the very last row which may or may not be what you expect.

Regards

**Timothy100** · 02-05-2011, 06:52 PM

Okay, there are two worksheets that are taking up all the memory. Here is one of the two. The other is the feeder sheet which fills in these columns. I've shown this one part, but I had built the tool to manage 250 parts (or sets of data). Please review to see if you can help me shrink this file. Thanks!

http://www.sendspace.com/file/orraan