Thanks a lot for your detailed answer.

Do you think this type of problem can be handled in another way in excel?
What approach would you take in order to produce the most efficient
production schedule that respects the delivery times?

Thanks

"Harlan Grove" wrote:

ryguy7272 wrote...
What you need is Solver. . . .
....
"LiAD" wrote:
....
Imagine I have a list of something to make in a factory, products
A,B,C and D. I know the date the customer wants to have it and how
long it takes to manufacture, therefore what date i should start.
I want to give the workshop a list of what products to make and
when. To make it easy I would like to make all the A's, B's etc at
the same time, PROVIDING they are within the date and re-arranging
the order will not cause any other orders to be late.

Imagine I have 3 days between the 1 and 4 Mar to make the following
list (times to produce beside).

Time to make Date due
A 10 hours 1 Mar
B 20 hours 2 Mar
C 3 hours 2 Mar
A 10 hours 3 Mar
C 3 hours 4 Mar
A 10 hours 4 Mar

In between each item takes 2 hours to change the machine from A to
B, B to C etc. so if I regroup I gain time.

Suppose today is the 30 Apr . . .

Then the due dates in the table above are March dates in the
subsequent year, so you have 11 months to manufacture these parts? Or
should the dates in the table be in May? I'll assume so.

. . . and I have to decide what to make when for the next four
days. My machine must start with product A.

I would like excel to minimise the number of times the machine
changes from one product to another, keeping all the products
within the dates requested.
....

You don't mention times in the table above, so are the due dates all
as of 1 second after midnight on the given dates? I'll assume so along
with assuming the machine can be operated indefinitely and that you
have a full 24 hour work day.

That so, this problem could be solved by inspection. You need 10 hours
of A in 24 hours, 20 hours of B and 3 hours of C in 48 hours, another
10 hours of A in 72 hours, another 3 hours of C and another 10 hours
of A in 96 hours.

The most obvious efficiency is running a single 6 hour production run
for C ending at 03:00 on 2 May, which means you'd have 3 hours of C at
00:00 on 2 May. So you'd need to begin C production at 21:00 on 1 May,
so end B's production run at 19:00 on 1 May, so begin product B's
production run at 23:00 on 30 April, so end product A's first
production run at 21:00 on 30 April, so you'd have 21 hours of product
A production at 00:00 on 1 May.

Anyway, the most efficient production run would be

30 Apr 00:00 Begin Product A production run
30 Apr 21:00 End Product A production run and retool for product B
30 Apr 23:00 Begin Product B production run
1 May 19:00 End Product B production run and retool for product C
1 May 21:00 Begin Product C production run
2 May 03:00 End Product C production run and retool for product A
2 May 05:00 Begin Product A production run
2 May 14:00 End Product A production run

30 hours total product A in 2 separate 21 hour and 9 hour runs
20 hours product B in a single 20 hour run
6 hours product C in a single 6 hour run
6 hours total retooling time

I need some way for excel to group similar products, check the
final production time verses the delivery time, if its within date
re-arrange if not take the next most urgent product etc etc.
....

I don't think Solver can handle this. The constraint is that you need
sufficient inventory of given products at order due dates/times. That
means running Solver to generate a production run table with
constraints derived from an inventory table that reflects additions
from production and reductions from filling orders.

If you use COUNTIF or SUMPRODUCT functions in cell formulas to count
the number of products with negative units in inventory and set Solver
constraints that those cells must equal 0, Solver will fail because
such formulas are too nonlinear. OTOH, unless you need 20 or fewer
separate production runs, Solver can't handle individual constraints
for each product at each order date.

This is more of a database problem than a spreadsheet one. This really
requires tracking inventory given additions to inventory from
production and reductions to inventory from filling orders, so ideally
involving a production run table (what you need to generate), an order
table (the given), an inventory transaction table (additions from
production and reductions from orders), and an inventory table (stock
on hand, essentially accumulating the inventory transactions at every
point in time).

The constraint is that you should always have nonnegative (= 0)
inventory in all products at all times. The objective is minimizing
the ending date/time of the last production run.

The initial production run's start time would be a given. Easiest just
to cycle through products A, B, C, A, B, C, etc. Then start times for
subsequent runs would be 2 hours after the prior run's end time *IF*
the prior run's end time was later than its start time, or just the
previous run's start time. That is, products can have zero run times.
This way production run end times are the only variables.

That said, I still don't believe Solver can handle realistic size
problems of this kind.