Break into 24 hour schedules

bluetaurean · Oct 2, 2012

Sajan · Oct 2, 2012

Hi,

Could you clarify your requirements?

In your example, you indicate that Day 3 for CIP should be 4 hrs, even though there are 8 hours remaining in the 24 hour schedule for Day 3. That seems to be different from the schedule for Day 1 and Day 2.

Shouldn't the breakdown be as follows?

[pre]

Code:

Day	Product	      Duration
1	ProductA	24
2	ProductA	16
2	ProductB	8
3	ProductB	12
3	CIP	        5
3	ProductC	7
4	ProductC	8

[/pre]
-Sajan.

Luke M · Oct 2, 2012

Are you open to use a macro or a UDF? I can't think of a nice way to do this using just formulas w/o getting caught in a circular loop.

Sajan · Oct 2, 2012

Hi Luke,

I think the following formula would do the job, as long my interpretation of the requirments are correct. (i.e. there are 24 hours in a day, and you would try to allocate each day to the maximum.)

I changed the layout of the result as follows (to be more of a GANTT view)

[pre]

Code:

---------------Day1	Day2	Day3	Day4
ProductA	24	16	0	0
ProductB	0	8	12	0
CIP	        0	0	5	0
ProductC	0	0	7	8

[/pre]
Assumptions:

1. source list is in the range A2:B5

2. Result list is in the range starting D1:H5 (with D1 being the blank cell, and E1 saying "Day1", etc. Similarly, "ProductA" is listed on cell D2, "ProductB" is in cell D3, etc.)

3. The allocation of hours to each day would be in the same order as the listing of products

Put the following formula in cell E2, and copy to the right and down (to the rest of the range E2:H5):

=IF(($B2-SUM($D2:D2)) > (24-SUM(E$1:E1)), (24-SUM(E$1:E1)), $B2-SUM($D2:D2))

You can extend this approach to additional days or products as needed, as well as format the cells so that the zeros are not displayed.

Cheers,

Sajan.

Edit: Since the columns were not aligning correctly, I added the dashes in the sample result.

Luke M · Oct 2, 2012

@Sajan

Very nice Sajan! I like the Gantt layout and the flexibility of the formula. We could even set it up to use something other than 24 hrs / day if we needed to. Again, tip my hat to you!

Sajan · Oct 2, 2012

Hi Luke,

Thanks for the kind words! I am glad we were able to identify a solution.

Regards,

Sajan.

bluetaurean · Oct 3, 2012

Hi Sajan & Luke,

thanks for ideas. it is indeed what is required.

Would be interesting to see a vba based solution as well. the only drawback with the formula is that everytime the order is changed by moving cells the formuals will have to be recopied. A flat out will be helpful in then pivoting similar output for multiple ranges (production lines)

thanks again,

Sajan · Oct 3, 2012

@Bluetaurean,

For a VBA based solution, one of the VBA experts on this forum will need to help you.

As far as your comment about needing to adjust the formula when the source cells are moved, you could make two adjustments to my formula to avoid that:

1. Reference the source product names using formulas that are independent of the location using INDEX() and ROW() functions

2. Change the reference to B2 in the formula with a reference using INDEX() and MATCH() functions

For example,

For the data ranges in my sample referenced in the previous post, change the formula for cell E2:E5 with =INDEX(A:A,ROW())

For cell F2, use the following formula:

=IF((INDEX($B:$B, MATCH($E2,$A:$A, 0))-SUM($E2:E2)) > (24-SUM(F$1:F1)), (24-SUM(F$1:F1)), INDEX($B:$B, MATCH($E2,$A:$A, 0))-SUM($E2:E2))

copy it down and to the right as needed.

VBA might offer a more elegant solution for a "flat listing" (instead of the pivot-table like view provided by the formula). Hopefully, someone else will help you with that.

Cheers,

Sajan.

SirJB7 · Oct 3, 2012

Hi, bluetaurean!

Give a look at this file:

https://dl.dropbox.com/u/60558749/Break%20into%2024%20hour%20schedules%20%28for%20bluetaurean%20at%20chandoo.org%29.xlsm

Load/type columns A:B and then run GenerateDailyProduction macro to fulfill columns D:F.

Take care of the two dynamically defined named ranges when implementing the solution in your real workbook.

Regards!

PS: Posting code regarding CASFFML Luke M's favorite excuse...

-----

[pre]

Code:

Option Explicit

Sub GenerateDailyProduction()
&#39; constants
Const ksProduct = &#34;ProductList&#34;
Const ksDaily = &#34;DailyList&#34;
Const kiHours = 24
&#39; declarations
Dim rngP As Range, rngD As Range
Dim I As Long, J As Integer
Dim lDay As Long, iHours As Integer, lDayProduction As Long
Dim sProduct As String, iProductionHours As Integer, iAllocatedHours As Integer
&#39; start
Application.DisplayAlerts = False
Set rngP = Range(ksProduct)
Set rngD = Range(ksDaily)
With rngD
If .Rows.Count &#62; 1 Then Range(.Rows(2), .Rows(.Rows.Count)).ClearContents
End With
&#39; process
With rngP
lDayProduction = 1
lDay = 0
iHours = kiHours
For I = 1 To .Rows.Count
&#39; values
sProduct = .Cells(I, 1).Value
iProductionHours = _
Val(Left$(.Cells(I, 2).Value, InStr(.Cells(I, 2).Value, &#34; &#34;) - 1))
&#39; distribute hours
iAllocatedHours = 0
Do While iAllocatedHours &#60; iProductionHours
&#39; new day
If iHours &#62;= kiHours Then
iHours = 0
lDay = lDay + 1
End If
&#39; new day production
If iProductionHours - iAllocatedHours &#60;= kiHours - iHours Then
J = iProductionHours - iAllocatedHours
Else
J = kiHours - iHours
End If
iAllocatedHours = iAllocatedHours + J
iHours = iHours + J
lDayProduction = lDayProduction + 1
rngD.Cells(lDayProduction, 1).Value = lDay
rngD.Cells(lDayProduction, 2).Value = sProduct
rngD.Cells(lDayProduction, 3).Value = J
Loop
Next I
End With
&#39; end
rngD.Cells(2, 1).Select
Set rngD = Nothing
Set rngP = Nothing
Application.DisplayAlerts = True
Beep
End Sub
-----

[/pre]

Luke M · Oct 3, 2012

SirJB7 beat me this time, but here's my take at a macro. Takes data in columns A:B, starting in row 2 and going however long is needed, outputting to columns D:F, starting in row 2.

[pre]

Code:

Sub CreateSchedule()
Dim LastRow As Long
Dim DayCount As Long &#39;What day are we on
Dim HourCount As Long &#39;How many hours left for a product
Dim HoursRemain As Long &#39;Hours left in day
Dim HoursUsed As Long &#39;How many hours used from product so far
Dim ProductList As Range
Dim HourList As Range
Dim i As Long &#39;List counter
Dim OutputRow As Long
Dim PName As String &#39;Product name

With ActiveSheet
LastRow = .Cells(.Rows.Count, &#34;A&#34;).End(xlUp).Row
End With

Set ProductList = Range(&#34;A2&#34;, Cells(LastRow, &#34;A&#34;))
Set HourList = ProductList.Offset(0, 1)

DayCount = 1
HoursRemain = 24

&#39;What row does output begin on?
OutputRow = 2

i = 1
PName = ProductList.Cells(i).Value
HourCount = HourList.Cells(i).Value
HoursUsed = 0
&#39;Start building list

Application.ScreenUpdating = False
Range(&#34;D:F&#34;).ClearContents &#39;clear old list
Do Until i &#62; ProductList.Count

Cells(OutputRow, &#34;D&#34;).Value = DayCount
Cells(OutputRow, &#34;E&#34;).Value = PName
HoursUsed = WorksheetFunction.Min(HourCount, HoursRemain)
Cells(OutputRow, &#34;F&#34;).Value = HoursUsed

HoursRemain = HoursRemain - HoursUsed
If HoursRemain &#60;= 0 Then &#39;Out of hours for the day
HoursRemain = 24
HourCount = HourCount - HoursUsed
DayCount = DayCount + 1
Else
&#39;Product is complete, go to next product
i = i + 1
PName = ProductList.Cells(i).Value
HourCount = HourList.Cells(i).Value
HoursUsed = 0
End If

&#39;Happens when hours left on product
&#39;equals number of hours left in day
If HourCount = 0 Then
&#39;Product is complete, go to next product
i = i + 1
PName = ProductList.Cells(i).Value
HourCount = HourList.Cells(i).Value
HoursUsed = 0
End If
OutputRow = OutputRow + 1

Loop
Application.ScreenUpdating = True

End Sub

[/pre]

Sajan · Oct 3, 2012

Hello,

For purely academic interests, here is a formula based approach that produces a flat listing.

I have used one helper column, to calculate the cumulative durations.

Source data is assumed to be in A1:C5 as shown in the sample:

[pre]

Code:

#  A           B          C
1  Product     Duration	  CumDuration
2  ProductA	10	  10
3  ProductB	20	  30
4  CIP	        3	  33
5  ProductC	4	  37
i.e. first product is listed in cell A2, etc.


For ease of reference, I have also used the following Named Ranges:

PList refers to A2:A5

PDuration refers to B2:B5

CumDuration refers to C2:C5


CumDuration was calculated as =SUM(B$2:B2) in cell C2, and then copied down through C5.


In my sample output, I have put them in the range E8:G14, and looks as follows:

#    E          F               G
8    Day	Product	        Duration
9    1	        ProductA	10
10   1	        ProductB	14
11   2	        ProductB	6
12   2	        CIP	        3
13   2	        ProductC	4
14   …	        …	        …

[/pre]
In cell E8, put the following formula (entered with Ctrl+Shift+Enter):

=IF(SUM(G$8:G8)>=SUMPRODUCT(PDuration), "…", IF(N(E8)=0, 1, IF(SUMIFS(G$8:G8, E$8:E8, E8)>=24, E8+1, E8)))

Copy it to the rows below (i.e. through E14)

In cell F8, put the following formula (entered with Ctrl+Shift+Enter):

=IF(SUM(G$8:G8)>=SUMPRODUCT(PDuration), "…",INDEX(PList, MATCH(1, IF(CumDuration-SUM(G$8:G8) > 0, 1), 0)))

Copy it to the rows below (i.e. through F14)

In cell G8, put the following formula (entered with Ctrl+Shift+Enter):

=IF(SUM(G$8:G8)>=SUMPRODUCT(PDuration), "…",IF(INDEX(PDuration, MATCH(1, IF(CumDuration-SUM(G$8:G8) > 0, 1), 0)) - SUMIFS(G$8:G8, F$8:F8,F9) > 24-SUMPRODUCT(--(E$8:E8=E9), IF(ISNUMBER(G$8:G8), G$8:G8, 0)), 24-SUMPRODUCT(--(E$8:E8=E9), IF(ISNUMBER(G$8:G8), G$8:G8, 0)), INDEX(PDuration, MATCH(1, IF(CumDuration-SUM(G$8:G8) > 0, 1), 0)) - SUMIFS(G$8:G8, F$8:F8,F9)))

Copy it to the rows below (i.e. through G14)

As the last row in the sample output shows, if you copy the formula to more rows than available duration to allocate, it will display "..."

I have not attempted to optimize the formulas in any way, since this was more to illustrate what is possible with just formulas. If anyone wants to know what the formulas are doing, let me know, and I can write up an explanation.

Cheers,

Sajan.

SirJB7 · Oct 3, 2012

@Luke M

Hi!

Looking over my shoulder again?

Almost only changed the variable names...

Regards!

Luke M · Oct 3, 2012

@SirJB7

I'll go w/ "great minds think alike". Besides, you know I have CASFFML issues! =P

SirJB7 · Oct 3, 2012

@SirJB7

Hi, myself!

So long...

Ok, I think I got it, the guy with the more than convenient CASFFML issue is one of those, but who the hell might be the others?

Regards!

Sajan · Oct 3, 2012

Hi bluetaurean,

Here are the formulas for creating a flat list, slightly shortened. Essentially, the shorter formula replaces IF(A<B, B, A) logic with MIN(A,B)

Formula for cell E9 (to be copied down through E14), entered with Ctrl+Shift+Enter:

=IF(SUM(G$8:G8)>=SUMPRODUCT(PDuration), "…", MAX( N(E8) + 1*(SUMIFS(G$8:G8, E$8:E8, E8)>=24), 1))

Formula for cell F9 (to be copied down through F14), entered with Ctrl+Shift+Enter:

=IF(SUM(G$8:G8)>=SUMPRODUCT(PDuration), "…",INDEX(PList, MATCH(TRUE, (CumDuration-SUM(G$8:G8)) > 0, 0)))

Formula for cell G9 (to be copied down through G14), entered with Ctrl+Shift+Enter:

=IF(SUM(G$8:G8)>=SUMPRODUCT(PDuration), "…",MIN(INDEX(PDuration, MATCH(TRUE, CumDuration-SUM(G$8:G8) > 0, 0)) - SUMIFS(G$8:G8, F$8:F8,F9), 24-SUMPRODUCT((E$8:E8=E9)* IF(ISNUMBER(G$8:G8), G$8:G8, 0))))

Now you could use the formula approach or VBA approach. You have choices!!

Cheers,

Sajan.

bluetaurean · Oct 6, 2012

@Luke, Sajan, Sirjb7,

Thanks a lot for help.

Break into 24 hour schedules

bluetaurean

Member

Sajan

Excel Ninja

Luke M

Excel Ninja

Sajan

Excel Ninja

Luke M

Excel Ninja

Sajan

Excel Ninja

bluetaurean

Member

Sajan

Excel Ninja

SirJB7

Excel Rōnin

Luke M

Excel Ninja

Sajan

Excel Ninja

SirJB7

Excel Rōnin

Luke M

Excel Ninja

SirJB7

Excel Rōnin

Sajan

Excel Ninja

bluetaurean

Member