Sumifs Alternative in VBA dictionary

[reggieneo]

Hi All,
I have a 2250 cells with sumifs formula. Can somebody please help me to create a sumifs vba alterative that I have built for 2250 Cells.

Need to get the sum of work hours for each work ticket for each Employee.

my data is about 20 thousand rows and just 3 columns.. DATA is growing.

I want to place the answer to worksheet 2 from N to AB Column.

worksheet 2 Row N3 to AB3 is the Criteria for Employee
worksheet 2 Row B4 to B150 is the Criteria for work ticket


Worksheet 1 A3:D20000 is the source data

Worksheet 1 A3:A20000 is the Employee Rows
Worksheet 1 B3:B20000 is the work ticket Rows
Worksheet 1 C3:C20000 is the work hours I need to sum

please help.

 

[Akuini]

Try this:

Sub reggieneo_1()
Dim i As Long, j As Long
Dim c As Range
Dim tx As String
Dim va, vb, t
Dim e As Object, d As Object

t = Timer
Application.ScreenUpdating = False
Sheets("Sheet1").Activate
va = Range("A2:C" & Cells(Rows.Count, "A").End(xlUp).Row)

Set d = CreateObject("scripting.dictionary"): d.CompareMode = vbTextCompare
Set e = CreateObject("scripting.dictionary"): d.CompareMode = vbTextCompare

    For i = 1 To UBound(va, 1)
        d(va(i, 1)) = Empty
    Next
    For i = 1 To UBound(va, 1)
        e(va(i, 2)) = Empty
    Next

If d.Exists("") Then d.Remove ""
If e.Exists("") Then e.Remove ""

Sheets("Sheet2").Activate
Set c = Range("A1").Resize(e.Count + 1, d.Count + 1)
    c.Resize(c.Rows.Count + 1, c.Columns.Count + 1).Clear

Range("B1").Resize(1, d.Count) = d.keys
Range("A2").Resize(e.Count, 1) = Application.Transpose(e.keys)
vb = c.Cells
d.RemoveAll: e.RemoveAll

    For i = 1 To UBound(va, 1)
        d(va(i, 1) & "|" & va(i, 2)) = d(va(i, 1) & "|" & va(i, 2)) + va(i, 3)
    Next

    For i = 2 To UBound(vb, 1)
        tx = vb(i, 1)
        For j = 2 To UBound(vb, 2)
              vb(i, j) = d(vb(1, j) & "|" & tx)
        Next
    Next

c = vb
Application.ScreenUpdating = True
Debug.Print "It's done in:  " & Timer - t & " seconds"
End Sub

I tested the code using this example:

reggieneo - Sumifs Alternative in VBA dictionary 1.xlsm
	A	B	C
1	Name	Ticket	Time
2	Lochlan	H	1
3	Cory	T	2
4	Isaac	N	3
5	Erick	H	3.5
6	Patrick	H	7.5
7	Lochlan	H	6
8	Cory	H	1
9	Isaac	T	2
10	Erick	H	3
11	Isaac	H	1
12	Erick	T	2
13	Patrick	N	3
14	Lochlan	H	4
15	Cory	T	5
Sheet1

Result:

reggieneo - Sumifs Alternative in VBA dictionary 1.xlsm
	A	B	C	D	E	F
1		Lochlan	Cory	Isaac	Erick	Patrick
2	H	11	1	1	6.5	7.5
3	T		7	2	2
4	N			3		3
Sheet2

 

[reggieneo]

it doesn't have sum values in sheets 2, maybe because my original Names (Sheet 1 Column A) are numbers formatted as text, then when the results was written in the sheet 2, it became numbers. its like 005420 from source sheet1 but returns 5420.

other issue is I only need to calculate what were the Tickets have in (Sheet 1 Column E). the code gives me the entire ticket list.
meaning if I have 1000 Tickets in my criteria, and I have 100k unique tickets in the source, the vba code gives me the whole 100K unique tickets.
hope I don't inconvenience you too much.

thanks

 

[offthelip]

Why do you want a VBA alternative?
Any function you create to do it in VBA will most likely be less efficient and more problematic that Excel's built-in functions.

Note that you CAN use most Excel functions in VBA using Application.WorksheetFunction, i.e. Application.WorksheetFunction.SumIfs(...)

Also note that this type of task may be more down more efficiently with Pivot Tables, Power Pivot, or a relational database program like Microsoft Access.

One of the very good reasons why it can be advantageous to replicate excel built in functions is it can make the workbook more usable because it recalculates much faster. If as is the case with the OP you have 2250 sumifs formula every time you update a row all the sumifs recalculate which makes the workbook very slow to update. If the sumifs are done in VBA then then this can be triggered on demand i.e when all the updates are done. Obviously this might be done by turning calculation on or off, but sometimes you need calculation to be on all the time because each row needs to be calculated.
I have been in exactly this situation where the solution was to trigger the sumifs with a button. The result was a worksheet that was usable.

 

[Akuini]

other issue is I only need to calculate what were the Tickets have in (Sheet 1 Column E). the code gives me the entire ticket list.
meaning if I have 1000 Tickets in my criteria, and I have 100k unique tickets in the source, the vba code gives me the whole 100K unique tickets.

Before you run the code, what data already exist in sheet2 besides the tickets? what about the Employee?
And where are their location (row and column)?

 

[reggieneo]

Before you run the code, what data already exist in sheet2 besides the tickets? what about the Employee?
And where are their location (row and column)?

the employee is in Sheet 2, from G3:U3 (could grow), this is the criteria for employee
The ticket is also in Sheet 2 from B4:B150 (Could Grow) this is the criteria for ticket
the source is in Sheet 1 A2: C

objective : look for match of employee and ticket from Sheet 2 (criteria) to Sheet 1 ( the source) then once match is found, calculate the sum for each Employee on matching ticket.

below is a sample sumifs formula for Sheet 2 G4 ( to get the value form sheet 1)

sumifs('sheet1'!C:C,'sheet1'!A:A,''sheet2'!G3,sheet1'!B:B,'sheet1'!B4)

 

[Akuini]

Try this one:

Sub reggieneo_2()
Dim i As Long, j As Long
Dim tx As String
Dim va, vb, vc, t
Dim d As Object

t = Timer

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

Sheets("Sheet1").Activate
va = Range("A2:C" & Cells(Rows.Count, "A").End(xlUp).Row)

Set d = CreateObject("scripting.dictionary"): d.CompareMode = vbTextCompare
    For i = 1 To UBound(va, 1) 'employee + ticket
        d(va(i, 1) & "|" & va(i, 2)) = d(va(i, 1) & "|" & va(i, 2)) + va(i, 3)
    Next

Sheets("Sheet2").Activate

    va = Range("B4", Cells(Rows.Count, "B").End(xlUp)) 'ticket : vertical
    vb = Range("G3", Cells(3, Columns.Count).End(xlToLeft)) 'employee : horisontal
    ReDim vc(1 To UBound(va, 1), 1 To UBound(vb, 2))

    For j = 1 To UBound(vb, 2)
        tx = vb(1, j)
        For i = 1 To UBound(va, 1)
              vc(i, j) = d(tx & "|" & va(i, 1))
        Next
    Next

Range("G4").Resize(UBound(vc, 1), UBound(vc, 2)) = vc
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

Debug.Print "It's done in:  " & Timer - t & " seconds"

End Sub

Example:

reggieneo - Sumifs Alternative in VBA dictionary 1.xlsm
	A	B	C
1	Employee	Ticket	Time
2	0123	H	1
3	0124	T	2
4	0125	N	3
5	0333	H	3.5
6	0123	H	7.5
7	0124	K	6
8	0125	K	1
9	0333	T	2
10	0123	H	3
11	0124	H	1
12	0125	T	2
13	0333	N	3
14	0123	H	4
15	0124	T	5
Sheet1

Result:

reggieneo - Sumifs Alternative in VBA dictionary 1.xlsm
	A	B	C	D	E	F	G	H	I	J
2
3							0123	0124	0333
4		H					15.5	1	3.5
5		N							3
6		T						7	2
7
Sheet2

 

[reggieneo]

Try this one:

Sub reggieneo_2()
Dim i As Long, j As Long
Dim tx As String
Dim va, vb, vc, t
Dim d As Object

t = Timer

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

Sheets("Sheet1").Activate
va = Range("A2:C" & Cells(Rows.Count, "A").End(xlUp).Row)

Set d = CreateObject("scripting.dictionary"): d.CompareMode = vbTextCompare
    For i = 1 To UBound(va, 1) 'employee + ticket
        d(va(i, 1) & "|" & va(i, 2)) = d(va(i, 1) & "|" & va(i, 2)) + va(i, 3)
    Next

Sheets("Sheet2").Activate

    va = Range("B4", Cells(Rows.Count, "B").End(xlUp)) 'ticket : vertical
    vb = Range("G3", Cells(3, Columns.Count).End(xlToLeft)) 'employee : horisontal
    ReDim vc(1 To UBound(va, 1), 1 To UBound(vb, 2))

    For j = 1 To UBound(vb, 2)
        tx = vb(1, j)
        For i = 1 To UBound(va, 1)
              vc(i, j) = d(tx & "|" & va(i, 1))
        Next
    Next

Range("G4").Resize(UBound(vc, 1), UBound(vc, 2)) = vc
   
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

Debug.Print "It's done in:  " & Timer - t & " seconds"

End Sub

Example:

reggieneo - Sumifs Alternative in VBA dictionary 1.xlsm
	A	B	C
1	Employee	Ticket	Time
2	0123	H	1
3	0124	T	2
4	0125	N	3
5	0333	H	3.5
6	0123	H	7.5
7	0124	K	6
8	0125	K	1
9	0333	T	2
10	0123	H	3
11	0124	H	1
12	0125	T	2
13	0333	N	3
14	0123	H	4
15	0124	T	5
Sheet1

Result:

reggieneo - Sumifs Alternative in VBA dictionary 1.xlsm
	A	B	C	D	E	F	G	H	I	J
2
3							0123	0124	0333
4		H					15.5	1	3.5
5		N							3
6		T						7	2
7
Sheet2

this is just GREAT! Thanks so much! It is way faster and a very elegant solution. I will look into it in detail and learn the structures. thanks again.

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.