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​

 

[Eric W]

You can do this with Solver. Set up your sheet like this:

Book1
	A	B	C	D	E	F	G	H
1	Items	Time to produce	Line		Line	Total Time		Variance
2	Item 1	10			1	0		0
3	Item 2	20			2	0
4	Item 3	30			3	0
5	Item 4	20
6	Item 5	25
7	Item 6	15
Sheet4

Cell Formulas
Range	Formula
H2	H2	=MAX(F2:F4)-MIN(F2:F4)
F2:F4	F2	=SUMIF($C$2:$C$7,E2,$B$2:$B$7)

If you don't have Solver installed, click File > Options > Add-ins > On the bottom select Excel Add-ins and click Go ... > Check the Solver Add-in box > OK.

Then go to the Data tab, click Solver, and set up the parameters as follows:

Click Solve, wait a few minutes, and you'll get this:

Book1
	A	B	C	D	E	F	G	H
1	Items	Time to produce	Line		Line	Total Time		Variance
2	Item 1	10	1		1	40		0
3	Item 2	20	3		2	40
4	Item 3	30	1		3	40
5	Item 4	20	3
6	Item 5	25	2
7	Item 6	15	2
Sheet4

Cell Formulas
Range	Formula
H2	H2	=MAX(F2:F4)-MIN(F2:F4)
F2:F4	F2	=SUMIF($C$2:$C$7,E2,$B$2:$B$7)

It's actually possible to write a macro that'll work faster. In this case, there are only 729 possibilities to check, but Solver does several thousand cases. But this should work fine. Let us know how it works for you.

 

[eliozo]

Thank you so much for your help! I will try it.
But since i have to do it on a daily basis because it’s a production plan i am implementing, i prefer if it was a macro with a button and it shows me exactly the same result i dont mind. Thank youu again !!

 

[bebo021999]

I'd offer a UDF (UserDefineFunction)
If items combination in F2, in G2 use:
=timeneed(F2)

Alt-F11 to open VBA code editor, insert/new module, then paste below code onto edit window

Option Explicit
Function timeneed(ByVal item As Range) As Long
Dim i&, iList As Range, iTime As Range, s
Set iList = Range("A2:A7") ' adjust to actual range that contains Items list
Set iTime = Range("B2:B7") ' adjust to actual range that contains Time
s = Split(item, "+") ' split string , splited by "+", to be multi single items
    For i = 0 To UBound(s) ' loop through each item found in string
        With WorksheetFunction
            If .CountIf(iList, Trim(s(i))) Then timeneed = timeneed + .Index(iTime, .Match(Trim(s(i)), iList, 0))
        End With
    Next
End Function

 

[eliozo]

I'd offer a UDF (UserDefineFunction)
If items combination in F2, in G2 use:
=timeneed(F2)

Alt-F11 to open VBA code editor, insert/new module, then paste below code onto edit window

Option Explicit
Function timeneed(ByVal item As Range) As Long
Dim i&, iList As Range, iTime As Range, s
Set iList = Range("A2:A7") ' adjust to actual range that contains Items list
Set iTime = Range("B2:B7") ' adjust to actual range that contains Time
s = Split(item, "+") ' split string , splited by "+", to be multi single items
    For i = 0 To UBound(s) ' loop through each item found in string
        With WorksheetFunction
            If .CountIf(iList, Trim(s(i))) Then timeneed = timeneed + .Index(iTime, .Match(Trim(s(i)), iList, 0))
        End With
    Next
End Function

Thank you! how can I do it with a button?

 

[Eric W]

Do you always have 3 production lines? What's the maximum number of items that you would produce in a day?

 

[eliozo]

Do you always have 3 production lines? What's the maximum number of items that you would produce in a day?

yes always 3 production lines. maximum number of item to produce in a day is 24 or 25

 

[Eric W]

Hmm, that makes it tricky. There are algorithms that are designed to do this kind of load-balancing, but I don't know them offhand, or have the time to research them. My original idea was to just do a brute force search, which is fine for small groups. But with 25 items and 3 possibilities for each item, that's nearly a trillion combinations, too many to search exhaustively, it would take too long.

About the best I can offer at the moment is a "greedy" algorithm. The way this works is that it sorts all the times, longest to shortest. Then it creates 3 buckets, one for each line. Then it goes down the list of times, and adds the time to whichever bucket has the lowest current total. This will probably not give you the "best" solution, unless you're very lucky, but it should come close. A lot depends on the range of times you have.

Let me know if you want the code for this, and if so, what version of Excel do you have?

 

[eliozo]

Hmm, that makes it tricky. There are algorithms that are designed to do this kind of load-balancing, but I don't know them offhand, or have the time to research them. My original idea was to just do a brute force search, which is fine for small groups. But with 25 items and 3 possibilities for each item, that's nearly a trillion combinations, too many to search exhaustively, it would take too long.

About the best I can offer at the moment is a "greedy" algorithm. The way this works is that it sorts all the times, longest to shortest. Then it creates 3 buckets, one for each line. Then it goes down the list of times, and adds the time to whichever bucket has the lowest current total. This will probably not give you the "best" solution, unless you're very lucky, but it should come close. A lot depends on the range of times you have.

Let me know if you want the code for this, and if so, what version of Excel do you have?

I am capable of assembling the items into 11-12 items. Is it possible from your side? But let's say Item1 needs 120minutes, could it be divided let's say 100 minutes in one line and 20 minutes in another so that the total be the same in the 3 lines I don't mind that if it's possible.
Thank you so much for both of you and for whoever can help !!!

 

[Eric W]

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.