Macro to subtotal the hours associated with a name, then perform a calcunation on the subtotal

[Tanner_2004]

I have a table that includes several columns of data. There is a column with employee names and another column with employee hours for each week the employee worked. Each record represents a week of work and therefore includes the emplyee's name and hours for that week. I want to subtotal the hoursfor each employee, store the subtotaled hours and the perform a calculation on the subtotaled hours. In this case, if the subtotaled hours for an employee is great than a constant, the subtotal hours exceeding the constant would appear in another cell, next the employees name. I woudl like to use this macro regardless of the number of rows in the original table. Any ideas would be greatly appreciated.

Thank you!

 

[hiker95]

Tanner_2004,

What version of Excel are you using?

We can not tell what cells, rows, columns your raw data is in.

Can you post the raw data worksheet, and, post the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.


If you are not able to give us screenshots:
You can upload your workbook to Box Net,

sensitive data scrubbed/removed/changed

mark the workbook for sharing
and provide us with a link to your workbook.

 

[Tanner_2004]

[TABLE="width: 308"]
<COLGROUP><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1507" width=42><COL style="WIDTH: 129pt; mso-width-source: userset; mso-width-alt: 6115" width=172><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3185" width=90><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1536" width=43><COL style="WIDTH: 48pt" width=64><TBODY>[TR]
[TD="class: xl64, width: 42, bgcolor: yellow"]Name[/TD]
[TD="width: 172, bgcolor: transparent"]Position[/TD]
[TD="width: 90, bgcolor: transparent"]Vendor[/TD]
[TD="class: xl64, width: 43, bgcolor: yellow"]Hours[/TD]
[TD="width: 64, bgcolor: transparent"]Total Hours[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow"]ABC[/TD]
[TD="bgcolor: transparent"]Supplier Coordinator[/TD]
[TD="bgcolor: transparent"]ABC Company[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]37.5[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: yellow"]ABC[/TD]
[TD="class: xl66, bgcolor: transparent"]Supplier Coordinator[/TD]
[TD="class: xl66, bgcolor: transparent"]ABC Company[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]37.5[/TD]
[TD="class: xl68, bgcolor: #8db4e2, align: right"]75.00[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow"]DEF[/TD]
[TD="bgcolor: transparent"]Supplier Coordinator[/TD]
[TD="bgcolor: transparent"]ABC Company[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]37.5[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow"]DEF[/TD]
[TD="bgcolor: transparent"]Supplier Coordinator[/TD]
[TD="bgcolor: transparent"]ABC Company[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]37.5[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow"]DEF[/TD]
[TD="bgcolor: transparent"]Supplier Coordinator[/TD]
[TD="bgcolor: transparent"]ABC Company[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]30[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: yellow"]DEF[/TD]
[TD="class: xl66, bgcolor: transparent"]Supplier Coordinator[/TD]
[TD="class: xl66, bgcolor: transparent"]ABC Company[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]30[/TD]
[TD="class: xl68, bgcolor: #8db4e2, align: right"]97.50[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow"]HJLKP[/TD]
[TD="bgcolor: transparent"]Account Assistant Associate[/TD]
[TD="bgcolor: transparent"]ABC Company[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]37.5[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow"]HJLKP[/TD]
[TD="bgcolor: transparent"]Tax Processor ll[/TD]
[TD="bgcolor: transparent"]ABC Company[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]0.75[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow"]HJLKP[/TD]
[TD="bgcolor: transparent"]Tax Processor ll[/TD]
[TD="bgcolor: transparent"]ABC Company[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]3[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow"]HJLKP[/TD]
[TD="bgcolor: transparent"]Tax Processor ll[/TD]
[TD="bgcolor: transparent"]ABC Company[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]1.5[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: yellow"]HJLKP[/TD]
[TD="class: xl66, bgcolor: transparent"]Tax Processor ll[/TD]
[TD="class: xl66, bgcolor: transparent"]ABC Company[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]2.75[/TD]
[TD="class: xl68, bgcolor: #8db4e2, align: right"]45.50

This is the result using Excel 2010[/TD]
[/TR]
</TBODY>[/TABLE]

 

[Tanner_2004]

This is the raw data.
I want to sum the hours on every name change, insert a column to the right of the hours column, then copy the subtotaled hours for each name on the last row of each name. Thank yiou. I couldn't get things going with Box.

[TABLE="width: 442"]
<COLGROUP><COL style="WIDTH: 141pt; mso-width-source: userset; mso-width-alt: 6684" span=2 width=188><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4522" width=127><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3072" width=86><TBODY>[TR]
[TD="class: xl67, width: 188, bgcolor: yellow"]Name[/TD]
[TD="width: 188, bgcolor: transparent"]Position[/TD]
[TD="width: 127, bgcolor: transparent"]Vendor[/TD]
[TD="class: xl67, width: 86, bgcolor: yellow"]Hours[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: yellow"]ABC[/TD]
[TD="bgcolor: transparent"]Supplier Coordinator[/TD]
[TD="bgcolor: transparent"]ABC Company[/TD]
[TD="class: xl67, bgcolor: yellow, align: right"]37.5[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: yellow"]ABC[/TD]
[TD="bgcolor: transparent"]Supplier Coordinator[/TD]
[TD="bgcolor: transparent"]ABC Company[/TD]
[TD="class: xl67, bgcolor: yellow, align: right"]37.5[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: yellow"]DEF[/TD]
[TD="bgcolor: transparent"]Supplier Coordinator[/TD]
[TD="bgcolor: transparent"]ABC Company[/TD]
[TD="class: xl67, bgcolor: yellow, align: right"]37.5[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: yellow"]DEF[/TD]
[TD="bgcolor: transparent"]Supplier Coordinator[/TD]
[TD="bgcolor: transparent"]ABC Company[/TD]
[TD="class: xl67, bgcolor: yellow, align: right"]37.5[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: yellow"]DEF[/TD]
[TD="bgcolor: transparent"]Supplier Coordinator[/TD]
[TD="bgcolor: transparent"]ABC Company[/TD]
[TD="class: xl67, bgcolor: yellow, align: right"]30[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: yellow"]DEF[/TD]
[TD="bgcolor: transparent"]Supplier Coordinator[/TD]
[TD="bgcolor: transparent"]ABC Company[/TD]
[TD="class: xl67, bgcolor: yellow, align: right"]30[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: yellow"]HJLKP[/TD]
[TD="bgcolor: transparent"]Account Assistant Associate[/TD]
[TD="bgcolor: transparent"]ABC Company[/TD]
[TD="class: xl67, bgcolor: yellow, align: right"]37.5[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: yellow"]HJLKP[/TD]
[TD="bgcolor: transparent"]Tax Processor ll[/TD]
[TD="bgcolor: transparent"]ABC Company[/TD]
[TD="class: xl67, bgcolor: yellow, align: right"]0.75[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: yellow"]HJLKP[/TD]
[TD="bgcolor: transparent"]Tax Processor ll[/TD]
[TD="bgcolor: transparent"]ABC Company[/TD]
[TD="class: xl67, bgcolor: yellow, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: yellow"]HJLKP[/TD]
[TD="bgcolor: transparent"]Tax Processor ll[/TD]
[TD="bgcolor: transparent"]ABC Company[/TD]
[TD="class: xl67, bgcolor: yellow, align: right"]1.5[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: yellow"]HJLKP[/TD]
[TD="bgcolor: transparent"]Tax Processor ll[/TD]
[TD="bgcolor: transparent"]ABC Company[/TD]
[TD="class: xl67, bgcolor: yellow, align: right"]2.75[/TD]
[/TR]
</TBODY>[/TABLE]

 

[hiker95]

Tanner_2004,


Sample raw data:

Excel Workbook
	A	B	C	D	E	F
1	Name	Position	Vendor	Hours
2	ABC	Supplier Coordinator	ABC Company	37.5
3	ABC	Supplier Coordinator	ABC Company	37.5
4	DEF	Supplier Coordinator	ABC Company	37.5
5	DEF	Supplier Coordinator	ABC Company	37.5
6	DEF	Supplier Coordinator	ABC Company	30
7	DEF	Supplier Coordinator	ABC Company	30
8	HJLKP	Account Assistant Associate	ABC Company	37.5
9	HJLKP	Tax Processor ll	ABC Company	0.75
10	HJLKP	Tax Processor ll	ABC Company	3
11	HJLKP	Tax Processor ll	ABC Company	1.5
12	HJLKP	Tax Processor ll	ABC Company	2.75
13
Sheet1

After the macro:

Excel Workbook
	A	B	C	D	E	F
1	Name	Position	Vendor	Hours	Total Hours
2	ABC	Supplier Coordinator	ABC Company	37.5
3	ABC	Supplier Coordinator	ABC Company	37.5	75	37.5
4	DEF	Supplier Coordinator	ABC Company	37.5
5	DEF	Supplier Coordinator	ABC Company	37.5
6	DEF	Supplier Coordinator	ABC Company	30
7	DEF	Supplier Coordinator	ABC Company	30	97.5
8	HJLKP	Account Assistant Associate	ABC Company	37.5
9	HJLKP	Tax Processor ll	ABC Company	0.75
10	HJLKP	Tax Processor ll	ABC Company	3
11	HJLKP	Tax Processor ll	ABC Company	1.5
12	HJLKP	Tax Processor ll	ABC Company	2.75	45.5	8
13
Sheet1

if the subtotaled hours for an employee is great than a constant, the subtotal hours exceeding the constant would appear in another cell

I assume the constant is 37.5 hours?

According to your logic, the correct answers should be the cells with the YELLOW background?

 

[Tanner_2004]

Wow, this is more than I wanted , but excellent. How do I look up the answer? And I can't thank you enough.

 

[hiker95]

Tanner_2004,


Sample raw data (already sorted/grouped per your sample by column A, Name):

Excel Workbook
	A	B	C	D	E
1	Name	Position	Vendor	Hours
2	ABC	Supplier Coordinator	ABC Company	37.5
3	ABC	Supplier Coordinator	ABC Company	37.5
4	DEF	Supplier Coordinator	ABC Company	37.5
5	DEF	Supplier Coordinator	ABC Company	37.5
6	DEF	Supplier Coordinator	ABC Company	30
7	DEF	Supplier Coordinator	ABC Company	30
8	HJLKP	Account Assistant Associate	ABC Company	37.5
9	HJLKP	Tax Processor ll	ABC Company	0.75
10	HJLKP	Tax Processor ll	ABC Company	3
11	HJLKP	Tax Processor ll	ABC Company	1.5
12	HJLKP	Tax Processor ll	ABC Company	2.75
13
Sheet1

After the macro:

Excel Workbook
	A	B	C	D	E
1	Name	Position	Vendor	Hours	Total Hours
2	ABC	Supplier Coordinator	ABC Company	37.5
3	ABC	Supplier Coordinator	ABC Company	37.5	37.50
4	DEF	Supplier Coordinator	ABC Company	37.5
5	DEF	Supplier Coordinator	ABC Company	37.5
6	DEF	Supplier Coordinator	ABC Company	30
7	DEF	Supplier Coordinator	ABC Company	30	97.50
8	HJLKP	Account Assistant Associate	ABC Company	37.5
9	HJLKP	Tax Processor ll	ABC Company	0.75
10	HJLKP	Tax Processor ll	ABC Company	3
11	HJLKP	Tax Processor ll	ABC Company	1.5
12	HJLKP	Tax Processor ll	ABC Company	2.75	8.00
13
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub FindTotalHours()
' hiker95, 09/15/2012
' http://www.mrexcel.com/forum/excel-questions/659407-macro-subtotal-hours-associated-name-then-perform-calcunation-subtotal.html
Dim r As Long, lr As Long, n As Long, sr As Long, er As Long
Application.ScreenUpdating = False
Columns(5).ClearContents
Cells(1, 5).Value = "Total Hours"
lr = Cells(Rows.Count, 1).End(xlUp).Row
For r = 2 To lr
  n = Application.CountIf(Columns(1), Cells(r, 1).Value)
  sr = r
  er = sr + n - 1
  With Cells(er, 5)
    .Formula = "=Sum(D" & sr & ":D" & er & ")-37.5"
    .NumberFormat = "#,##0.00"
    .Font.Bold = True
  End With
  r = r + n - 1
Next r
Columns(5).AutoFit
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the FindTotalHours macro.