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?

 

[Peter_SSs]

I wish I could use a small sample of data, but there could be 28 columns and 366 rows times by 100 stations for one year alone.

Couldn't we have some dummy data that was 4 columns and 5 rows times 2 stations and 2 years using XL2BB just to demonstrate the concept?

 

[Legacy 352679]

But we only need to see a SMALL sample to try and acertain the best way to provide a solution! It doesn't need to be 28 columns and 366 rows times by 100 stations
So....rather than populating the entire columns on Sheet2 in advance, why not apply the formulas to the raw data whan it is pasted in Sheet2 via a macro.
If this is an option you will need to supply the formulas and which column / columns they go into ??
You don't say which version of Excel you are using, but there could be millions of redundant formulas on the sheet !!

I'll be able to provide the formulas tomorrow. If this can be applied through a macro for new data that's entered that would be fantastic, I just wouldn't know how to go about setting it up. But I'll reply tomorrow with everything. It's 2016 as far as I'm aware.

But you're correct, I have redundant formulas just sitting not doing anything. The intention was to have it so that who ever used it next to add data would just have the results straight away, as I won't be the only person adding to the database.

 

[Legacy 352679]

Ill

Couldn't we have some dummy data that was 4 columns and 5 rows times 2 stations and 2 years using XL2BB just to demonstrate the concept?

I'll provide the data tomorrow when I'm back at my workstation. Would it be easier if I just make a dummy excel file for you and attach it?

 

[Michael M]

Ill

I'll provide the data tomorrow when I'm back at my workstation. Would it be easier if I just make a dummy excel file for you and attach it?

You can make a file and upload to a hosting site, Dropbox for instance. Then post the link back here

 

[Legacy 352679]

So unfortunately my workplace doesn't allow access to dropboxes, not this website so I'm going to try my best to demonstrate my formulas in tables written on my phone. So apologies for any additional confusion.

Example raw data that goes into sheet 1:

Station	Date	Temp	STP	VISIB	GUST	MIN	MAX
1010	01/01/1990	20	18.9	999	32	2	999
1010	02/01/1990	25	14.6	564	999	6	999
1010	03/01/1990	23	17.1	999	32	999	65

This is only an example of the data, as the columns go to AB. The data is for daily information for the year.

Formulas in sheet 2:

Column a: =IF('Raw Data'!F2&" "&'Raw Data'!A2="","",('Raw Data'!F2&" "&'Raw Data'!A2)

Column b: =IF(R2="",01/01/1900,R2)
Column r: =IF('Raw Data'!B2="","",'Raw Data'!B2)
The reason why I have the date the way I do is, I have a pivot table on sheet 3 that reads all the cells for column A - L in sheet 2. I needed dates in every cell for column B so that the pivot table would continue to group by dates properly and automatically, so when someone was entering new raw data into sheet 1, all they had to do was refresh the pivot table in sheet 3 and it would continue to group by date with the new data. I wanted to avoid people having to update the data select range because not everyone is comfortable with excel.

column C: =IF('Raw Data'!G2="","", IF('Raw Data'!G2>0.1, IF('Raw Data'!G2<900, CONVERT('Raw Data'!G2"F","C"))))
column d: =IF('Raw Data'!I2="","", IF('Raw Data'!I2>0.1, IF('Raw Data'!G2<900, CONVERT('Raw Data'!I2"F","C"))))

The reason why I have >0.1 and <900 is because I'm converting from F to C. Any value that is 0 is -17.8, and since the formula is applied to every cell in column C and D on sheet 2, all the cells that don't have raw data in sheet 1 are being treated as 0 in sheet 2, and therefore when I was doing my averages in my pivot table in sheet 3, given that I was using every cell for these columns, it was skewing the data. Of course, if I can generate a macro for sheet 2 to only read raw data that is populated, that would then change the need to filter out 0 data.

The reason for the 900 is because every data point that is missing within the data is 999 or 9999. It's a **** way the raw data is given.

Column E: =IFERROR(S2,"")
Column S: =IF('Raw Data'!G2="","",100*(EXP((17.625*D2)/(243+D2))/EXP((17.25*C2)/(243.04+C2))))
Again, I have the if error formula because of the pivot table and so that the cell in column E would be blank and not skew the data.

Column F-L are simple filtering formulas that are along the long the lines of =IF(RAW DATA"","",IF(RAW DATA>999,"",RAW DATA))

A lot of this is pretty much just for the pivot table. If I didn't have/need that, then I could just click and drag the formulas down and save a lot of space. But because the pivot table reads every cell from A-L in sheet 2, and because I wanted to keep the grouping in dates, I've done it this way.

So far everything works, I'm just looking to reduce the file size.

I apologise if this ends up being formatted poorly. I'm on my phone again as there are restrictions at work.

 

[Michael M]

This code will populate Sheet2 in the columns you have provided the information for down as far as there is data in the RawData sheet
I haven't provided formulas for Columns F-L, as you haven't given us specific formulas to translate.
Make sure you try this on a copy of your workbook, in case it isn't what you actually needed....Also make sure sheet 2 is empty of ALL formulas

Sub MM1()
Dim lr As Long, r As Long
lr = Sheets("Raw Data").Cells(Rows.Count, "A").End(xlUp).Row
With Sheets("Sheet2")
    .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]

A couple of changes, as the formulas you provided contained typo errors

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

 

[Legacy 352679]

A couple of changes, as the formulas you provided contained typo errors

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

I appreciate everything everyone has done. I'm going to work from home on this later tonight and I will update everyone tomorrow. I've found a few issues in my formulas and have amended them so the sheets I'm working on are a little simpler. I just realised that all my FALSE values were being calculated and again, skewing my data.

 

[Michael M]

So, why not upload a SMALL sample workbook to dropbox from home ??

 

[Legacy 352679]

So, why not upload a SMALL sample workbook to dropbox from home ??

I can't email my raw data to myself due to our servers preventing outgoing mail to anyone other than @ourdomain, so when I say work from home, I meant like one or two formulas using made up information just to see if the functions work.

But, after finding what was wrong with my formulas whilst at work, and applying the information you've given me, I've managed to mostly get the macro up and running, which is fantastic. I haven't tried it on a large set of data yet, but for the small things it's working well.

This will significantly reduce the file size and has shown me how to apply this for future reference, even if I'm not too familiar with what everything means in the macro just yet.