Hello,
I'm not exactly sure how to describe this exactly... I have several criteria. Right now the process is automated as much as I know how to, but I'd like to automate it even further to where I just have to plug in the data (in columns B-F->row 23 & column I->rows 17-19), and it automatically assign each weighted task evenly among the employees with either equations, or the click of a button using VBA.
-Multiple employees with changing task loads per day
- Each task has different weights
- The count in each weighted task is used up as it's distributed to each employee
- The goal is to distribute the weighted values to each employee evenly
- The cells highlighted in green in the attached spreadsheet are the primary focus of my question.
- The cells colored in grey don't need to be focused on for this question.
In more specific detail:
1. in column I, rows 17-19, Task 1, Task 2 , and Task 3 task categories are the total number (manually inputted) of each task to be distributed evenly to the employees listed in Column A.
2. The task categories listed in Column H, rows 17-19, are weighted because each one takes longer then the next:
--> Task 1= 4
--> Task 2= 3
--> Task 3= 2
3. The "Starting task availability (i.e. bandwidth)" in column K shows the value of what each employee can handle for that day (highest number means that employee can handle the most tasks for that day). The "Employee's bandwidth deficit/surplus" in column R, rows 6-10, shows the leftover bandwidth of each employee as tasks are assigned.
---> For example, When Task 1 is assigned to Employee AA with a "Starting task availability (i.e. bandwidth)" of 11, their load value decreases down to 7, making employee BB the next candidate for another Task 1, and so on, until all the Task 1's are used up. Once Task 1 is used up, the desired mix of equations/VBA functions will start assigning all the Task 2's to the employees with the highest task availability value.
4. Task 1 should be assigned (i.e. used up as it's distributed to each employee) first, then Task 2 next, then Task 3.
5. As each task is assigned, it's task value decreases by 1 in Column J, rows 17-19.
6. In the event that there is a tie, the employee that wasn't assigned a task most recently will get the task assigned to them.
7. Once all the tasks have been assigned, the goal is to have the "Employee's bandwidth deficit/surplus" column as close to 0 as possible for each employee, indicating the task loads have been distributed evenly.
Goal:
To use equations in columns M-O, rows 6-10, to properly distribute each weighted task, as mentioned above.
Or
Use VBA to create a button that achieves the logic mentioned above in one click.
**The version of Excel that'll be using this spreadsheet is 2007.**
It's a pretty small spreadsheet, but I'm at a loss as to how to proceed.
Thank you for any help on this!
Bookmarks