(1) Explain your problem, don't simply post "This isn't working". What were you doing when you faced the problem? What have you tried to resolve - did you look for a solution using "Search" ? Has it happened just once or several times?
(2) It's also good to get feedback when a solution is found, return to the original post to explain how it was resolved so that more people can also use the results.
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
Printer-friendly version
Send to friend
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
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
Email: PPadmin@planningplanet.com
Replies