Sub-totaling and Totaling question

[nikneven]

This is part of a sheet that i have that totals employee hours and pay per week. It looks like this....

Analyzed Site Payroll 9-13.xls
	Y	Z	AA	AB	AC	AD	AE
1	Day Rate	OT Rate		Straight Pay	OT Pay	Total
2	$ 200.00	$ 300.00		$ 200.00	$ 30.00
3	$ 200.00	$ 300.00		$ 100.00	$ 150.00
4	$ 200.00	$ 300.00		$ 60.00	$ 60.00
5
6
7	$ 220.00	$ 330.00		$ 220.00	$ -
8	$ 220.00	$ 330.00		$ -	$ -
9	$ 220.00	$ 330.00		$ 330.00	$ -
10	$ 220.00	$ 330.00		$ 110.00	$ -
11	$ 220.00	$ 330.00		$ 176.00	$ -
12	$ 220.00	$ 330.00		$ 110.00	$ -
13	$ 220.00	$ 330.00		$ 110.00	$ -
14	$ 220.00	$ 330.00		$ 110.00	$ -
15	$ 220.00	$ 330.00		$ 220.00	$ -
16
17
18	$ 500.00	$ 750.00		$ -	$ -
19	$ 500.00	$ 750.00		$ 850.00	$ 225.00
20	$ 500.00	$ 750.00		$ 900.00	$ -
21	$ 500.00	$ 750.00		$1,000.00	$ -
22	$ 500.00	$ 750.00		$ 450.00	$ 75.00
23	$ 500.00	$ 750.00		$ 250.00	$ 375.00
24
WEEK OF 9-13

I'd like it to look like this without having to subtotal and total every one by hand....

 

[nikneven]

Analyzed Site Payroll 9-13.xls
	Y	Z	AA	AB	AC	AD
1	DayRate	OTRate		StraightPay	OTPay	Total
2	$200.00	$300.00		$200.00	$30.00
3	$200.00	$300.00		$100.00	$150.00
4	$200.00	$300.00		$60.00	$60.00
5				$360.00	$240.00	$600.00
6
7	$220.00	$330.00		$220.00	$-
8	$220.00	$330.00		$-	$-
9	$220.00	$330.00		$330.00	$-
10	$220.00	$330.00		$110.00	$-
11	$220.00	$330.00		$176.00	$-
12	$220.00	$330.00		$110.00	$-
13	$220.00	$330.00		$110.00	$-
14	$220.00	$330.00		$110.00	$-
15	$220.00	$330.00		$220.00	$-
16				$1,386.00	$-	$1,386.00
17
18	$500.00	$750.00		$-	$-
19	$500.00	$750.00		$850.00	$225.00
20	$500.00	$750.00		$900.00	$-
21	$500.00	$750.00		$1,000.00	$-
22	$500.00	$750.00		$450.00	$75.00
23	$500.00	$750.00		$250.00	$375.00
24				$3,450.00	$675.00	$4,125.00
25
WEEK OF 9-13

any ideas? totaling all 300 employees is a real pain...

thanks
nik

 

[Andrew Poulsom]

Select your data and choose Data|Subtotals from the menu. At each change in Day Rate, Use function Sum, Add subtotal to Straight pay and OT pay and click OK.

You may want to remove the blank rows first, fill in the Total using SUM and include that column in Add subtotal to.

 

[nikneven]

I've tried that, unfortunatly there are people next to each other that have the same rates, so it doesn't properly subtotal them. I've tried to do it by name, but i cant seem to make it total the correct rows.

Nik

 

[Andrew Poulsom]

I don't see any people in what you posted. You seem to be just subtotalling when the Day rate changes.

For more help you will need to post more representative data.

 

[nikneven]

I have about 300 sets of these...

Analyzed Site Payroll 9-13.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Date	CallType	CallTime		Name			BookedStraight	BookedOT	WorkedStraight	WorkedOT	Notes	PM	Event		DayRate	OTRate		StraightPay	OTPay	Total
2	09/10/06	LoadIn	2:00PM	2	First	Name	{Confirmed}	10	0	10	1		PM:(XXX)XXX-XXXX	ShowName		$200.00	$300.00		$200.00	$30.00
3	09/12/06	Strike	1:00AM	3	First	Name	{Confirmed}	5	5	5	5		PM:(XXX)XXX-XXXX	ShowName		$200.00	$300.00		$100.00	$150.00
4	09/12/06	Strike	9:00PM	6	First	Name	{Confirmed}	3	2	3	2		PM:(XXX)XXX-XXXX	ShowName		$200.00	$300.00		$60.00	$60.00
5
WEEK OF 9-13

 

[Andrew Poulsom]

What determines where one group starts and the next group ends? If it's just 2 blank rows, how did they get there?

 

[nikneven]

Well, obviously the names differ, though we have people with the same first names, and some with the same last names. the two blank rows are there to create room for me to total each person and for our comptroller to easily read the sheet for entering it into payroll. the rows get there with this macro.

Sub DTS9()
Dim i As Long
For i = Range("a" & Rows.Count).End(xlUp).Row To 2 Step -1
If Cells(i, 6) <> "" And Cells(i, 6) <> Cells(i + 1, 6) Or Cells(i, 5) <> Cells(i + 1, 5) Then
Cells(i + 1, 6).EntireRow.Insert Shift:=xlDown
Cells(i + 1, 6).EntireRow.Insert Shift:=xlDown
End If
Next
End Sub

 

[Andrew Poulsom]

In a spare column concatenate the first and last name, eg:

=E2&F2

Then subtotal at each change in that column.

 

[nikneven]

didn't work. i still couldn't get that to work. its not totaling them. ****. by hand it is.... the grand total thing is messing everything up. and i cant get them to add across into the total column