boomerang_1
New Member
- Joined
- Jun 27, 2019
- Messages
- 1
Hi All,
Just checking if anyone is able to assist me with setting up a code to add rows (based on a condition) and sum lines above.
Raw data is is below.
[TABLE="width: 1006"]
<tbody>[TR]
[TD]State[/TD]
[TD]Store[/TD]
[TD]Staff Name[/TD]
[TD]Day[/TD]
[TD]Date[/TD]
[TD]Hours Category 1[/TD]
[TD]Hours Category 2[/TD]
[TD]Hours Category 3[/TD]
[TD]Hours Category 4[/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Store 1[/TD]
[TD]Staff 1[/TD]
[TD]Tuesday[/TD]
[TD="align: right"]11/06/19[/TD]
[TD="align: right"]8.55[/TD]
[TD="align: right"]8.50[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Store 1[/TD]
[TD]Staff 1[/TD]
[TD]Wednesday[/TD]
[TD="align: right"]12/06/19[/TD]
[TD="align: right"]8.17[/TD]
[TD="align: right"]8.12[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Store 1[/TD]
[TD]Staff 1[/TD]
[TD]Thursday[/TD]
[TD="align: right"]13/06/19[/TD]
[TD="align: right"]6.32[/TD]
[TD="align: right"]6.32[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Store 1[/TD]
[TD]Staff 1[/TD]
[TD]Friday[/TD]
[TD="align: right"]14/06/19[/TD]
[TD="align: right"]8.32[/TD]
[TD="align: right"]8.30[/TD]
[TD="align: right"]0.02[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Store 1[/TD]
[TD]Staff 1[/TD]
[TD]Saturday[/TD]
[TD="align: right"]15/06/19[/TD]
[TD="align: right"]6.93[/TD]
[TD="align: right"]6.93[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Store 1[/TD]
[TD]Staff 2[/TD]
[TD]Tuesday[/TD]
[TD="align: right"]11/06/19[/TD]
[TD="align: right"]8.28[/TD]
[TD="align: right"]8.23[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Store 1[/TD]
[TD]Staff 2[/TD]
[TD]Wednesday[/TD]
[TD="align: right"]12/06/19[/TD]
[TD="align: right"]7.42[/TD]
[TD="align: right"]7.42[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Store 1[/TD]
[TD]Staff 2[/TD]
[TD]Thursday[/TD]
[TD="align: right"]13/06/19[/TD]
[TD="align: right"]6.48[/TD]
[TD="align: right"]6.48[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Store 1[/TD]
[TD]Staff 3[/TD]
[TD]Friday[/TD]
[TD="align: right"]14/06/19[/TD]
[TD="align: right"]3.27[/TD]
[TD="align: right"]3.27[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
I would like the macro to do the following;
> If staff1 works from Mon-Fri (weekday), then add row and sum hours (bold font & top border if possible)
> If staff1 works form Sat to/or Sun (weekend), do nothing (i.e. just list the hours)
Result:
[TABLE="width: 1006"]
<tbody>[TR]
[TD]State[/TD]
[TD]Store[/TD]
[TD]Staff Name[/TD]
[TD]Day[/TD]
[TD]Date[/TD]
[TD]Hours Category 1[/TD]
[TD]Hours Category 2[/TD]
[TD]Hours Category 3[/TD]
[TD]Hours Category 4[/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Store 1[/TD]
[TD="align: left"]Staff 1[/TD]
[TD]Tuesday[/TD]
[TD="align: right"]11/06/19[/TD]
[TD="align: right"]8.55[/TD]
[TD="align: right"]8.50[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Store 1[/TD]
[TD]Staff 1[/TD]
[TD]Wednesday[/TD]
[TD="align: right"]12/06/19[/TD]
[TD="align: right"]8.17[/TD]
[TD="align: right"]8.12[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Store 1[/TD]
[TD]Staff 1[/TD]
[TD]Thursday[/TD]
[TD="align: right"]13/06/19[/TD]
[TD="align: right"]6.32[/TD]
[TD="align: right"]6.32[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Store 1[/TD]
[TD]Staff 1[/TD]
[TD]Friday[/TD]
[TD="align: right"]14/06/19[/TD]
[TD="align: right"]8.32[/TD]
[TD="align: right"]8.30[/TD]
[TD="align: right"]0.02[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]31.35[/TD]
[TD="align: right"]31.23[/TD]
[TD="align: right"]0.12[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Store 1[/TD]
[TD]Staff 1[/TD]
[TD]Saturday[/TD]
[TD="align: right"]15/06/19[/TD]
[TD="align: right"]6.93[/TD]
[TD="align: right"]6.93[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Store 1[/TD]
[TD]Staff 2[/TD]
[TD]Tuesday[/TD]
[TD="align: right"]11/06/19[/TD]
[TD="align: right"]8.28[/TD]
[TD="align: right"]8.23[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Store 1[/TD]
[TD]Staff 2[/TD]
[TD]Wednesday[/TD]
[TD="align: right"]12/06/19[/TD]
[TD="align: right"]7.42[/TD]
[TD="align: right"]7.42[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Store 1[/TD]
[TD]Staff 2[/TD]
[TD]Thursday[/TD]
[TD="align: right"]13/06/19[/TD]
[TD="align: right"]6.48[/TD]
[TD="align: right"]6.48[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]22.18[/TD]
[TD="align: right"]22.13[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Store 1[/TD]
[TD]Staff 3[/TD]
[TD]Friday[/TD]
[TD="align: right"]14/06/19[/TD]
[TD="align: right"]3.27[/TD]
[TD="align: right"]3.27[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3.27[/TD]
[TD="align: right"]3.27[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
</tbody>[/TABLE]
Thank you so much for your help.
Just checking if anyone is able to assist me with setting up a code to add rows (based on a condition) and sum lines above.
Raw data is is below.
[TABLE="width: 1006"]
<tbody>[TR]
[TD]State[/TD]
[TD]Store[/TD]
[TD]Staff Name[/TD]
[TD]Day[/TD]
[TD]Date[/TD]
[TD]Hours Category 1[/TD]
[TD]Hours Category 2[/TD]
[TD]Hours Category 3[/TD]
[TD]Hours Category 4[/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Store 1[/TD]
[TD]Staff 1[/TD]
[TD]Tuesday[/TD]
[TD="align: right"]11/06/19[/TD]
[TD="align: right"]8.55[/TD]
[TD="align: right"]8.50[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Store 1[/TD]
[TD]Staff 1[/TD]
[TD]Wednesday[/TD]
[TD="align: right"]12/06/19[/TD]
[TD="align: right"]8.17[/TD]
[TD="align: right"]8.12[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Store 1[/TD]
[TD]Staff 1[/TD]
[TD]Thursday[/TD]
[TD="align: right"]13/06/19[/TD]
[TD="align: right"]6.32[/TD]
[TD="align: right"]6.32[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Store 1[/TD]
[TD]Staff 1[/TD]
[TD]Friday[/TD]
[TD="align: right"]14/06/19[/TD]
[TD="align: right"]8.32[/TD]
[TD="align: right"]8.30[/TD]
[TD="align: right"]0.02[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Store 1[/TD]
[TD]Staff 1[/TD]
[TD]Saturday[/TD]
[TD="align: right"]15/06/19[/TD]
[TD="align: right"]6.93[/TD]
[TD="align: right"]6.93[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Store 1[/TD]
[TD]Staff 2[/TD]
[TD]Tuesday[/TD]
[TD="align: right"]11/06/19[/TD]
[TD="align: right"]8.28[/TD]
[TD="align: right"]8.23[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Store 1[/TD]
[TD]Staff 2[/TD]
[TD]Wednesday[/TD]
[TD="align: right"]12/06/19[/TD]
[TD="align: right"]7.42[/TD]
[TD="align: right"]7.42[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Store 1[/TD]
[TD]Staff 2[/TD]
[TD]Thursday[/TD]
[TD="align: right"]13/06/19[/TD]
[TD="align: right"]6.48[/TD]
[TD="align: right"]6.48[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Store 1[/TD]
[TD]Staff 3[/TD]
[TD]Friday[/TD]
[TD="align: right"]14/06/19[/TD]
[TD="align: right"]3.27[/TD]
[TD="align: right"]3.27[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
I would like the macro to do the following;
> If staff1 works from Mon-Fri (weekday), then add row and sum hours (bold font & top border if possible)
> If staff1 works form Sat to/or Sun (weekend), do nothing (i.e. just list the hours)
Result:
[TABLE="width: 1006"]
<tbody>[TR]
[TD]State[/TD]
[TD]Store[/TD]
[TD]Staff Name[/TD]
[TD]Day[/TD]
[TD]Date[/TD]
[TD]Hours Category 1[/TD]
[TD]Hours Category 2[/TD]
[TD]Hours Category 3[/TD]
[TD]Hours Category 4[/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Store 1[/TD]
[TD="align: left"]Staff 1[/TD]
[TD]Tuesday[/TD]
[TD="align: right"]11/06/19[/TD]
[TD="align: right"]8.55[/TD]
[TD="align: right"]8.50[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Store 1[/TD]
[TD]Staff 1[/TD]
[TD]Wednesday[/TD]
[TD="align: right"]12/06/19[/TD]
[TD="align: right"]8.17[/TD]
[TD="align: right"]8.12[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Store 1[/TD]
[TD]Staff 1[/TD]
[TD]Thursday[/TD]
[TD="align: right"]13/06/19[/TD]
[TD="align: right"]6.32[/TD]
[TD="align: right"]6.32[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Store 1[/TD]
[TD]Staff 1[/TD]
[TD]Friday[/TD]
[TD="align: right"]14/06/19[/TD]
[TD="align: right"]8.32[/TD]
[TD="align: right"]8.30[/TD]
[TD="align: right"]0.02[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]31.35[/TD]
[TD="align: right"]31.23[/TD]
[TD="align: right"]0.12[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Store 1[/TD]
[TD]Staff 1[/TD]
[TD]Saturday[/TD]
[TD="align: right"]15/06/19[/TD]
[TD="align: right"]6.93[/TD]
[TD="align: right"]6.93[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Store 1[/TD]
[TD]Staff 2[/TD]
[TD]Tuesday[/TD]
[TD="align: right"]11/06/19[/TD]
[TD="align: right"]8.28[/TD]
[TD="align: right"]8.23[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Store 1[/TD]
[TD]Staff 2[/TD]
[TD]Wednesday[/TD]
[TD="align: right"]12/06/19[/TD]
[TD="align: right"]7.42[/TD]
[TD="align: right"]7.42[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Store 1[/TD]
[TD]Staff 2[/TD]
[TD]Thursday[/TD]
[TD="align: right"]13/06/19[/TD]
[TD="align: right"]6.48[/TD]
[TD="align: right"]6.48[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]22.18[/TD]
[TD="align: right"]22.13[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Store 1[/TD]
[TD]Staff 3[/TD]
[TD]Friday[/TD]
[TD="align: right"]14/06/19[/TD]
[TD="align: right"]3.27[/TD]
[TD="align: right"]3.27[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3.27[/TD]
[TD="align: right"]3.27[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
</tbody>[/TABLE]
Thank you so much for your help.