This is a linear programming optimization problem that can be tackled if using Excel Solver. Simplified algorithms that will not attempt to minimize total waste in occasions might lead to feasible solutions but not necessarily close to optimal.
Member for
13 years 2 months
Member for13 years2 months
Submitted by bogdanleonte on Wed, 2018-01-17 22:58
If you are familliar with excel VBA (Visual Basic For Application) you could create a macro which will do this.
The macro will arrange contractor hours and task durations Descending and it will assign the contractor with most free hours to the activities with the longest duration, after this the macro will subtract the assigned hours from the available contractor hours. When the first contractor will no longer be able to perform any activity then it will move to the next contractor and repeat, until all tasks have contractors.
This is not very easy but with a little work it can be done.
You could also specify that a contractor cannot be assigned to more than a number of tasks, if you wish to use more contractors.
Hope this helps.
Best regards,
Bogdan
Member for
13 years 2 months
Member for13 years2 months
Submitted by bogdanleonte on Wed, 2018-01-17 22:58
If you are familliar with excel VBA (Visual Basic For Application) you could create a macro which will do this.
The macro will arrange contractor hours and task durations Descending and it will assign the contractor with most free hours to the activities with the longest duration, after this the macro will subtract the assigned hours from the available contractor hours. When the first contractor will no longer be able to perform any activity then it will move to the next contractor and repeat, until all tasks have contractors.
This is not very easy but with a little work it can be done.
You could also specify that a contractor cannot be assigned to more than a number of tasks, if you wish to use more contractors.
Member for
21 years 8 monthsPlease download the Excel
Please download the Excel file so you can take a look at the constraints.
Distinct_Resource_Allocation.xlsx
Latter we might explore methods to make the model easier to modify in case we want to add activities and contractors.
Member for
12 years 11 monthsHi Rafael, I have tried your
Hi Rafael,
I have tried your method, but it says that there are too many variables. (Looking at a list of 30 contractors and 16 to 17 tasks).
Kindly advise if these settings are the same as what you have. shown in your response.
Regards,
Benjamin
[[wysiwyg_imageupload:5352:]]
Member for
21 years 8 monthsThis is a linear programming
This is a linear programming optimization problem that can be tackled if using Excel Solver. Simplified algorithms that will not attempt to minimize total waste in occasions might lead to feasible solutions but not necessarily close to optimal.
Member for
13 years 2 monthsHello Benjamin,If you are
Hello Benjamin,
If you are familliar with excel VBA (Visual Basic For Application) you could create a macro which will do this.
The macro will arrange contractor hours and task durations Descending and it will assign the contractor with most free hours to the activities with the longest duration, after this the macro will subtract the assigned hours from the available contractor hours. When the first contractor will no longer be able to perform any activity then it will move to the next contractor and repeat, until all tasks have contractors.
This is not very easy but with a little work it can be done.
You could also specify that a contractor cannot be assigned to more than a number of tasks, if you wish to use more contractors.
Hope this helps.
Best regards,
Bogdan
Member for
13 years 2 monthsHello Benjamin,If you are
Hello Benjamin,
If you are familliar with excel VBA (Visual Basic For Application) you could create a macro which will do this.
The macro will arrange contractor hours and task durations Descending and it will assign the contractor with most free hours to the activities with the longest duration, after this the macro will subtract the assigned hours from the available contractor hours. When the first contractor will no longer be able to perform any activity then it will move to the next contractor and repeat, until all tasks have contractors.
This is not very easy but with a little work it can be done.
You could also specify that a contractor cannot be assigned to more than a number of tasks, if you wish to use more contractors.
Hope this helps.
Best regards,
Bogdan