Macro or more efficient ways to reduce processing time (SUMIFS)

[Bitturkl]

I am trying to do sumifs on below data as an example (Actual file has ~700K rows) and it took more than a couple of hours to compute. The 1st column has unique code of shop, the 2nd column has Part ID sold and 3rd column has the quantity sold. My aim is to create a 2nd table below which has only unique part ID (~60K) and sales amount from each shop. The file has 700K lines representing each and every sale transaction throughout a month.

I am using the below sumifs
=SUMIFS($C$2:$C$9,$A$2:$A$9,F$1,$B$2:$B$9,$E2)

How can I reduce the processing time? Is there a macro that can be used which is more efficient in processing time? Appreciate if you can give the full VBA code for this example - I have not used VBA for 10years now and had used very simple VBA only earlier

Code Part Quantity
D01 P001 10
D01 P002 5
D02 P001 4
D01 P001 10
D02 P004 1
D03 P002 3
D02 P003 2
D02 P004 7

Part D01 D02 D03
P001 20 4 -
P002 5 - 3
P003 - 2 -
P004 - 8 -

 

[liveinhope]

Have you tired a pivot table ?
My sample data has the same columns as yours and 510k rows - refreshes in seconds


column field = store
row field = part
value field = sum of qty

 

[kalak]

You ask for a macro.
Here's one you can try on your sample data, assumed to start from cell A1 in 3 columns
I don't know how it would go for 700krows.

Option ExplicitSub try_this()

Dim d1 As Object, d2 As Object
Dim a, c(), r&, i&, e, f

Set d1 = CreateObject("scripting.dictionary")
Set d2 = CreateObject("scripting.dictionary")
d1.CompareMode = 1: d2.CompareMode = 1
r = Range("A:B").Find("*", , , , xlByRows, xlPrevious).Row
a = Cells(1).Resize(r, 3)

ReDim c(1 To r, 1 To r)

For i = 2 To r
    e = a(i, 1): f = a(i, 2)
    If d1(e) = "" Then d1(e) = d1.Count + 1
    If d2(f) = "" Then d2(f) = d2.Count + 1
    c(d2(f), 1) = f: c(1, d1(e)) = e
    c(d2(f), d1(e)) = c(d2(f), d1(e)) + a(i, 3)
Next i

Range("E1").Resize(d2.Count + 1, d1.Count + 1) = c
Range("E1") = "Variable Name"
Columns("E").Resize(, d1.Count + 1).AutoFit

End Sub

 

[Bitturkl]

Pivot table would be faster but won't help me much --- this is one month data I have similar 12 months files and in each month there would be some unique parts sold so when I have to sum all then I would need to do a sumif of all pivot tables based on the master part list for the full year.

Have you tired a pivot table ?
My sample data has the same columns as yours and 510k rows - refreshes in seconds


column field = store
row field = part
value field = sum of qty

 

[Bitturkl]

Thanks a ton! Let me try this and will let you know how it goes.

You ask for a macro.
Here's one you can try on your sample data, assumed to start from cell A1 in 3 columns
I don't know how it would go for 700krows.

Option ExplicitSub try_this()

Dim d1 As Object, d2 As Object
Dim a, c(), r&, i&, e, f

Set d1 = CreateObject("scripting.dictionary")
Set d2 = CreateObject("scripting.dictionary")
d1.CompareMode = 1: d2.CompareMode = 1
r = Range("A:B").Find("*", , , , xlByRows, xlPrevious).Row
a = Cells(1).Resize(r, 3)

ReDim c(1 To r, 1 To r)

For i = 2 To r
    e = a(i, 1): f = a(i, 2)
    If d1(e) = "" Then d1(e) = d1.Count + 1
    If d2(f) = "" Then d2(f) = d2.Count + 1
    c(d2(f), 1) = f: c(1, d1(e)) = e
    c(d2(f), d1(e)) = c(d2(f), d1(e)) + a(i, 3)
Next i

Range("E1").Resize(d2.Count + 1, d1.Count + 1) = c
Range("E1") = "Variable Name"
Columns("E").Resize(, d1.Count + 1).AutoFit

End Sub

 

[kalak]

Thanks a ton! Let me try this and will let you know how it goes.

That code worked for me on your posted sample, but as it stands won't work on 700k rows.
The modified version below should be OK though. It took me about 8 seconds to do your sample data filled down 700k rows.

Sub try_this()

Dim d1 As Object, d2 As Object
Dim a, c(), r&, i&, e, f


Set d1 = CreateObject("scripting.dictionary")
Set d2 = CreateObject("scripting.dictionary")
d1.CompareMode = 1: d2.CompareMode = 1
r = Range("A:B").Find("*", , , , xlByRows, xlPrevious).Row
a = Cells(1).Resize(r, 3)


For i = 2 To r
    d1(a(i, 1)) = 1
    d2(a(i, 2)) = 1
Next i
ReDim c(1 To d2.Count + 1, 1 To d1.Count + 1)
d1.RemoveAll: d2.RemoveAll


For i = 2 To r
    e = a(i, 1): f = a(i, 2)
    If d1(e) = "" Then d1(e) = d1.Count + 1
    If d2(f) = "" Then d2(f) = d2.Count + 1
    c(d2(f), 1) = f: c(1, d1(e)) = e
    c(d2(f), d1(e)) = c(d2(f), d1(e)) + a(i, 3)
Next i


Range("E1").Resize(d2.Count + 1, d1.Count + 1) = c
Range("E1") = "Variable Name"
Columns("E").Resize(, d1.Count + 1).AutoFit


End Sub

 

[Bitturkl]

Thanks! This works perfectly. I am now trying to understand the logic behind this and get familiar with the code (trying to understand what removeall does etc.). Can you help me understand the fundamental logic that you are using behind these in simple terms.

That code worked for me on your posted sample, but as it stands won't work on 700k rows.
The modified version below should be OK though. It took me about 8 seconds to do your sample data filled down 700k rows.

Sub try_this()

Dim d1 As Object, d2 As Object
Dim a, c(), r&, i&, e, f


Set d1 = CreateObject("scripting.dictionary")
Set d2 = CreateObject("scripting.dictionary")
d1.CompareMode = 1: d2.CompareMode = 1
r = Range("A:B").Find("*", , , , xlByRows, xlPrevious).Row
a = Cells(1).Resize(r, 3)


For i = 2 To r
    d1(a(i, 1)) = 1
    d2(a(i, 2)) = 1
Next i
ReDim c(1 To d2.Count + 1, 1 To d1.Count + 1)
d1.RemoveAll: d2.RemoveAll


For i = 2 To r
    e = a(i, 1): f = a(i, 2)
    If d1(e) = "" Then d1(e) = d1.Count + 1
    If d2(f) = "" Then d2(f) = d2.Count + 1
    c(d2(f), 1) = f: c(1, d1(e)) = e
    c(d2(f), d1(e)) = c(d2(f), d1(e)) + a(i, 3)
Next i


Range("E1").Resize(d2.Count + 1, d1.Count + 1) = c
Range("E1") = "Variable Name"
Columns("E").Resize(, d1.Count + 1).AutoFit


End Sub

 

[kalak]

Thanks! This works perfectly. I am now trying to understand the logic behind this and get familiar with the code (trying to understand what removeall does etc.). Can you help me understand the fundamental logic that you are using behind these in simple terms.

Depending on how much you already know about this sort of stuff ...
The removeall just removes all information that has been put into the two scripting dictionaries.
This enabled them to be used anew within the same code, say as an alternative to defining new ones, or using some other approach.
I used them twice (for different purposes) to avoid the error which my first code would have given for a large sample.
Basically, the dictionaries obtained unique values (called dictionary "keys") for the entries in your "code" and "part" columns, and positioned these as row headers and column headers in the array c(). Then put the sums of your "quantity" values (called dictionary "items") in the appropriate part of the body of the c() array.
Then listed the c() array on the worksheet.
Most of this stuff is done in the computer's memory rather than juggling stuff around on worksheets, so the result can often turn out to be acceptably fast.
I hope this is helpful rather than confusing.

 

[Bitturkl]

Thanks ... Can't say I understood fully but got some idea.

Btw I realized that this is replicating what a Pivot table does - what if I have the intended output table with the fields (Part nos. in rows and the unique shops listed in column) and need to just populate the total against each part no. and shop ... the part nos. in this (column E) has some additional part nos. which might not be present in column A to C for this particular month ... would it be possible to do that? I.e. basically replicate the sumifs formula I had in all these cells..

Depending on how much you already know about this sort of stuff ...
The removeall just removes all information that has been put into the two scripting dictionaries.
This enabled them to be used anew within the same code, say as an alternative to defining new ones, or using some other approach.
I used them twice (for different purposes) to avoid the error which my first code would have given for a large sample.
Basically, the dictionaries obtained unique values (called dictionary "keys") for the entries in your "code" and "part" columns, and positioned these as row headers and column headers in the array c(). Then put the sums of your "quantity" values (called dictionary "items") in the appropriate part of the body of the c() array.
Then listed the c() array on the worksheet.
Most of this stuff is done in the computer's memory rather than juggling stuff around on worksheets, so the result can often turn out to be acceptably fast.
I hope this is helpful rather than confusing.