M
mslabbe
Or this way too complicated?
I'm trying to figure out if this can be done. I'm trying to see if we can
create an ordering matrix based on inventory and needs for a two week lead
time. We have on opportunity to save time by ordering in bulk. However, we
need to know in a week's period how many bulk gallons to order and then order
the rest in single amounts.
The worksheet has Dates in col A...I need to seperate it to weeks SUN-SAT.
It has a date range of 2-3 weeks (where Week 1 is already ordered but we need
to know if we ordered the correct bulk and single amounts, Week 2 will be for
the new order and week three is more of a safety stock).
Col C has the names of Products
Col E has the amounts needed in that day (say gallons of the product name).
Col I has its bulk gallon increments.
What I'd like to do is based on weeks (week 1, then week 2 and then week 3),
staring with week 1, look for a desired product (col C) to how many gallons
is needed to its ordered increments for the entire week.
EXAMPLE
Col A Col C Col E Col I Order bulk order single
2/11/2008 Prod A 84 28 3.0 3 0
2/12/2008 Prod A 14 28 0.5 0 1
2/12/2008 Prod B 1000 900 1.1 1 1
2/12/2008 Prod C 300 150 2.0 2 0
2/13/2008 Prod B 450 900 0.5 0 1
2/14/2008 Prod A 56 28 2.0 2 0
2/14/2008 Prod B 1800 900 2.0 2 0
2/15/2008 Prod A 98 28 3.5 3 1
2/15/2008 Prod B 900 900 1.0 1 0
2/15/2008 Prod C 300 150 2.0 2 0
2/18/2008 Prod A 98 28 3.5 3 1
2/18/2008 Prod C 300 150 2.0 2 0
2/19/2008 Prod A 84 28 3.0 3 0
2/19/2008 Prod B 900 900 1.0 1 0
2/20/2008 Prod A 14 28 0.5 0 1
2/20/2008 Prod B 450 900 0.5 0 1
2/20/2008 Prod C 300 150 2.0 2 0
2/21/2008 Prod A 56 28 2.0 2 0
2/21/2008 Prod B 450 900 0.5 0 1
2/21/2008 Prod C 200 150 1.3 1 1
2/22/2008 Prod B 1000 900 1.1 1 1
2/22/2008 Prod C 300 150 2.0 2 0
2/23/2008 Prod A 84 28 3.0 3 0
2/23/2008 Prod B 1000 900 1.1 1 1
Any feed back would be greatly appreciated...its probably too long to figure
out, but I figured I'd ask...maybe someone has already done something similar
or has ideas on where to start?
Thanks in advance
Cheers
I'm trying to figure out if this can be done. I'm trying to see if we can
create an ordering matrix based on inventory and needs for a two week lead
time. We have on opportunity to save time by ordering in bulk. However, we
need to know in a week's period how many bulk gallons to order and then order
the rest in single amounts.
The worksheet has Dates in col A...I need to seperate it to weeks SUN-SAT.
It has a date range of 2-3 weeks (where Week 1 is already ordered but we need
to know if we ordered the correct bulk and single amounts, Week 2 will be for
the new order and week three is more of a safety stock).
Col C has the names of Products
Col E has the amounts needed in that day (say gallons of the product name).
Col I has its bulk gallon increments.
What I'd like to do is based on weeks (week 1, then week 2 and then week 3),
staring with week 1, look for a desired product (col C) to how many gallons
is needed to its ordered increments for the entire week.
EXAMPLE
Col A Col C Col E Col I Order bulk order single
2/11/2008 Prod A 84 28 3.0 3 0
2/12/2008 Prod A 14 28 0.5 0 1
2/12/2008 Prod B 1000 900 1.1 1 1
2/12/2008 Prod C 300 150 2.0 2 0
2/13/2008 Prod B 450 900 0.5 0 1
2/14/2008 Prod A 56 28 2.0 2 0
2/14/2008 Prod B 1800 900 2.0 2 0
2/15/2008 Prod A 98 28 3.5 3 1
2/15/2008 Prod B 900 900 1.0 1 0
2/15/2008 Prod C 300 150 2.0 2 0
2/18/2008 Prod A 98 28 3.5 3 1
2/18/2008 Prod C 300 150 2.0 2 0
2/19/2008 Prod A 84 28 3.0 3 0
2/19/2008 Prod B 900 900 1.0 1 0
2/20/2008 Prod A 14 28 0.5 0 1
2/20/2008 Prod B 450 900 0.5 0 1
2/20/2008 Prod C 300 150 2.0 2 0
2/21/2008 Prod A 56 28 2.0 2 0
2/21/2008 Prod B 450 900 0.5 0 1
2/21/2008 Prod C 200 150 1.3 1 1
2/22/2008 Prod B 1000 900 1.1 1 1
2/22/2008 Prod C 300 150 2.0 2 0
2/23/2008 Prod A 84 28 3.0 3 0
2/23/2008 Prod B 1000 900 1.1 1 1
Any feed back would be greatly appreciated...its probably too long to figure
out, but I figured I'd ask...maybe someone has already done something similar
or has ideas on where to start?
Thanks in advance
Cheers