Excel file freezes and stop working, file size 1GB

[ibmy]

Hi,

My Excel file freezes,stop working and took a very long time (15 minutes) to finish when apply the formula in G3 to the last row then loop next column.

3.1.xlsb
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Point A				1.20702	1.20703	1.20706	1.20699	1.20724	1.20707	1.2071	1.20712	1.20711	1.20713	1.20715	1.20714	1.20704	1.20723	1.20727
2	1.20721
3	1.20722
4	1.20723
5	1.20724
6	1.20721
7	1.20724
8	1.20722
9	1.20722
10	1.20723
11	1.20722
12	1.20723
13	1.20722
14	1.20722
15	1.20729
16	1.20732
17	1.20722
18	1.20723
19	1.20722
20	1.20722
21	1.20719
Sheet9

Cell Formulas
Range	Formula
G3:U3	G3	=IF(OR(AND($C2>G$1,$C3<G$1),AND($C2<G$1,$C3>G$1)),1,"")

Last Row is based on column C & Last Column is based on last column used in row 1.
Both Last Row and Last Column have no fix number, it vary daily data.
Last Column could be in range of 1000-2500 while Last Row could be 250k to 550k

Can formula in G3 can be converted to vba code?
...then fill down G3 to last row, loop until last column

p/s: My Excel file size change from 15MB to 1GB after apply the formula and cannot be open.

 

[mumps]

Delete all the formulae and try this macro:

Sub CalcVals()
    Application.ScreenUpdating = False
    Dim v As Variant, r As Long, c As Long, lRow As Long, lCol As Long, arr() As Variant
    lRow = Range("C" & Rows.Count).End(xlUp).Row
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column
    v = Range("C1:C" & lRow).Resize(, lCol - 2).Value
    ReDim arr(lRow - 2, UBound(v, 2) - 4)
    For r = 3 To UBound(v)
        For c = 5 To UBound(v, 2)
            If (v(r - 1, 1) > v(1, c) And v(r, 1) < v(1, c)) Or (v(r - 1, 1) < v(1, c) And v(r, 1) > v(1, c)) Then
                arr(r - 3, c - 5) = "1"
            Else
                arr(r - 3, c - 5) = ""
            End If
        Next c
    Next r
    Range("G3").Resize(lRow - 2, UBound(v, 2) - 4) = arr
    Application.ScreenUpdating = True
End Sub

 

[ibmy]

Thanks @mumps

It works but for large data, there is an error

Daily Data: daily
Row : 120k , Column : ANH
It took 20 minutes
Need to close all others program, else there is memory error.

Weekly Data : weekly
Row : 500k , Column : CFE
Error :

My system spec:

Is there any way to get the result faster for daily data or do I need to upgrade RAM?
....or do I need to use other programs that faster data processing? If so. please recommend me...

 

[mumps]

You have a huge amount of data. The approach I used places all your data in memory and analyses it in memory without having to reference the sheet so it is very fast. I'm not sure about another program (perhaps a database like Microsoft Access?) Upgrading your RAM and a faster processor may help although I don't know to what extent you would have to upgrade but given the amount of data you have, I suspect that the time factor may continue to be a problem. Perhaps other members of this Forum will have other suggestions.

 

[Logit]

Perhaps break your 1 gb workbook down into smaller sizes and analyze each segment by itself. From my experience, 1 gb is practically unmanageable for Excel to handle.

 

[ibmy]

Thanks @mumps

Thanks @Logit for suggestion.