Easier way to apply formulas to reduce file size?

[Legacy 352679]

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]

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 !

 

[Peter_SSs]

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.
If not, what does the ".. and no" mean?

 

[Legacy 352679]

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.

Windy Harbour 2012-2001.xlsm

Shared with Dropbox

www.dropbox.com

Rottnest Island 2019-1990.xlsx

Shared with Dropbox

www.dropbox.com

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.

 

[Legacy 352679]

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.
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]

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

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]

I think we can assist with the chart as well, down the track, so the whole thing can be automated

 

[Legacy 352679]

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.

 

[Legacy 352679]

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]

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 !