Easier way to apply formulas to reduce file size?

Yiew33

New Member
Joined
Nov 24, 2015
Messages
43
Hey everyone,

I've managed to get all my spreadsheet/formulas working fine, but I'm running into an issue where the file size has just become huge.

One tab I've got raw data, and on another tab I've got all my formulas that, when the raw data is entered into the first tab, become populated with the filtered data that I'm interested in (i.e. raw data is in F and one formula is to convert it to C). I've made it so that these formulas on the second tab are applied to every cell in the column (all the way down to 250000 or what ever the limit is) because I'm unsure how much raw data I'm going to have, and wanted it so that any future data could simply be entered and transformed instantly. So as you can imagine, after a couple of rows, there's a lot of data being stored and a lot of cells with formulas not being used. I've also got pivot tables going on a separate tab, but they don't seem to be an issue in this case.

This has resulted in my spreadsheet being 20+mb in size.

Is there any way to make it so that the formulas on the second tab are created only when the raw data is entered? Is it possible to do this using a macro at all, remembering that in the future, additional data will be entered into the spreadsheet? Or do I just have to live with an excessively large excel file?
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Okay....so if you have further questions / issues, please post back and we'll see if we can help out !!!
BTW, the size of the data set won't matter....the code provided can basically populate the formulas to the bottom of the worksheet, if required !
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,858
Office Version
  1. 365
Platform
  1. Windows
I've managed to mostly get the macro up and running
That macro populates formulas in Sheet2 down to the last row of data in Sheet1 (Raw Data). Yet when I asked that question..
If there are 2500 rows of data on Sheet1, do you need 2500 rows of formulas on Sheet2?
Your response was ..
1) Yes and no.
Has something changed. :confused:
If not, what does the ".. and no" mean?
 

Yiew33

New Member
Joined
Nov 24, 2015
Messages
43
Okay....so if you have further questions / issues, please post back and we'll see if we can help out !!!
BTW, the size of the data set won't matter....the code provided can basically populate the formulas to the bottom of the worksheet, if required !
So, I managed to get the files off our work drive so I have them at home and can provide them via a dropbox to make everything clearer.

The VBA code you provided works perfectly, it's mostly my lack of knowledge on how to run everything smoothly that's causing me some headaches, but that's for me to workout in time.

The only actual issue that I've been encountering is that when I'm populating Sheet2 using the macro, if there are 1000 cells of raw data, the macro will populate all 1000 cells on Sheet2, but also put something (yet nothing) in row 1001. It looks like a tiny rectangle in the cell. When creating the pivot table in Sheet3, I'm selecting the range in Sheet2 by CTRLA, which is including row 1001, and as a result, the dates for the pivot table are not grouping properly.

Now, I can tell people to 'Just CTRLA and minus 1 from the final range value' but if this step can be avoided then the spreadsheet would be perfect and I can workout the rest of the small details (like adding a button to run the macro for the spreadsheet etc). So my follow-up question would be, is there a way to avoid entering anything in row 1001, or is it just the macro reading from 1001, finding that there is nothing there and not populating it?

Please note in the following examples that I have updated the formulas (although there may still be a typo or they're written inefficiently) to reflect the requirements of the way the spreadhseets will now work. Thanks to your help I've been able to cut them down a little bit.


Rottnest is the file that I got working without the use of any macro, and as such, the pivot table is exactly how I want it to be displayed visually, with the correct grouping of dates.
The Windy-Harbour file was the one I used the macro with. The data populated perfectly, but as you can see, in row 1001, there is something within the first cell.

At this stage I'm just trying to streamline the process so that anyone using the file doesn't have to take so many actions to include new data into the pivot table. If a macro could be made to include the new data also in the pivot table that would be mint, but at this stage I'm happy with how things are, as it's mostly the conversions and formulas that I wanted to avoid people having to play around with.
 

Yiew33

New Member
Joined
Nov 24, 2015
Messages
43
That macro populates formulas in Sheet2 down to the last row of data in Sheet1 (Raw Data). Yet when I asked that question..

Your response was ..

Has something changed. :confused:
If not, what does the ".. and no" mean?
Sorry Peter, that response was for when I was working with the older spreadsheet I was using. For 28 columns in the raw data, I may have only needed 8 columns in Sheet2, but to have the formula applies to every cell of those 8 columns.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

Ok, change to this code to eliminate the "blank" cell...I'm guessing it's a Non printing space!

VBA Code:
Sub MM1()
Dim lr As Long, r As Long, ws As Worksheet
Set ws = Sheets("Sheet2")
lr = Sheets("Raw Data").Cells(Rows.Count, "A").End(xlUp).Row-1
With ws
    .Range("A2:A" & lr).Formula = "=IF('Raw Data'!F2&"" ""&'Raw Data'!A2="""","""",'Raw Data'!F2&"" ""&'Raw Data'!A2)"
    .Range("B2:B" & lr).Formula = "=IF(R2="""",01/01/1900,R2)"
    .Range("C2:C" & lr).Formula = "=IF('Raw Data'!G2="""","""", IF(AND('Raw Data'!G2>0.1,'Raw Data'!G2<900), CONVERT('Raw Data'!G2,""F"",""C"")))"
    .Range("D2:D" & lr).Formula = "=IF('Raw Data'!I2="""","""", IF(AND('Raw Data'!I2>0.1, 'Raw Data'!G2<900), CONVERT('Raw Data'!I2,""F"",""C"")))"
    .Range("E2:E" & lr).Formula = "=IFERROR(S2,"""")"
    .Range("R2:R" & lr).Formula = "=IF('Raw Data'!B2="""","""",'Raw Data'!B2)"
    .Range("S2:S" & lr).Formula = "=IF('Raw Data'!G2="""","""",100*(EXP((17.625*D2)/(243+D2))/EXP((17.25*C2)/(243.04+C2))))"
End With
End Sub
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
I think we can assist with the chart as well, down the track, so the whole thing can be automated
 

Yiew33

New Member
Joined
Nov 24, 2015
Messages
43

ADVERTISEMENT

I think we can assist with the chart as well, down the track, so the whole thing can be automated
I really appreciate you taking your time to help on this project. I'll update you later today once I've had a chance to try the new code.
 

Yiew33

New Member
Joined
Nov 24, 2015
Messages
43
I think we can assist with the chart as well, down the track, so the whole thing can be automated
I've applied the new code (I saw the small change so that's a handy little tip to remember for next time) and it works perfectly. At this stage I'm still manually putting in the pivot tables which I'm ok with, because once they're there, the only time they will need changing is when new data is added and a new range is selected. I'm not sure if that's something that can be automated, but it's a simple thing to do manually so for all intensive purposes in happy with how the sheet has turned out, and thanks to your help I've managed to achieve my original objective of reducing the file size.

Thank you.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Pretty well anything you do manually, can be automated !!
If you really want to have a play with code....make a copy of your workbook, then use the macro recorder to create a new pivot table, like that one you already have....It will make a LOT of code, but once done we can remove all the redundant code. The same goes foor the charts as well !
 

Forum statistics

Threads
1,148,362
Messages
5,746,263
Members
424,001
Latest member
Sophiemakescakes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top