I've laid out the data as follows on sheet 1.

Col A - product A B C A B C
Col B - time to produce in days (0,5, 0,4, 0,3)
Col C - delivery date
Col D - calculation for eithers todays date + prod time for first item, or
date finished last product + time to produce next one
Cell F1 - to days date

When I run the macro Iìm getting a complile error as a whole list of things
are not allowed as public memebers of object modules.

"joel" wrote:

It would be a recursive algorithm. I have a bunch of them that I mdofiy as
required. You would need to re-run the program as new order are placed like
every night or once a week becaue plans always change.

Here is the recursive code that will put the combination on sheet 1 so you
can see the results. Put a small number in becasue the combinations are
large and may exceed the number of rows you can have in a worksheet. You
willnot be saving all the combinations so the size of the worksheet is not a
factor.

Public Orders()
Public combo
Public RowCount As Long
Sub permuations()

NumberofOrders = InputBox("Enter Number of orders")
ReDim Orders(NumberofOrders)
For i = 1 To NumberofOrders
Orders(i) = i
Next i

ReDim combo(NumberofOrders)
Level = 1
RowCount = 1

Call recursive(Level)
End Sub
Sub recursive(ByVal Level As Integer)

Length = UBound(Orders) + 1

For i = 0 To (Length - 1)

'for combinations check if item already entered
found = False
For j = 0 To (Level - 2)
'combo is a count of the combinations,not the actual data
'123
'132
'213
'231
'312
'321
'data is actually in InStrings
If combo(j) = i Then
found = True
Exit For
End If
Next j

If found = False Then

combo(Level - 1) = i

If Level = Length Then
ComboString = ""
For j = 0 To (Length - 1)
ComboString = ComboString & Orders(combo(j))
Next j
Sheets("Sheet1").Range("A" & RowCount) = ComboString
RowCount = RowCount + 1
Else
Call recursive(Level + 1)
End If
End If
Next i
End Sub


"LiAD" wrote:

So from one input table I would need to create every possible output
combination then select the one thats best. Seems workable.

What function or type of macro is best to use that would to generate all the
tables automatically?

Thanks

"joel" wrote:

I don't have any idea on the quantities or time to make each item so it is
hard to get to a correct solution. A JIT approach would require finding the
latest date to start an order and the time required to make the item.

to try evvery combination would requir e chaing the which orderr you build
first, then 2nd and thrid going though every combination or build sequencies.
if you had 10 orders then try each combination

Below is a table of the orders and the sequence built. the 1st row is
building the order in sequence. The 2nd row is the same except building
order 10 before order 9.
1, 2, 3, 4, 5, 6, 7, 8, 9, 10
1, 2, 3, 4, 5, 6, 7, 8, 10, 9
1, 2, 3, 4, 5, 6, 7, 9, 8, 10
1, 2, 3, 4, 5, 6, 7, 9, 10, 8
1, 2, 3, 4, 5, 6, 7, 10, 8, 9
1, 2, 3, 4, 5, 6, 7, 10, 9, 8
1, 2, 3, 4, 5, 6, 8, 7, 9, 10
1, 2, 3, 4, 5, 6, 8, 7, 10, 9
and so on

the solution would be to try every combination and determine how many orders
were late, the number of times you had to retool, and the number of days you
shipped before the due date.

"LiAD" wrote:

To take ur points one at a time. Assume that

- inventory is zero it all needs to be manufactured.
- manufacturing can start at today()
- prod is 24 hours mon-fri
- in reality the product range is massive so its more or less custom orders.
reoccuring orders will be dealt with another way. This tool does not need
to take care of those orders.
- Rather than picking a number of days between retools I would rather than
excel told me when i had too. When I have to could be based on your JIT
approach, perfect. Dates will decide when products will be scheduled.
- no penalties or cost analysis needed just balance what we need to produce
against optimising machine running time.

I could start with the most urgent product. Ask excel to find all products
that are the same as the urgent product, schedule them all together, then
just before the next product will be late schedule a change and so it
continues. Nice approach and i think that would work for this case.

In terms of developing a macro to run this - at the moment thats beyond my
knowledge.

What do you mean by keep and history and the best results? Lots of
schedules are produced and you just pick the best one?

I know details are a bit vague for now, but once i see if the approach may
work then I can put some fat on the bones.

Thanks a lot for your help
LiAD

"joel" wrote:

The problem can be sove with programming but you need some additional
information to get started. First how much inventory do you have and does
your delivery dates start immediatel or do you have some lead time? Also how
many hours a day are you working and is there any penaties for working over
time?

The solution for the problem is to set up some criteria(s) whre you are
going to retool and then compare the results to determine the best solution.
The best solution would be to minimze being late and to build up as much
inventory as possible. One questtion you didn't specify is if each order is
a custom order or are there standard products. Each product could come in
100 different colors so you wouldn't be able to build up inventory, every
order is a custom order.

One soilution could be to change products every two days. Another solution
would be to continue making one product until you wold become late on one of
the other two products and switch so you are making a product "Just-In-time".

This problem seems like a modeling solution is the best approach. In
modeling your run a 1000 (or any fixed number) of attempts. Set criteria X =
number of days until you retool (make number 1 to 10) and Y when you retool
which product you change to (could be the same product. then keep a history
of the results keeping only the best results.


for i = 1 to 1000
X= int(10*rnd())+1
Y= int(3*rnd())+1 product A = 1 Product B = 2 Product C = 3

for Days = 1 to 100
'run your production line and vaidate if you met your criteria

next Days

next i

Hello,

I have a complicated problem which is dificult to explain easily I think.
I will try and lay out the basic concept. What I would like to know is how
can I achieve this in excel.

I have a list of products A B C for example which I will produce during a
set period of gg days on one single machine. The knowns are
- It takes xx hours to produce A, yy for B, zz for C
- If I change from one product to another it takes 4 hours to retool the
machine (from A to B, B to C etc for all changes)
- The production date that each product must be finished

Customers, (being difficult), would like me to deliver in the following
order A B C A C A C B. This presents two potential problems
- Time may be restricted
- If I do follow the customers orders exactly I need to spend a lot of time
changing over my machine. Time wasted that maybe I can use to find more
products or customers.

What would be nice is to have a tool in excel that can answer the question -
can I change the order in which I will manufacture the products and still
meet the customer delivery requirements? If so give me a production schedule.

So I have a table from which I know the products and dates the customer
wants. From the production and tool change times required I can calculate
when it will be possible to make each product. Then I would like to find
some way to get excel to produce a production schedule by optimising the
manufacturing order to reduce the amount of tool changes, (and therefore
total time spent on them), ensuring that all of my orders respect the
customer dates.

The function/method needs to group similar products together providing
- manufacture is finished on or before the customer date
- the resort process does not make any other products late (as in if the
customer asks to have A B A but I re-organise to A A B maybe the A's are
great but I have made the B late).

An important point to consider is that I have only one machine therefore it
is one product after another so the time that product 2 is finsihed is time
of day the line was started + manufacture time for product 1 + tool change
time + time to make product 2.

The function, (in my opinion), needs to iterative as basically it will
re-organise the manufacturing order, check that all orders will be finished
within date, if not re-organise and recheck etc etc.

Is this possible and if so how in terms of function design etc?

Thanks a lot. I hope its clear enough, if not please ask. I'll well stuck
in tables and tables and tables.

LiAD