Guru Challenge

[Guest]

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 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

 

[Frank Kabel]

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

 

[Paul D]

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

I've posted this problem before and have not as of yet got any response,

let's try again. Here's what I got:

 

[Bob Phillips]

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)

I've posted this problem before and have not as of yet got any response,

let's try again. Here's what I got:

 

[Tom Ogilvy]

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

--
Regards,
Tom Ogilvy

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.

 

[Paul D]

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

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.

 

[fendwick]

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


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

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" & 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