Automate the ability to schedule a task for multiple people using different parameters

OK, so I have a very large project that I do bi-annually and it is extremely time consuming and very tedious. I have been exploring options to automate it further then I already have. Here is the general idea of what I am doing on a small scale. If I can get some assistance small scale, it should give me some guidance to create loops or replication (whatever might be needed) as this involves well over 1000 people. I also realize this is very difficult and time consuming so thank you in advance to whomever offers their assistance.

So we have an on-call rotation and our staff is required to participate 10 weeks a year. We have several different Tiers (order in which they are mobilized). Tier 1, Tier 2, and Tier 3. They are allowed to submit preferences and rank their preferred weeks 1-52. Then based on employee rank, they are assigned their 10 weeks of on-call somewhere within the 52 weeks and splitting up Tiers as evenly as possible. So the number one rank will usually get all 10 of their preferences and so on. Three of each tier and then 1 floating that can fit wherever availability is.

Each week however, we only need X amount of staff to be on-call for each tier. We put more weight into certain weeks due to the nature of the business and likelihood additional staff needed.

So I need Excel to be able to do the following with the use of Macros -

1.) House a table of participating employees with preference weeks- pretty easy I can do that
2.) House a table listing all 52 weeks and the maximum amount of on-call staff needed per Tier (example: 20 Tier 1, 15, Tier 2, and 10 Tier 3)
3.) Have an output sheet with the results. Employees listed in alphabetical order in the Column A. Row 1 (header) would list the weeks 1-52 (dates appearing by day) so 365 columns. Then the results of what tier each person is on for their assigned weeks.

So basically when the macro runs it needs to look at each employee (row) separate. (they will already be in correct order based on rank). It then needs to look at the employees preferences starting with 1 and going up in order (these may not be in chronological order). If the preference is available and the table showing on-call staff needed isn't at 0, then it would assign that week. It would repeat this process until the employee has been assigned 10 total weeks. It then needs to move on to the next ranked employee.

I know that is a lot and probably all of it can't be done, but any one piece that can be automated is a time saver.

Some questions. When you say that people put in their top 10 choices, do you mean week and tier? Or if picking a week and not making the first or second cut, moves to the next tier? What happens if you don't enough people to staff certain weeks?

Originally Posted by dflak

Some questions. When you say that people put in their top 10 choices, do you mean week and tier? Or if picking a week and not making the first or second cut, moves to the next tier? What happens if you don't enough people to staff certain weeks?

Hi, Thanks for the reply! They only submit preferences for weeks and not tiers, I choose those based on needs. If the first and second isn't available, then it keeps going until the next preference is available. We should always have enough people, making sure number of weeks being assigned match the employee count. Please let me know If you need clarification on anything else and thank you for your willingness to help.

This will probably take a while and will have to await until I have a reasonable block of free time to work it. If anyone else wants to jump in in the meantime, feel free to do so.

I got the break I was looking for and this was a bit easier than I thought. On the Tier page, you define the Week and how many people can be assigned to each tier. I picked low numbers so I could do some QA on it. On the preference page you enter the people and their choices 1 to 10. Both of these pages use Excel Tables.

The preference page does some data validation on the entries. It validates that it's a whole number between 1 and 52. It does not validate that the number has not been used somewhere else on the same row.

The code contains comments on the logic. First I clean out the old data for Remain and Assigned on the Tier page and Weeks on the Preference page. Then I set the remain to the maximum allowed for that tier. The code sorts people by rank and goes through their choices. If a tier for the requested week is available, the name is assigned to that tier, and the number of slots in that tier is decremented by 1. When the tier for that week gets to zero, it is "closed."

You can hide the Remain helper columns. They should all be zeroed out by the time the program completes. Originally, I did not have enough people to fill all the slots. In this case, some tiers were not fully staffed. I did not go back and fill these with available people who did not include them in their top 10. In other words, a person might have gotten 8 of 10 requested weeks. I did no use this person to fill in an understaffed tier.

I did not do a lot of QA on this.

dflak,

Thank you so much for this, I really appreciate it. I will spend some time doing some QA and testing different scenarios. I was wondering for easy of use, is there a way to do a finalized copy that has the weeks assigned. For example:

Employee Name -------------------Dates listed by week of over here - 01/04/16-------1/11/16 etc for all 52 weeks

Bill Johnson Tier 1 Tier 2 Tier 3


We use this format to complete a mail merge which allows us to easily send the employees a list of all their weeks assigned.

Again thank you so much.

Please attach a small sample of how you would like the data for the mail merge organized. I think we can do something with the tier page.

Again, thank you so much for your assistance! This truly is awesome that you're taking the time to work with me on this. The details about the VBA language is very helpful too! So I have made a few modifications to the worksheet to fit in with a new change to how we handle this. Sadly, I keep getting mismatch error when trying to run the code. It works for the first 12 people and then kicks out an error. I have tried to modify the code but I can't seem to find the fix.

So we are now splitting the year into two rounds. Agents will submit preferences ranked 1-22 for the first half and then 1-30 for the second half of the year so we will be assigning in two rounds. The first round will assigned 4 weeks of on-call ( I would like one of each tier) and then 1 can be randomly assigned based on remaining availability and would be ideal if the rank could be taken into account and assigned the lowest tier available. Example (employee ranked #1 and is assigned 1 of each tier, I would like their 4th assignment to be tier 3 if available.)

The next caveat is that agents are allowed to submit a priority week of PTO. Can we write a statement to ignore that week?

I added the Results page in the format I would ideally like to see it output in. Lists each day at the top, employees to the left, then site, then enter in the Tier they are on. Would that be possible?

Lastly, employees don't always submit preferences as this is option. How do we handle the blanks? They can be assigned next available based on rank.

I attached a new copy. Again, thank you again!

I can't open the attachment. Could you please try again?

I'm sorry, here it is again.

Originally Posted by dflak

Still cannot download it. Can you?

Yes, it lets me download it no problem. Using firefox browser if that makes a difference. Is it giving you an error message?

Originally Posted by dflak

I can't open it, but I can download it to disk and I can open it from there.

Thank you! If you need anything from me, let me know. I have been trying to work with the code to make it work but continue to be unsuccessful so I really appreciate the assistance.

No worries, thank you!

As always, thank you for your help! When I try to run the code, I am getting a mismatch error on this line of code: C=C+1 after it loops the first time. Any idea what might be causing this? Also, when you say this wouldn't have been the way to go with the changes, could you let me know what you think might be a better route?

Thanks!

I wonder if there is something I am doing wrong? I try to run it the way you have it when I open it and continue to get the mismatch error. I can't seem to isolate the issue. It seems like it is trying to run the code and put the assignments in the header row of the table.

Also how can I make it so I designate the amount of weeks it assigns as that is a variable that can change.

That worked! Thanks! The Calendar output seems to be excluding some data. I am looking into it as well. One question, does this somehow limit the number of weeks it assigns? This variable does change so just wondering.

Thanks gain

Ok, so I got the weeks data to work and I think I made the adjustments to make the results output. I know your busy, but is there a way to limited the amount of weeks this assigns? The first round needs to be 4 total weeks (1 of each Tier) and then a random one. The second round needs to assign 6 weeks (2 of each).

Originally Posted by dflak

I do not understand the question. Can you give me an example or two about what you want. I don't have the concept of what you mean by rounds.

For example, we split the process into two rounds. Employees submit preferences for the first half of the year and we only assign them 4 weeks of oncall. Then later, they submit preferences for the second half of the year. This only assigns them 6 weeks. I need the tiers to be even. So the first round they get a one tier one, one tier two, and one tier 3. The fourth can be any one. The second round they need two of each for 6 total weeks. Right now it keeps assigning based off preference. So some employees get 10 weeks and some get as little as two. Is there a way to say after "X" amount of assignements stop the loop and move to next employee?

Originally Posted by dflak

I do not understand the question. Can you give me an example or two about what you want. I don't have the concept of what you mean by rounds.

For example, we split the process into two rounds. Employees submit preferences for the first half of the year and we only assign them 4 weeks of oncall. Then later, they submit preferences for the second half of the year. This only assigns them 6 weeks. I need the tiers to be even. So the first round they get a one tier one, one tier two, and one tier 3. The fourth can be any one. The second round they need two of each for 6 total weeks. Right now it keeps assigning based off preference. So some employees get 10 weeks and some get as little as two. Is there a way to say after "X" amount of assignements stop the loop and move to next employee?

Hi Dlfak,

I don't want your work to go to waste, have you had any time to review this?