Guru Challenge
 

I've posted this problem before and have not as of yet got any response, let's try again. Heres what I got

I have a list of part numbers with due dates and operation times and status (see below

Part Number Due Date Labor Time Statu
1000 5/3/04 3 hours Critical
2000 5/4/04 2 hours Non-Critica
3000 5/4/04 2 hours Non-Critica
3000 5/4/04 2 hours Non-Critica
2000 5/5/04 2 hours Non-Critica

Here are the conditions the sort must follow

1. Critical status parts get priority regardless of due dat
2. If part non-critical, then part with earliest due date gets priorit
3. If two non-critical parts are due the same day, then the tie-breaker can be lowest part numbe
4. Only 5 hours available to work in a da
5. Cannot work the same part number twice in a single five hour da

If the code works, it should look something like thi

Part Number Due Date Labor Time Status Da
1000 5/3/04 3 hours Critical
2000 5/4/04 2 hours Non-Critical

3000 5/4/04 2 hours Non-Critical
2000 5/5/04 2 hours Non-Critical

3000 5/4/04 2 hours Non-Critical

Guru Challenge
 

Hi
don't think Excel is the right tool for this 8probably the
reason why you didn't receive an answer for your first
post).
Not sure how many parts you have but this could be quite
complicated (linear programming exercise). Maybe a project
planning tool is more suited (though you have to do a lot
of things manually).
There also could be more than one possible solution and
you have to decide which solution you want to use (or
maybe it's sufficient to get only one possible solution
and skip all the other ones)

Of course you could implement this kind of optimization
code in VBA (but this will take some time IMO)


-----Original Message-----
I've posted this problem before and have not as of yet
got any response, let's try again. Here?Ts what I got:

I have a list of part numbers with due dates and
operation times and status (see below)

Part Number Due Date Labor Time Status
1000 5/3/04 3 hours
Critical
2000 5/4/04 2 hours
Non-Critical
3000 5/4/04 2 hours
Non-Critical
3000 5/4/04 2 hours
Non-Critical
2000 5/5/04 2 hours
Non-Critical

Here are the conditions the sort must follow:

1. Critical status parts get priority regardless of
due date
2. If part non-critical, then part with earliest due
date gets priority
3. If two non-critical parts are due the same day,
then the tie-breaker can be lowest part number
4. Only 5 hours available to work in a day
5. Cannot work the same part number twice in a single
five hour day

If the code works, it should look something like this

Part Number Due Date Labor Time
Status Day
1000 5/3/04 3 hours
Critical 1
2000 5/4/04 2 hours Non-
Critical 1

3000 5/4/04 2 hours Non-
Critical 2
2000 5/5/04 2 hours Non-
Critical 2

3000 5/4/04 2 hours Non-
Critical 3

.

Guru Challenge
 

Leroy,
Your problem looks very similar to Jen's problem requested in a similar
post. I'm not sure linear programing is needed since as you state, the
lowest part number can be used (unless I am not understanding the problem
fully). Please see my reply to Jen's post titled
"Help! Extremely Difficult Sorting Problem"
Date: Monday, May 03, 2004 4:16 PM
Paul D

"Leroy" wrote in message
...
I've posted this problem before and have not as of yet got any response,
let's try again. Here's what I got:

I have a list of part numbers with due dates and operation times and
status (see below)

Part Number Due Date Labor Time Status
1000 5/3/04 3 hours Critical
2000 5/4/04 2 hours Non-Critical
3000 5/4/04 2 hours Non-Critical
3000 5/4/04 2 hours Non-Critical
2000 5/5/04 2 hours Non-Critical

Here are the conditions the sort must follow:

1. Critical status parts get priority regardless of due date
2. If part non-critical, then part with earliest due date gets priority
3. If two non-critical parts are due the same day, then the tie-breaker
can be lowest part number
4. Only 5 hours available to work in a day
5. Cannot work the same part number twice in a single five hour day

If the code works, it should look something like this

Part Number Due Date Labor Time Status Day
1000 5/3/04 3 hours Critical
1
2000 5/4/04 2 hours Non-Critical
1

3000 5/4/04 2 hours Non-Critical
2
2000 5/5/04 2 hours Non-Critical
2

3000 5/4/04 2 hours Non-Critical
3

Guru Challenge
 

Your example is not good as it already seems to be in order, but try this
anyway.

Add a helper column with this formula

=(--(D2<"Critical"))&TEXT(B2,"yyyymmdd")&A2

and copy down. Then sort on this column.

Not sure about the 5 bhours part though.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Leroy" wrote in message
...
I've posted this problem before and have not as of yet got any response,
let's try again. Here's what I got:

I have a list of part numbers with due dates and operation times and
status (see below)

Part Number Due Date Labor Time Status
1000 5/3/04 3 hours Critical
2000 5/4/04 2 hours Non-Critical
3000 5/4/04 2 hours Non-Critical
3000 5/4/04 2 hours Non-Critical
2000 5/5/04 2 hours Non-Critical

Here are the conditions the sort must follow:

1. Critical status parts get priority regardless of due date
2. If part non-critical, then part with earliest due date gets priority
3. If two non-critical parts are due the same day, then the tie-breaker
can be lowest part number
4. Only 5 hours available to work in a day
5. Cannot work the same part number twice in a single five hour day

If the code works, it should look something like this

Part Number Due Date Labor Time Status Day
1000 5/3/04 3 hours Critical
1
2000 5/4/04 2 hours Non-Critical
1

3000 5/4/04 2 hours Non-Critical
2
2000 5/5/04 2 hours Non-Critical
2

3000 5/4/04 2 hours Non-Critical
3

Guru Challenge
 

http://www.rentacoder.com/RentACoder/default.asp

--
Regards,
Tom Ogilvy

"Leroy" wrote in message
...
Paul,

That sounds like a great approach however I do need help in writing the
code....I don't know how to write VBA code, I just bought a VBA book and
trying to teach myself now so it will be awhile before I get any good.
Thanks for the reply.

Leroy

Guru Challenge
 

Leroy,
If you have a larger sample spreadsheet of what you need sorted, send it to
me and I will see what I can do.
email to
paulatlgsegdotcom
replace at with @ and dot with .
Also, make sure you write excel guru challenge in the title or I will delete
it as spam (although I am certainly NOT a guru)

Paul D

"Leroy" wrote in message
...
Paul,

That sounds like a great approach however I do need help in writing the
code....I don't know how to write VBA code, I just bought a VBA book and
trying to teach myself now so it will be awhile before I get any good.
Thanks for the reply.

Leroy

Guru Challenge
 

Code
-------------------


Private Sub CommandButton1_Click()
'' Assumption: 4 table headers in row 1; data begins A2:D2

Range("E1").Value = "Hours"
Range("F1").Value = "SortKey"

Range("E2").Formula = "=LEFT(TRIM(C2), SEARCH("" "", TRIM(C2)))"
Range("f2").Formula = "=(N(D2<""Critical""))&TEXT(B2,""yyyymmdd"")& A2"

Range("E2:F2").Select
Selection.AutoFill Destination:=Range("E2:F6"), Type:=xlFillDefault

Range("A1:F6").Select
Selection.Sort Key1:=Range("F2"), Order1:=xlAscending, Header:=xlYes, _
Orientation:=xlTopToBottom, DataOption1:=xlSortTextAsNumbers

Call Pkg(2, 6, 5, False)

Application.CutCopyMode = False

End Sub


Private Sub Pkg(toprow, botrow, noHours As Long, blnPack As Boolean)
Dim strParts As String

Range("A1:D" & botrow).Select
Selection.Copy

Range("H1").Select
ActiveSheet.Paste

recs = botrow - toprow + 1

currow = toprow
curdest = toprow
totTime = 0

While recs 0

While currow <= botrow And Not blnstop
If Range("H" & currow).Value < "" Then
If totTime + CInt(Range("E" & currow).Value) <= noHours And (InStr(strParts, ":" & Range("A" & currow).Value & ":") = 0) Then
strParts = strParts & ":" & Range("H" & currow).Value & ":"
totTime = totTime + CInt(Range("E" & currow).Value)
With Range("H11")
.Cells(curdest - 1, 1).Value = Range("A" & currow).Value: Range("H2").Cells(currow - 1, 1).Value = ""
.Cells(curdest - 1, 2).Value = Range("B" & currow).Value: Range("H2").Cells(currow - 1, 2).Value = ""
.Cells(curdest - 1, 3).Value = Range("C" & currow).Value: Range("H2").Cells(currow - 1, 3).Value = ""
.Cells(curdest - 1, 4).Value = Range("D" & currow).Value: Range("H2").Cells(currow - 1, 4).Value = ""
End With
curdest = curdest + 1: recs = recs - 1
Else
If skipt = 0 Then skipt = currow
End If
End If
currow = currow + 1
If currow botrow Or (totTime + CInt(Range("E" & currow).Value) noHours And Not blnPack) Then blnstop = True
Wend
curdest = curdest + 1
totTime = 0: strParts = ""
If skipt 0 Then
currow = skipt
skipt = 0
End If
blnstop = False
Wend

End Sub


-------------------



~× wrote:
*I've posted this problem before and have not as of yet got an
response, let's try again. Heres what I got:

I have a list of part numbers with due dates and operation times an
status (see below)

Part Number Due Date Labor Time Status
1000 5/3/04 3 hours Critical
2000 5/4/04 2 hours Non-Critical
3000 5/4/04 2 hours Non-Critical
3000 5/4/04 2 hours Non-Critical
2000 5/5/04 2 hours Non-Critical

Here are the conditions the sort must follow:

1. Critical status parts get priority regardless of due date
2. If part non-critical, then part with earliest due date get
priority
3. If two non-critical parts are due the same day, then th
tie-breaker can be lowest part number
4. Only 5 hours available to work in a day
5. Cannot work the same part number twice in a single five hour day

If the code works, it should look something like this

Part Number Due Date Labor Time Status
Day
1000 5/3/04 3 hours Critical
1
2000 5/4/04 2 hours Non-Critical
1

3000 5/4/04 2 hours Non-Critical
2
2000 5/5/04 2 hours Non-Critical
2

3000 5/4/04 2 hours Non-Critical
3

--
Message posted from http://www.ExcelForum.com