Help with creating a Macro
If you want to calculate the total units produced per hour for one particular
order,
then your requirement can be met with formulae...
Here is what you need to do;
Assuming you have data in the sheet named Sheet1 then in Sheet2 A1
Enter a Order#
in B1 enter this formula
=Vlookup(A1,Sheet1!B:D,3,False) to get # of Units for order# in A1
In C1 enter this formula
=B1/(SUMPRODUCT(((Sheet1!E2:E100*60)+(Sheet1!H2:H100)) ,--(Sheet1!B2:B100=A1)))
[Change 100 to end of your data set on Sheet1]
Note: Your formula for total minutes should be E2*60 + H2 and not (E2+H2)*60!!
By entering the Order# in A1 you can get the result for different Order#s.
You can also enter unique Orders#s in Col A of Sheet2 and copy the formula
down.
Do change references to absolute references by changing E2:E100 to E$2:E$100
and so on...
"Liz J" wrote:
I am putting together a productivity report and I need some help with
creating a macro. I feel a macro would be the most accurate and efficient
option for manipulating this data. If you think different, please review my
question, then let me know. Thank you!
Below is the column data I'm working with:
Column A: Team Name
Column B: Order #
Column C: Customer Name
Column D: # of Units
Column E: # of Hours
Column F: Start Time
Column G: End Time
Column H: # of Minutes
I want to figure out the total units produced in an hour timeframe for (at
the change of) each Order # (Column B). In general, I would like to create a
macro that allows calculations for one order # that has data on multiple
lines. (Please look at the equation(s) explanation below.)
Here's what I'm looking at, as far as numbers are concerned:
A B C D E F
G H
1 Team: Order #: Customer: Units: Hours: Start: End:
Minutes:
2 Blue 1234 ABC Store 550 12 2:27pm 3:34pm 77
3 Red 5678 DEF Store 2100 12 8:18am 9:40am 82
4 Green 5678 DEF Store 2100 9 9:42am 10:02am 20
So, if we were to calculate one order, that has data only on one row, that
equation would be =(D2/(H2*E2))*60. This equation assumes the time for each
order is only on a singular row. That, however, is not the case for most of
the production times. Most of the order data are on multiple rows. For
example, row 2 might capture all of the order's data (as would be the case
for the (D2/(H2*E2))*60) equation), but others might take two rows or more.
This would then change the equation to something like this:
=(D4/((H3*E3)+(H4*E4)))*60 [assuming one order took the next two rows].
Some things to know:
Even if an order populates data for more than one row, Column D: # of Units,
does not change. For example, for order #5678 (in rows 2 and 3), the total
production was 2100 units and the order information populated two rows, D3
and D4 all read 2100. (This also explain why my example equation:
=(D4/((H3*E3)+(H4*E4)))*60 only takes cell D4 instead of adding cells D3 and
D4 before dividing by ((H3*E3)+(H4*E4)).)
I know this might seem confusing, but I would appreciate any comments you
can provide. Thanks for all of your help!
"Liz J" wrote:
I am putting together a productivity report and I need some help with
creating a macro. I feel a macro would be the most accurate and efficient
option for manipulating this data. If you think different, please review my
question, then let me know. Thank you!
Below is the column data I'm working with:
Column A: Team Name
Column B: Order #
Column C: Customer Name
Column D: # of Units
Column E: # of Hours
Column F: Start Time
Column G: End Time
Column H: # of Minutes
I want to figure out the total units produced in an hour timeframe for (at
the change of) each Order # (Column B). In general, I would like to create a
macro that allows calculations for one order # that has data on multiple
lines. (Please look at the equation(s) explanation below.)
Here's what I'm looking at, as far as numbers are concerned:
A B C D E F
G H
1 Team: Order #: Customer: Units: Hours: Start: End:
Minutes:
2 Blue 1234 ABC Store 550 12 2:27pm 3:34pm 77
3 Red 5678 DEF Store 2100 12 8:18am 9:40am 82
4 Green 5678 DEF Store 2100 9 9:42am 10:02am 20
So, if we were to calculate one order, that has data only on one row, that
equation would be =(D2/(H2*E2))*60. This equation assumes the time for each
order is only on a singular row. That, however, is not the case for most of
the production times. Most of the order data are on multiple rows. For
example, row 2 might capture all of the order's data (as would be the case
for the (D2/(H2*E2))*60) equation), but others might take two rows or more.
This would then change the equation to something like this:
=(D4/((H3*E3)+(H4*E4)))*60 [assuming one order took the next two rows].
Some things to know:
Even if an order populates data for more than one row, Column D: # of Units,
does not change. For example, for order #5678 (in rows 2 and 3), the total
production was 2100 units and the order information populated two rows, D3
and D4 all read 2100. (This also explain why my example equation:
=(D4/((H3*E3)+(H4*E4)))*60 only takes cell D4 instead of adding cells D3 and
D4 before dividing by ((H3*E3)+(H4*E4)).)
I know this might seem confusing, but I would appreciate any comments you
can provide. Thanks for all of your help!
|