Make sumifs with array faster

[ouvay]

Hello all

I've written this bit of code.. it works ... but can anyone help make it faster?

Its basically a sumifs with arrays -problem is that I'm dealing with over 1.2 million rows (600k+ in one table and 600k+ in the sumifs data table) - so it takes a pretty minute

Sub totals_fromHDFC()

    Dim hdfcwb As Workbook: Set hdfcwb = Workbooks.Item("Payment Control - HDFC Leg.xlsm")
    Dim hdfc As Worksheet: Set hdfc = hdfcwb.Worksheets(1)
    Dim a() As Variant
    Dim data2() As Variant
    Dim r As Long, i As Long, NumRows As Long,  s1 As String
 
 
    With Sheet2 ''assigning main data table to an array
    NumRows = .Cells(.Rows.Count, "B").End(xlUp).Row
    data2 = .Range("D1:J" & NumRows).Value
    For i = LBound(data2) To UBound(data2)
        data2(i, 2) = data2(i, 7)
        data2(i, 3) = data2(i, 5)
    Next i
    ReDim Preserve data2(1 To NumRows, 1 To 3) As Variant
    ReDim sum2(2 To NumRows, 1 To 2) As Variant
    s2 = .Name
    End With
 
    With hdfc 'assigning table where I want to sumif, to array
    NumRows = .Cells(.Rows.Count, "B").End(xlUp).Row
    a = .Range("C1:H" & NumRows).Value
    For i = LBound(a) To UBound(a)  ''feel free to ignore this hot mess :)  its just me rearranging my array to suit my preferences
        a(i, 1) = a(i, 4)
        a(i, 4) = a(i, 2)
        a(i, 2) = a(i, 5)
        a(i, 5) = a(i, 3)
        a(i, 3) = a(i, 6)
    Next i
    ReDim Preserve a(1 To NumRows, 1 To 5) As Variant
    ReDim sum2(1 To NumRows, 1 To 2) As Variant
    s2 = .Name
    End With
 

'actual summing happens in this loop
For r = 2 To UBound(data2)
    Application.StatusBar = "Calculating " & s2 & " row " & r & " of " & UBound(data2) & "... " & Format(r / UBound(data2), "PERCENT") & " Completed"  ' just some status bar ux
    For i = LBound(a) To UBound(a)
        If data2(r, 1) = a(i, 1) And data2(r, 2) = a(i, 2) And data2(r, 3) = a(i, 3) Then
            sum2(r, 1) = sum2(r, 1) + a(i, 4)
            sum2(r, 2) = sum2(r, 2) + a(i, 5)
        End If
    Next i
Next r
 
    Sheet2.Range("AD2").Resize(UBound(sum2), 1).Value = sum2
 
End Sub

 

[RobP]

Hi,

my initial thought is that whilst in your final loop you like to keep a visual as to how many its processed.. its very costly in resources.

So, with that in mind, I'll bet if you actually timed it your code as is, and then just removed that application.statusbar update line and did it again, you'd see a reasonable improvement.

In the past, I've also used the VBA Dictionary to speed up my arrays, but that was usually for me searching for items, so not sure it can help in this case. It might be worth you taking a look at it though - maybe some experts on here could offer further guidance to that.

cheers
Rob

 

[ouvay]

Hi,

my initial thought is that whilst in your final loop you like to keep a visual as to how many its processed.. its very costly in resources.

So, with that in mind, I'll bet if you actually timed it your code as is, and then just removed that application.statusbar update line and did it again, you'd see a reasonable improvement.

In the past, I've also used the VBA Dictionary to speed up my arrays, but that was usually for me searching for items, so not sure it can help in this case. It might be worth you taking a look at it though - maybe some experts on here could offer further guidance to that.

cheers
Rob

Thanks! I'll give it a go.. but as things are I it takes a few hours to run I've got to add more to it before it's complete
I've been looking into dictionary collections and it seems very promising but I've got very little knowledge about them.. so I'm hoping someone will be able to help me out there!
Thanks for your suggestion though

 

[RobP]

Hi,

another idea I had was to re-write your loop, to just use IF's rather than multiple AND every time. I figure if the first one is not a match, it will skip the loop without having to perform a check on the second or third. It might help save a little time ?

Rob

For r = 2 To UBound(Data2)
    'Application.StatusBar = "Calculating " & s2 & " row " & r & " of " & UBound(Data2) & "... " & Format(r / UBound(Data2), "PERCENT") & " Completed"  ' just some status bar ux
    For i = LBound(a) To UBound(a)
        If Data2(r, 1) = a(i, 1) Then
            If Data2(r, 2) = a(i, 2) Then
                If Data2(r, 3) = a(i, 3) Then
                    sum2(r, 1) = sum2(r, 1) + a(i, 4)
                    sum2(r, 2) = sum2(r, 2) + a(i, 5)
                End If
            End If
        End If
    Next i
Next r

 

[My Aswer Is This]

In your original post you said Quote:
"so it takes a pretty minute"

What exactly does that mean?
Do you mean 1 minute or 20 minutes?
I do see some users on this forum complain when a script takes 9 seconds to run.

 

[ouvay]

In your original post you said Quote:
"so it takes a pretty minute"

What exactly does that mean?
Do you mean 1 minute or 20 minutes?
I do see some users on this forum complain when a script takes 9 seconds to run.

I need to run this code on 2 sheets.. one sheet with 300k rows, takes 4 hours at the very least ( I stopped monitoring after that, since it was time to go home lol) and the other sheet with 600k rows( I have not attempted this yet)

That being said, I have yet to implement RobP's suggestions and break down the If statement into multiple ifs

 

[Alex Blakenburg]

Typically when you use SumIfs you have a small number of rows looking up (& summarising) a large number of rows. What are you trying to do ?
Does it have to be VBA ? Have you considered Power Query ?

If you want to post an XL2BB with some sample data of both sheets, I can see if using a dictionary helps speed it up.

 

[ouvay]

Typically when you use SumIfs you have a small number of rows looking up (& summarising) a large number of rows. What are you trying to do ?
Does it have to be VBA ? Have you considered Power Query ?

If you want to post an XL2BB with some sample data of both sheets, I can see if using a dictionary helps speed it up.

Hi

I'm having a bit of trouble with uploading the data via XL2BB since I'm logged in via a remote PC, so instead I have prepared some sample files to share which are cleaned up with only the relevant data .. I've also made some remarks to help explain what I'm trying to do

Easyupload.io - Upload Files and Share Them Easily

Uplaod files easily and share them with your friends and colleagues. File upload solution for everyone. Upload big files and get a link to share effortlesly.

easyupload.io

a quick run through of the data ...
we have 2 ID numbers ( a buyer ID and a sponsor ID)
-- these are the 1st and 2nd criteria

next we have a 3rd criteria (1st pay/payoff)

we are checking how much each buyer and sponsor were paid in the bank statements, taking into consideration the 3rd criteria ..

unfortunately, none of these data fields are small by any means...
the buyer/sponsor list alone is well over 1 million rows and the bank statement is ever growing too( currently 600k+ rows)

which is why i need an efficient way to calculate this, every day at the very least

any help would be appreciated !

thanks in advance

 

[ouvay]

Hi,

another idea I had was to re-write your loop, to just use IF's rather than multiple AND every time. I figure if the first one is not a match, it will skip the loop without having to perform a check on the second or third. It might help save a little time ?

Rob

For r = 2 To UBound(Data2)
    'Application.StatusBar = "Calculating " & s2 & " row " & r & " of " & UBound(Data2) & "... " & Format(r / UBound(Data2), "PERCENT") & " Completed"  ' just some status bar ux
    For i = LBound(a) To UBound(a)
        If Data2(r, 1) = a(i, 1) Then
            If Data2(r, 2) = a(i, 2) Then
                If Data2(r, 3) = a(i, 3) Then
                    sum2(r, 1) = sum2(r, 1) + a(i, 4)
                    sum2(r, 2) = sum2(r, 2) + a(i, 5)
                End If
            End If
        End If
    Next i
Next r

I tried this! It did make it considerably faster

Thank you

 

[RobP]

Interested to know by how much ?

Just to know…

Thanks a lot for feedback, glad it helped