How to use solver for this case?

[1234_a]

The exercise is:
There are 5 employees in your small business who can perform any of the 5 different tasks that your company has. The following table shows how many hours of working time are required for each employee to perform each obligation.
Your job is to split these responsibilities so that each of the employees carries out exactly one obligation and each obligation is fulfilled exactly once and at the same time the total time in hours to complete all the work is the least possible.

	exercise 1	exercise 2	exercise 3	exercise 4	exercise 5
1.employee	8​	12​	21​	9​	10​
2. employee	5​	18​	19​	10​	12​
3.employee	9​	17​	18​	9​	13​
4. employee	7​	15​	17​	11​	9​
5.employee	6​	19​	21​	8​	12​

 

[i_nth]

I suggest you look at some of the examples at Optimization Solutions - Human Resources and Scheduling Examples to learn how to formulate this type of model. The "Offices" model is quite similar to your requirement.

 

[CA_Punit]

I think it is an Assignment Probelm. Will get back with the probelm after understanding how assignment probelms can be solved

 

[1234_a]

I think it is an Assignment Probelm. Will get back with the probelm after understanding how assignment probelms can be solved

Thank you. I will wait for your response!

 

[1234_a]

I suggest you look at some of the examples at Optimization Solutions - Human Resources and Scheduling Examples to learn how to formulate this type of model. The "Offices" model is quite similar to your requirement.

Maybe it is, but I don't understand it. Could you please help me with this problem?

 

[CA_Punit]

I have gone through similar kind of Assignment Probelm but i am not able to devise a formula to calculate the optimum result. Hopefully some one with VBA knowledge could help

 

[Eric W]

When you say this is an exercise, is this for a class, or seminar or something? Generally speaking, a lot of the helpers here don't like to give answers to class questions since the point is to learn, and if we just give an answer, that defeats the purpose. We are willing to give hints and pointers though. And when you say "Solver", do you mean that this is the tool that the exercise requires?

Consider this layout:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1		exercise 1	exercise 2	exercise 3	exercise 4	exercise 5		Task		All used?		Hours needed
2	1.employee	8	12	21	9	10		1		1		8
3	2. employee	5	18	19	10	12		2				18
4	3.employee	9	17	18	9	13		3				18
5	4. employee	7	15	17	11	9		4				11
6	5.employee	6	19	21	8	12		5				12
7
8											Sum:	67
Sheet4

Cell Formulas
Range	Formula
J2	J2	=PRODUCT(--(COUNTIF(H2:H6,{1,2,3,4,5})=1))
L2:L6	L2	=INDEX(B2:F2,H2)
L8	L8	=SUM(L2:L6)

Named Ranges
Name	Refers To	Cells
solver_adj	=Sheet4!$H$2:$H$6	J2, L2
solver_lhs1	=Sheet4!$H$2:$H$6	J2, L2
solver_lhs2	=Sheet4!$H$2:$H$6	J2, L2
solver_lhs3	=Sheet4!$H$2:$H$6	J2, L2

A1:F6 is your table. H2:H6 is a column saying which task is assigned to which employee. They should be a number from 1 to 5, and each should be used exactly once. The formula in J2 tells you if every task is used exactly once. Then the L2:L6 column is a simple lookup of the number of hours for that task, and L8 is the total hours. This now has everything you need to set up a Solver problem. Minimize cell L8, varying cells H2:H6 subject to the constraints that H2:H6 must be 1-5, and J2 must be 1. You'll also probably need to select the Evolutionary solving method. When you get it all set up, it should run through several thousand cases (3125 probably), and come up with an answer in the H2:H6 column. The L8 total should be a bit less that what you see here.

This should answer your question, but there are still some flaws in it. For example, there are 5! = 120 possible ways to assign the tasks. Seems like there should be a way to check only those ways, and not all 3125 ways that the Solver checks. Also, you are dependent on the Solver method. Are you sure that it really gives you the best answer? After all, 2 of the 3 methods don't work. If you know VBA, it'd be easy to write a macro that exhaustively checks all 120 ways and gets the best answer. But what if you have 100 people and 200 tasks? An exhaustive macro could take years to run. There are algorithms that work in much faster ways.

Anyway, here are some ideas, and some things to think about. Good luck!

 

[1234_a]

When you say this is an exercise, is this for a class, or seminar or something? Generally speaking, a lot of the helpers here don't like to give answers to class questions since the point is to learn, and if we just give an answer, that defeats the purpose. We are willing to give hints and pointers though. And when you say "Solver", do you mean that this is the tool that the exercise requires?

Consider this layout:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1		exercise 1	exercise 2	exercise 3	exercise 4	exercise 5		Task		All used?		Hours needed
2	1.employee	8	12	21	9	10		1		1		8
3	2. employee	5	18	19	10	12		2				18
4	3.employee	9	17	18	9	13		3				18
5	4. employee	7	15	17	11	9		4				11
6	5.employee	6	19	21	8	12		5				12
7
8											Sum:	67
Sheet4

Cell Formulas
Range	Formula
J2	J2	=PRODUCT(--(COUNTIF(H2:H6,{1,2,3,4,5})=1))
L2:L6	L2	=INDEX(B2:F2,H2)
L8	L8	=SUM(L2:L6)

Named Ranges
Name	Refers To	Cells
solver_adj	=Sheet4!$H$2:$H$6	J2, L2
solver_lhs1	=Sheet4!$H$2:$H$6	J2, L2
solver_lhs2	=Sheet4!$H$2:$H$6	J2, L2
solver_lhs3	=Sheet4!$H$2:$H$6	J2, L2

A1:F6 is your table. H2:H6 is a column saying which task is assigned to which employee. They should be a number from 1 to 5, and each should be used exactly once. The formula in J2 tells you if every task is used exactly once. Then the L2:L6 column is a simple lookup of the number of hours for that task, and L8 is the total hours. This now has everything you need to set up a Solver problem. Minimize cell L8, varying cells H2:H6 subject to the constraints that H2:H6 must be 1-5, and J2 must be 1. You'll also probably need to select the Evolutionary solving method. When you get it all set up, it should run through several thousand cases (3125 probably), and come up with an answer in the H2:H6 column. The L8 total should be a bit less that what you see here.

This should answer your question, but there are still some flaws in it. For example, there are 5! = 120 possible ways to assign the tasks. Seems like there should be a way to check only those ways, and not all 3125 ways that the Solver checks. Also, you are dependent on the Solver method. Are you sure that it really gives you the best answer? After all, 2 of the 3 methods don't work. If you know VBA, it'd be easy to write a macro that exhaustively checks all 120 ways and gets the best answer. But what if you have 100 people and 200 tasks? An exhaustive macro could take years to run. There are algorithms that work in much faster ways.

Anyway, here are some ideas, and some things to think about. Good luck!

Thank you! After your explanation I finally understood what I need to do.