Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Large File due to over 117,000 formulas 1

Status
Not open for further replies.
Shippwreck

Shippwreck

Programmer
Oct 20, 2003
136
GB
Hi,

I have a file that has 15 sheets. 13 of them record data for all staff in an organisation and record 21 different items per member of staff. The most number of staff at any one organisation is under 1,000 so i have given each sheet 1,000 rows for data entry. The problem now is that it is 40Mb big.

I have done all the basic things like selecting all blank cells and clearing them and then selecting all unused rows and columns and deleting them, and the file is saved as a .xls in the same version as excel that i'm using.

I have realised the problem is with the formulas. There are two main formulas in these cells and they are:

This one appears 1000 times in each sheet:
=IF(ISNA(AND(MATCH($B13,'Groupings'!$E$2:$E$153,0)>0,
MATCH($C13,'LDP Groupings'!$F$2:$F$77,0)>0))=TRUE,
IF(ISNA(MATCH($B13,'Groupings'!$E$259:$E$354,0)=TRUE),
IF(ISNA(MATCH($B13,'Groupings'!$E$355:$E$408,0)=TRUE),
IF(ISNA(MATCH($B13,'Groupings'!$E$409:$E$445,0)=TRUE),
IF(ISNA(MATCH($B13,'Groupings'!$E$446:$E$764,0)=TRUE),
IF(ISNA(MATCH($B13,'Groupings'!$E$2:$E$258,0)=TRUE),
"InvalidCode","Ungrouped"),"Group1"),"Group2"),"Group3"),"Group4"),"Group5")

This one appears 8000 times in each sheet:
=IF($A13="Group5",$F13*'04-05 Q2'!$B$4,IF($A13="Group4",$F13*'04-05 Q2'!$B$5,IF($A13="Group3",$F13*'04-05 Q2'!$B$6,IF($A13="Group2",$F13*'04-05 Q2'!$B$7,IF($A13="Group1",$F13*'04-05 Q2'!$B$8,IF($A13="Ungrouped",$F13*'04-05 Q2'!$B$9,0))))))

As you can see they are a bit complicated. Apart from making these a function in VBA is there any other way to reduce the file size?

I am sure it is these formulas that are making the file large because when i only have 10 rows in each sheet the file size is under 1 Mb and when there are 1000 it is 40 Mb

Any ideas would be great, (apart from using a database as my boss doesn't want that and anyway they are a pain to send via e-mail)

Thanks

Shippwreck

 
Sort by date Sort by votes
So 15 sheets
1000 rows per sheet - all stacked with formulae.

Sorry but your file is gonna be big. Full stop

You obviously know the alternatives as you have mentioned them - you either need to load a formula to a row only when needed (ie at each entry of new data) or you need to port over to a database - bearing in mind that you can extract data OUT of a database if you need to email it !

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question
 
Upvote 0 Downvote

If you don't share the file or don't track changes I would threaten my boss with manual input of values to reduce size, since the VBA function or database in not an option!
 
Upvote 0 Downvote
Geoff is right, it is going to be big no matter what, BUT, your formulae are very inefficient and can be improved.

For example, the first formula:
=IF(ISNA(AND(MATCH($B13,'Groupings'!$E$2:$E$153,0)>0,
MATCH($C13,'LDP Groupings'!$F$2:$F$77,0)>0))=TRUE,
IF(ISNA(MATCH($B13,'Groupings'!$E$259:$E$354,0)=TRUE),
IF(ISNA(MATCH($B13,'Groupings'!$E$355:$E$408,0)=TRUE),
IF(ISNA(MATCH($B13,'Groupings'!$E$409:$E$445,0)=TRUE),
IF(ISNA(MATCH($B13,'Groupings'!$E$446:$E$764,0)=TRUE),
IF(ISNA(MATCH($B13,'Groupings'!$E$2:$E$258,0)=TRUE),
"InvalidCode","Ungrouped"),"Group1"),"Group2"),"Group3"),"Group4"),"Group5")

contains multiple matches to the same sheet. If you put the appropriate Group name in cells F2:F764 of sheet "Groupings", you could simplify this formula very easily, by using INDEX with MATCH.

And as for your second formula, this is much simpler:
Code: 
=$F13*OFFSET('04-05 Q2'!$B$3,MATCH($A13,{"Group5","Group4","Group3","Group2","Group1","Ungrouped"},0),0,1,1)

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.
 
Upvote 0 Downvote
Wow,

Thank you so much Glenn, you are wonderful, if i could kiss you i would as that has helped an absolute ton!

I haven't implemented the first suggetsion, however the second one i have just finished and the file has gone from 40.2 Mb to 12.8 Mb

Now i call that a result

I'm gonna go for the first one next, and if i get any problems i hope you wont mind if i ask here for some help (although i hope i wont have to)

So anyway, thank you and have a star cause to me you are one!

Thanks

Matt

 
Upvote 0 Downvote
My pleasure [smile] .

If you get any problems, ask here again, and someone will be able to help, even if I happen to be too busy to do it myself.



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.
 
Upvote 0 Downvote
Just one thing more:

I think that Excel does not calculate values correctly if there are more than 55 formulas on a sheet. Just for case, check your formula results randomly.

Regards,



 
Upvote 0 Downvote
uuuum - where on earth did you get that from ??

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
Seconded Geoff, I'd like to know where this idea came from too!!!!

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.
 
Upvote 0 Downvote
Hi,

First off, yeah thirded, what the?????, all my formulas work perfectly...(or should i say correctly as they were obviously far from perfect ;) )

Anyway, the reason for this post is about the 1st suggestion by Glenn.

I have been playing around with Index and Match for the past couple of days, however i fail to see how to use them to make the formula more efficient. (That said i'm sure glenn is right, i'm just being dense)

So far i have named all the ranges, so ie
'Groupings'!$E$259:$E$354
has been named group_4

I understand how Index and Match work together, just not how to make them put the correct value in cell A13 (the cell that contains the formula), so...

help anyone.

Thanks for the help

Matt

 
Upvote 0 Downvote
Hold on,

I might be getting somewhere now,

Glenn i found your post to the guy with the school date problem and it has shown me some more things about index and match, let me have a play first,

and it works for me too...

 
Upvote 0 Downvote
Hi Matt,

here's what I was trying to suggest ... put the string "Group4" in cells 'Groupings'!$F$259:$F$354 and entries in the rest of that column for the other group names, and then do
=INDEX('Groupings'!$F$2:$F$746,MATCH($B13,'Groupings'!$E$2:$E$746,0))
to get the group name for an entry.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.
 
Upvote 0 Downvote
Ah yes i see what you're doing,

however that wont account for the and at the start of my query. I'll explain the data a little bit to make things clearer.

I have a list of 700 odd codes and they are grouped. However group 1 is a subset of group 5 and the only way to determine whether this instance of the code falls into group 1 or 5 is by using a second set of codes. I'll show below:

Code 1 |Code 2
M001 |MC10
M002 |MC11
M003 |MC12
M004 |MC13
M005 |MC14

Now if the code matches code 1 and matches code 2 then they are Group 1, if the code matches code 1 only then they are Group 5. (codes 1 and 2 are mutually exclusive of each other, so for instance if code 1 = M001 and code 2 = MC14 they are Group 1, if code 1 = M001 and code 2 = MC15 (a non existent code they are Group 5)

Now these only account for about 100 of the 700. for the other 600 your formula would work fine, its just these ones that are the pain as i need to match off against two independent columns.

I hope that all makes sense

Matt

 
Upvote 0 Downvote
You could do an IF with an extra check. It means having the INDEX(MATCH twice in the formula, like this:


=IF(INDEX('Groupings'!$F$2:$F$746,MATCH($B13,'Groupings'!$E$2:$E$746,0))="Code1",IF(ISNA(MATCH($C13,'LDP Groupings'!$F$2:$F$77,0)),"Code5","Code1"),INDEX('Groupings'!$F$2:$F$746,MATCH($B13,'Groupings'!$E$2:$E$746,0)))



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.
 
Upvote 0 Downvote
Aha,

Yes that works and greatly reduces the formula, thank you very very much

(I used to have it say Invalid Code when there was no code or the code was not valid, but #N/A will be fine)

Now, here's a question, I have added come conditional formatting to one sheet, (1000 Cells) that says =isna(A13) and has the format set to make the cell red, but this does not seem to change the file size at all. I was expecting to see the file size rise again when i did it, can anyone answer as to why it didn't, it was EXACTLY the same!

Thanks again Glen

Matt

 
Upvote 0 Downvote
I usually don't send spreadsheets out with formulas because there is alway some "know it all" who goes in and screws something up. I usually run a macro that selects everything on all of the tabs, and then copies and pastes special (values) back on top of itself. By stripping out the formulas, it typically reduced file size by more than 60%.
Code: 
    Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
    Sheets("Sheet1").Activate
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
Upvote 0 Downvote
Just to answer almir, the only limit I know of regarding the number of formulas being linked to calculation is that when you have more than 65,536 dependencies, you will see 'Calculate' in the status bar permanantly, and Excel will now perform a full calculation as opposed to just updating changed cells (Normal behaviour until you hit this limit).


Failing that the answer could never be 55 because everybody knows it is 42 :)

Regards
Ken...........

----------------------------------------------------------------------------
[peace]It's easier to beg forgiveness than ask permission[2thumbsup]
----------------------------------------------------------------------------
 
Upvote 0 Downvote
Hi 4281967,

Its funny you mention that because today a friend gave me code to do exactly the same, however you don't need to adjust worksheet names in it, see below:

Code: 
Sub Formula_Zapper()     
Worksheets.Select     
Cells.Select     
Selection.Copy     
Selection.PasteSpecial Paste:=xlPasteValues     
ActiveSheet.Select     
Application.CutCopyMode = False 
End Sub

Just so you know, i had to send out the sheet in question with the formulas intact as it was for them to fill in and use on an ongoing basis without any intervention from me.

I finally got the file size down to 7Mb before i sent it out!

Thanks

Matt


"Google is god...of the internet" - Me
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Damnitalldaddy
  • Locked
  • Question
excel formulas
Replies
1
Views
82
mintjulep
M
kwbMitel
  • Locked
  • Question
I can't add sheets to my Excel file because it's protected.
Replies
4
Views
646
StilLearningAlot
StilLearningAlot
Rick_Stanich
  • Locked
  • Question
Save active Excel file to new location
Replies
6
Views
129
Rick_Stanich
Rick_Stanich
kwbMitel
  • Locked
  • Question
Lost edited data in Excel File
Replies
3
Views
134
Scott24x7
Scott24x7
Metzke
  • Locked
  • Question
How to Transfer Licence to New PC
Replies
11
Views
392
teknance
teknance

Part and Inventory Search

Sponsor

Back
Top