Find alternative to using nested loops for subtotalling data

[totalchaos]

I currently have data (below) that I sort by date, then job, then trade and then shift. For each date, job, trade and shift, I must provide subtotals and output the results to a separate sheet.

The code I currently use has nested do..loops which navigates the data, fills an array and writes to a separate sheet. The code (below) is the gist of what I have. The actual code is so complex to look at it gives me a headache and is hard to manage.

I'm looking for an alternative to the approach I've taken. I researched using autofilter. Unfortunately, from I can tell, I cannot get the list of unique values inside each filter which I would use to store in an array and then loop through and autofilter the specific values in the 4 fields I listed in the first paragraph. I'd then use the worksheetfunction.subtotal to get my subtotals. If anybody knows how to do this, it would be greatly helpful to me to simplify my code and possibly provide some performance efficiency. If it's not possible to use autofilter, any other suggestions would be greatly appreciated to simplify my code and improve the efficiency since my actual list is much larger. I'm using excel 2007 and 2010 on different computers.

Here is the data:

Excel 2007
	A	B	C	D	E	F	G	H	I
1	Line#	Employee	Job	Trade	Code	Extra #	Shift	Work Date	Hours
2	1	1	1201	Carpenter	A	0	1	7/19/2012	4
3	2	1	1201	Carpenter	B	0	1	7/19/2012	4
4	3	2	1201	Plumber	A	1	2	7/18/2012	4
5	4	2	1201	Plumber	A	2	1	7/18/2012	3
6	5	2	1201	Plumber	A	0	1	7/18/2012	1
7	6	3	1201	Electrician	B	1	2	7/19/2012	3
8	7	3	1202	Electrician	B	0	2	7/19/2012	5
9	8	4	1201	Electrician	B	0	1	7/19/2012	8
10	9	4	1201	Electrician	A	0	1	7/18/2012	8
11	10	5	1202	Carpenter	A	0	1	7/19/2012	8
12	11	6	1201	Electrician	A	1	1	7/19/2012	2
13	12	6	1201	Electrician	A	0	1	7/19/2012	3
14	13	6	1201	Electrician	B	0	1	7/19/2012	2
15	14	6	1201	Electrician	B	1	1	7/19/2012	1
16	15	7	1202	Plumber	A	1	2	7/19/2012	3
17	16	7	1202	Plumber	A	0	2	7/19/2012	2
18	17	7	1202	Plumber	A	2	2	7/19/2012	4
19	18	8	1202	Carpenter	A	1	2	7/18/2012	2
20	19	8	1201	Carpenter	B	1	2	7/18/2012	6
21	20	9	1202	Plumber	A	1	1	7/18/2012	4
22	21	9	1202	Plumber	A	0	1	7/18/2012	4
23	22	10	1202	Carpenter	A	1	1	7/18/2012	2
24	23	10	1202	Carpenter	B	2	1	7/18/2012	1
25	24	10	1202	Carpenter	B	0	1	7/18/2012	3
26	25	10	1201	Carpenter	B	0	1	7/18/2012	2
Sheet1

<colgroup><col style="width: 48pt;" span="10" width="64"> <tbody>
</tbody>

Here is the code I currently use in a standard module:

Option Explicit
Dim wsSrce As Worksheet, rng As Range, sngHours As Single, strWrkDate As String, _
strJobNum As String, strTrade As String, strShift As String, Cntr As Long, _
arr() As Variant, i As Integer

Private Sub WorkReport()
Set wsSrce = ActiveWorkbook.Worksheets("Sheet1")
Set rng = wsSrce.Range("A1:I26")
'Sort by workdate, job, trade, shift
With wsSrce.Sort
.SortFields.Clear
.SortFields.Add Key:=Range("H2:H26"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range("C2:C26"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range("D2:D26"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range("G2:G26"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange rng
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'sum hours for each unique workdate, job, trade, shift combination
With wsSrce
Cntr = 1
Do Until IsEmpty(.Cells(Cntr, 1)) '1
Cntr = Cntr + 1
strWrkDate = .Cells(Cntr, 8)
Do While .Cells(Cntr, 8) = strWrkDate '2
strJobNum = .Cells(Cntr, 3)
Do While .Cells(Cntr, 8) = strWrkDate And .Cells(Cntr, 3) = strJobNum '3
strTrade = .Cells(Cntr, 4)
Do While .Cells(Cntr, 8) = strWrkDate And .Cells(Cntr, 3) = strJobNum And .Cells(Cntr, 4) = strTrade '4
strShift = .Cells(Cntr, 7)
Do While .Cells(Cntr, 8) = strWrkDate And .Cells(Cntr, 3) = strJobNum And .Cells(Cntr, 4) = strTrade And .Cells(Cntr, 7) = strShift '5
sngHours = sngHours + .Cells(Cntr, 9)
Cntr = Cntr + 1
Loop
FillArray
sngHours = 0
Loop
Loop
Loop
Loop
End With
WriteArray
'sort back to original order
With wsSrce.Sort
.SortFields.Clear
.SortFields.Add Key:=Range("A2:A26"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange rng
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Private Sub FillArray()
i = i + 1
ReDim Preserve arr(1 To 5, 1 To i)
arr(1, i) = strWrkDate
arr(2, i) = strJobNum
arr(3, i) = strTrade
arr(4, i) = strShift
arr(5, i) = sngHours
End Sub
Private Sub WriteArray()
Dim a As Integer, wsTrgt As Worksheet, lngLR As Long
Set wsTrgt = ActiveWorkbook.Worksheets("Sheet2")
For a = 1 To UBound(arr, 2)
With wsTrgt
lngLR = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
.Cells(lngLR, 1) = arr(1, a)
.Cells(lngLR, 2) = arr(2, a)
.Cells(lngLR, 3) = arr(3, a)
.Cells(lngLR, 4) = arr(4, a)
.Cells(lngLR, 5) = arr(5, a)
End With
Next a
Erase arr
End Sub

Any help is greatly appreciated!

 

[memar_one]

Can you give me an example of what you want the output to be? It's hard keeping all the tables/arrays + code in my head at once.

Edit: Nevermind, I ran your code and I can see.

 

[memar_one]

Have you ever tried using a pivot table?


If you haven't, you should select the data, go to Insert -> Pivot Table (Located on the left side if you use my version) and then press OK.

On this new sheet with the pivot table, you will have options called Pivot Field List (on my version it's on the right side) and also other options at the top for the table.

Look for "Tabular Layout" in PivotTable Tools -> Design -> Layout Options and select it.

Finally, in the pivot table field list, check tickmarks in this order: WorkDate, Job (Make sure this is in the "Row Labels" area instead of "Sum"), Trade, Shift (Again, put this as a "Row Label"), and Hours (Leave this as a sum).


This might be an easy solution to what you're looking for, and it doesn't even require a macro. Editing a pivot tables with macros can be hard, though (at least, I haven't found an easy way yet), and they're generally just for information purposes. You can also keep ALL your data there, and can hide/show categories as you would like to see them. Is this kind-of what you were looking for?

 

[gs1.049]

As above, depends on the output you want... have you considered using SUMIFS or AVERAGEIFS formulas etc...

You could sum the number of carpentry hours on job 1201 on 19 July by using...

=SUMIFS($I:$I, $D:$D, "Carpenter",$C:$C, 1201, $H:$H, 41109)

Of course you can refer to other cells in your formulas, instead of typing the criteria in manually.

Pivot tables work good too.

 

[totalchaos]

I initially thought the sumifs could work too by using them in vba with worksheetfunction. Unfortunately I won't know the criteria to be used in the formula until the timesheets are summarized into the first table I posted above. Then I must determine unique values of each of the 4 columns I need and filter the various combinations so that I get totals for each shift within each trade within each job within each date. I'm beginning to think that the easiest way to do this is with the nested loops I'm using now.

 

[memar_one]

Why do you need the data in this specific format? If you can tell us what you're using the data for, then perhaps there's an easier way to accomplish it.

 

[totalchaos]

The data must be in the format as shown below. I'm not worried about the employee count part right now. Ultimately, this data will be written to a customer's report form in this format. Hopefully, this will help you understand what I'm trying to accomplish. Thanks.

Excel 2007
	A	B	C	D	E	F	G
1				Employee Count		Hours
2	Work Date	Job	Trade	Shift 1	Shift 2	Shift 1	Shift 2
3	7/18/2012	1201	Carpenter	1	1	2	6
4	7/18/2012	1201	Electrician	1	0	8	0
5	7/18/2012	1201	Plumber	1	1	4	4
6	7/18/2012	1202	Carpenter	1	1	6	2
7	7/18/2012	1202	Plumber	1	0	8	0
8	7/19/2012	1201	Carpenter	1	0	8	0
9	7/19/2012	1201	Electrician	2	1	16	3
10	7/19/2012	1202	Electrician	0	1	0	5
11	7/19/2012	1202	Plumber	0	1	0	9
12	7/19/2012	1202	Carpenter	1	0	8	0
Sheet4

<colgroup><col style="width: 48pt;" span="8" width="64"> <tbody>
</tbody>

 

[memar_one]

=SUMPRODUCT(--(Sheet1!$H$2:$H$1000=$A3), --(Sheet1!$B$2:$B$1000=$B3), --(Sheet1!$D$2:$D$1000=C3), --(Sheet1!$G$2:$G$1000=D$2), Sheet1!$I$2:$I$1000)

If you put that in D3 and copied that cell+pasted it to D3:G12, will it show the correct data?
Note: goes up to row 1000 on the source ws; you can make it depend on the length of your source ws if you use code to generate it.

If you can populate your resulting list with that (that way you won't have to do so many loops to add up hours), then perhaps it will make your code simpler. The next step is to replace your loops with ways to populate the result list with all possible combinations of date/job number/trades, then paste in the formula at D3, then fill right and down for all combinations.

Does this help? I have other ideas, too.

 

[totalchaos]

I can't get the formula to work, but I am seriously leaning toward a pivot table, then navigating that with vba to get the data I need and finally writing that to our customer's report form. At this point, I don't know how to navigate pivot tables. There is a pivot items property which I will check out. One problem I can see with the pivot table is counting the number of unique employees. It counts an employee that has two different codes in one day twice. Any ideas?

 

[memar_one]

Specify both Code and Trade as identifiers; If you didn't, the person looking at the data wouldn't be able to tell the difference either.