[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Technical question regarding file size - large file with multiple sheets or single files?

Posted on 2011-10-07
8
Medium Priority
?
249 Views
Last Modified: 2013-11-05
We have a file which we use for calculating stock movements for a particular department/process in our factory. This file requires a number of manual inputs, copied from paperwork which can on occasions lead to errors or misinterpretations of the data. This file has a sheet for each week with 10-15 columns and 40-50 rows of data per day. Each week is the same format and size, closing stock from one week carried forward to the opening stock of the next. All very simple sums - input values, output values, expected stock, actual stock, variances etc. The file as it stands could potentially end up with 52 sheets of data.

The gist of the question is this: The file is already heading up towards 16Mb. If I were to split this into 52 separate files, one sheet per file, would it be the same total size; thinking maximising use of network storage resource.

By splitting to separate files, the input data could then be on a separate sheet replicating the data sheets.

Saving a blank file is circa 15Kb so I am assuming having 52 files would have this as a minimum/start point so less than 1Mb for the 52 files.

Splitting the 16Mb file into its respective 40 sheets (40 weeks so far this year) would suggest that they would be circa 0.5Mb each including the minimum start point for a blank file.

Is this a correct assumption to make???

I realise having the input sheets would increase the file size but I wouldn't have thought this would be a large increase.

Any pointers greatly appreciated.  Technical issues not a problem, simple sums etc and might need a bit of VBA to copy to new workbook etc but I can manage that.

Thanks
Rob H
0
Comment
Question by:Rob Henson
  • 4
  • 3
8 Comments
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36931427
I think it depends how many sheets you are planning to end up with.  If say you cap it at a year and end up with 52 sheets, that would be acceptable. However, if you start to get 100+ sheets this can begin to cause problems.  I have recently had to reengineer a couple of workbooks with 170+ sheets each which were getting very slow to open and difficult to navigate around.

It will also depend on how much consolidation of data you need to do across the sheets.  As you say you could have a vba solution that gathers the information, or cross -workbook links, but again in Excel 2007 I have found external links slow to update and very slow to relink if the source data workbook changes.  In my solution I needed a lot of consoidated data and I found the whole workbook much more efficient by having one 'current data' sheet for the most recent week and moving all historic data into database table-like rows on a single sheet. Even with nearly 300,000 rows, this still performed well (obviously in 2007 or better).  Of course arranged like this it's easier to move into a real database at a later stage if you need to.

On the size I don't think it will make too much difference  - we have 100MB+ workbooks that are quite happy (although not all are).
0
 
LVL 17

Expert Comment

by:Gerald Connolly
ID: 36936891
You should also consider using a back-end database like Access or SQLserver to actually hold your data, this along with a bit of VBA might well solve all your problems.

But what problems do you have? Why is a 16MByte (de facto useage "B" = Bytes, "b" = bits) a problem?
0
 
LVL 34

Author Comment

by:Rob Henson
ID: 36940942
Thanks for the comments so far.  For starters we don't have MS Access, Lotus Approach maybe but limited knowledge about its use and there isn't sufficient data manipulation to warrant SQL. The data relates to about 40 products and their movements (Opening Stock, Goods In, Goods Out & Closing Stock) on a daily basis.

There isn't any problem with the file already being 16MB and it works fine as it is when it is being used by the usual person.

However, when that person is on leave the file has to be used by some others. As this doesn't happen very often, the amount of knowledge regarding its use retained between cover periods is limited and mistakes are made which have a major impact on our factory results.

What I am looking to do is simplify the input of data into the file by having input sheets that replicate the paper data sheets that are used. In doing so though, I would have to overwrite values in previous week sheets or separate them out to another file, hence the question: Would 52 separate files be the same size as one file with 52 separate sheets?

Thanks
Rob H
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 17

Expert Comment

by:andrewssd3
ID: 36941015
Rob - the version will be important here.  Microsoft claim that 2007 and beyond files are up to 75% smaller than 2003 and earlier, and for once for a MS claim, this does seem to be true.  In 2007 you cetrainly do get some economies of scale in a single workbook with things like shared strings, and this may also be the case in earlier versions.

What version are you on?
0
 
LVL 34

Author Comment

by:Rob Henson
ID: 36941786
Excel 2003 with no plans to upgrade in the near future!

Thanks
Rob H
0
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 500 total points
ID: 36942011
On a quick test of this I created a master sheet in 2003 format with 52 sheets, with 15 cols and 50 rows of data on each sheet, with a mixture of text, numbers and formulae.

The master file is 858KB.  Splitting this into 52 individual files, results in files each with a size of 41 or 42KB (they're all identical, so the size difference must be to do with physical disc allocation).  The total size for them is 2.09MB.  That is sort of what I'd expect for smallish files - you're going to have more fixed overhead per individual file.  

Your master file is much bigger, either reflecting its history, standard Excel bloat, or some other factors like pivot table cache files, etc.  Given this I'd expect your individual files to take up a smaller proportion of the original file size - I would think my example probably represents  the worst case, as the master file was newly created and had not had time to start bloating.
0
 
LVL 34

Author Comment

by:Rob Henson
ID: 36942242
Hi,

Just looked at the current file and it is actually:

89 Sheets
172 Columns per sheet (6 days x 27 per day plus header columns)
54 Rows per sheet.

Assuming 1 data unit per cell, this is 826,632 Units.

The amended file plan would be:

2 Input sheets - 8 columns by 20 rows
6 Data Sheets
37 Columns per sheet (existing 27 plus headers)
54 Rows per sheet.

Same data unit assumption would give 12,148 per file so the equivalent time period worth would be 1,081,172 units, an increase of approx 31%. I suspect this is due to repetition of certain aspects of the data, row headers etc. and the base data for the creation of a file.

I guess its now down to me to answer the question whether the ease of use is worth the increase in file size.

Its been good to throw this in the air to get discussion. Thanks to all for their input.

Thanks
Rob H
0
 
LVL 34

Author Closing Comment

by:Rob Henson
ID: 36942287
Thanks for your input!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question