Help! Extremely Difficult Sorting Problem

[Guest]

I have a huge spreadsheet containing part numbers, work order numbers, labor time, and due dates. I know you cannot solve this under regular Excel channels. I think VB code will have to be added, unfortunately, I can't write code. I really need help in that department. Here are the constraints

1. Cannot exceed certain amount of labor hours in one day (For this example let's use 10 hours) Would like to be able to choose labor hour amount to try different scenarios
2. Cannot repeat the same part number within that block of 10 hour
3. Work must be sorted by due date within 10 hour block (that's the easy part

Look at the example below

Part Number W.O. Labor Time (Hours) Due Dat
A 100 2 1/1/0
A 101 2 1/1/0
B 200 3 1/2/0
B 201 3 1/2/0
C 300 4 1/1/0

Sorted Report Should Like the following

Part Number W.O. Labor Time (Hours) Due Dat
A 100 2 1/1/0
C 300 4 1/1/0
B 200 3 1/2/0
Page Break or Empty Ro

Part Number W.O. Labor Time (Hours) Due Dat
A 101 2 1/1/0
B 201 3 1/2/04

Anyone that can solve this is a master in my book.....thanks

 

[Paul D]

I have a huge spreadsheet containing part numbers, work order numbers,

labor time, and due dates. I know you cannot solve this under regular Excel
channels. I think VB code will have to be added, unfortunately, I can't
write code. I really need help in that department. Here are the
constraints:

1. Cannot exceed certain amount of labor hours in one day (For this

example let's use 10 hours) Would like to be able to choose labor hour
amount to try different scenarios.

Do you want to optimize the time to come as close to 10 as possible or just
keep selecting the next part available (not repeating a part) based on the
due dates until the allowed labor hours is reached. I'm going to assume we
can just select the next available

2. Cannot repeat the same part number within that block of 10 hours

not a problem, just keep stepping through list until new part is reached

3. Work must be sorted by due date within 10 hour block (that's the easy

part)

I would do this first, then break the list up by parts and labor time

Look at the example below:

Part Number W.O. Labor Time (Hours) Due Date
A 100 2 1/1/04
A 101 2 1/1/04
B 200 3 1/2/04
B 201 3 1/2/04
C 300 4 1/1/04

Sorted Report Should Like the following:

Part Number W.O. Labor Time (Hours) Due Date
A 100 2 1/1/04
C 300 4 1/1/04
B 200 3 1/2/04
Page Break or Empty Row

Part Number W.O. Labor Time (Hours) Due Date
A 101 2 1/1/04
B 201 3 1/2/04

Anyone that can solve this is a master in my book.....thanks.

See if you think this approach may work.
prompt user for limit of labor hours
Create a new temporary worksheet to move the sorted data to.
Then sort the existing data by due date. If as asked per above, just
selecting in order by part number is acceptable then I would take the first
part and get its hours in memory and move it to the new worksheet, deleting
its existing row.
I would continue through the list checking for a new part number then
checking the hours to make sure it fits in the limit, if so move to new
worksheet.
After first iteration through worksheet, add blank space then iterate again.
Continue iteration until no rows left on original sheet.
delete original worksheet since its blank, and rename new one to name of
original one.
You now have a sorted worksheet

If you wanted to check multiple scenarios. First create a copy of your
worksheet (maybe name sheet sortxx where xx is the hour limit), then create
a temporary worksheet, etc. This way the original does not get deleted.

If you need help coding this, let me know.

Paul D