Hi All,
I have a list of tasks ( Task A to Task Z) each requiring different amount of hours. On the other hand I also have a list of contractors each of which is able to commit to a specific amount of hours.
Is there a excel formula that I can use so that I can meet the following criteria:
1. I need to clear Task A to Task Z in that sequence.
2. Each contractor can only be used once.
3. I want to clear the maximum number of tasks with my limited pool of contractors.
Example:
Task A - 200, Task B - 450, Task C - 800, Task D - 1250, Task E - 1802
Contractor 1 - 100, Contractor 2 - 150, Contractor 3 - 150, Contractor 4 - 150, Contractor 5 - 155, Contractor 6 - 225
Expected Results:
Task A - Contractor 6 (Wastage 25)
Task B - Contractor 2, 3, 4 (Wastage 0)
Task C - Add more contractors.
Whilst the tasks and the hours are repetitive, the contractor hours change weekly, hence I am looking to automate the process of finding the best fit.
I was wondering if there was an easier way to do this other than manual trial and error.
Regards,
Benjamin
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.
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:]]
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.
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
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