Simulation HELP!

[eliozo]

Hello,
Thank you for helping me on the below:
I have to produce 6 items for example (item 1 to item 6). I have 3 lines of production (line 1,2,3) that can produce these 6 items. I have a table that shows me the time needed for every item to be produced on these lines. How can I do a simulation that gives me a result of which item with which other item should I produce on each line so that all 3 lines are equally functional with the same amount of time.
In the example below, ill explain better:
Thank you,

	Time to produce in minutes
Item 1​	10
Item 2​	20
Item 3​	30
Item 4​	20
Item 5​	25
Item 6​	15
Simulation result: (what I want from Excel to give me)​
Line 1​	Item 1 + Item 3	40 minutes​
Line 2​	Item 2 + Item 4	40 minutes​
Line 3​	Item 5 + Item 6	40 minutes​

 

[eliozo]

12 items and 3 options works out to just over half a million, easily in range of a brute force approach. But if you can combine and/or split items, that changes everything. If you have a list of 25 items, we could just add up the total amount of minutes and divide by 3 to get the number of minutes per line. Then just assign items to line 1 until it meets or exceeds that number. If it exceeds that number, split that item into 2 sub-items, give part to line 1, part to line 2. Then assign everything to line 2 until you meet or exceed that number again, and split that item if needed, and assign the rest to line 3. I could probably do that with formulas.

Ok yes that could be an option if it wont work out with a macro. Will be waiting for your result then !! Thank you !!

 

[Eric W]

Give this a shot:

Book1
	A	B	C	D	E	F	G	H	I
1	Item	Time	Line	Time	Split to:	Time		Line	Time
2	1	12	1	12				1	160
3	2	42	1	42				2	160
4	3	40	1	40				3	161
5	4	22	1	22
6	5	16	1	16				Total time	481
7	6	34	1	28	2	6		Time per line	160
8	7	47	2	47
9	8	47	2	47
10	9	25	2	25
11	10	19	2	19
12	11	20	2	16	3	4
13	12	55	3	55
14	13	20	3	20
15	14	37	3	37
16	15	10	3	10
17	16	35	3	34	3	1
18
19
Sheet3

Cell Formulas
Range	Formula
C2:C19	C2	=IF(A2="","",MATCH(SUM(D$2:D2),CHOOSE({1,2,3},0,$I$7+0.001,2*$I$7+0.001)))
D2:D19	D2	=IF(A2="","",MIN(B2,CEILING(SUM(B$1:B1)+0.001,$I$7)-SUM(B$1:B1)))
E2:E19	E2	=IF(D2<B2,MIN(C2+1,3),"")
F2:F19	F2	=IF(D2<B2,B2-D2,"")
I2:I4	I2	=SUMIF(C:C,H2,D:D)+SUMIF(E:E,H2,F:F)
I6	I6	=SUM(B2:B100)
I7	I7	=INT(I6/3)

Put the C2:F2 formulas in and drag down as far as needed. Then put in the H:I formulas. As you can see, the I7 formula shows how many minutes each line should get, with a possible difference of 1 or 2 minutes, which will go to line 3. Put in your items and times in columns A:B. Column C starts out at line 1, echoing the time from column B to column D, until the cumulative time exceeds the value in I7. When it does (row 7), it tells you to split item 6 into 2 parts, one part of 28 minutes that goes to line 1, and one part of 6 minutes that goes to line 2. The same thing happens on row 12. On row 17 it says to split item 17 in 2 parts, but both parts are in line 3. That's just the excess minute. You really don't need to split anything, it just made the formulas easier. Let me know how this works.

 

[eliozo]

=IF(A2="","",MATCH(SUM(D$2:D2),CHOOSE({1,2,3},0,$I$7+0.001,2*$I$7+0.001))

Hi Eric, I am working now on the file and see if it's gonna work. I am facing problems with this formula. When clicking enter it gives me there is an error and it seems where you wrote 1,2,3. Can you help please? thanks

 

[eliozo]

It worked now, i have to put ; between 1 2 and 3 in the choose function. Will give you update on the file. Thanks