What you need is Solver. You may have to install it, if it is not running
now. Tools Solver. If it is not there, Tools Add-ins Solver Add-In
OK. Then look at this:
http://www.solver.com/stepbystep2.htm

I don't have time to figure out the logic, and you know it better than me
anyway, so just use that as a guideline. Start simple, with one or two
criteria, and you will have your result in no time at all.

Good luck,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"LiAD" wrote:

Hi,

I have a complicated list to sort which will be quite difficult to explain.
I do not know how to approach it so I will give a simplified version.

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

So for example if we group all the A's and B's the first batch of product C
will not be delivered on time however with the list shown we could do A B C C
without affecting things.

What suggestions does anyone have for how best to approach this? Seems like
an iterative approach is needed, try, check dates, retry etc. Maybe not
though, maybe some rules could be set that index formulas can run off.

Anyone got any ideas