Unique and complicated question - for me at least

[Bothell]

I work in a staffing department and help manage the staffing of several call centers. Periodically, they take a pool of bankers and realign the schedules to gain efficiencies. All the agents are assigned a rank, and we produce a pool of schedules for how many agents there are. The agents then choose enough schedules to match their rank (I.E. someone ranked 3 would have to choose 3 different schedules). In theory, this works, because they know that they will at least get one schedule that they have chosen.

The perplexing issue that I have been wrestling with is this. I would like to automate the selection process. I am including a small example so I can illustrate my issue:

Book1
	C	D	E	F	G	H	I
2			Choices
3	Name	Rank	1	2	3	4	5
4	Agent1	4	1	4	3	2
5	Agent2	1	3
6	Agent3	5	3	4	2	5	1
7	Agent4	3	5	2	4
8	Agent5	2	4	3
Sheet1

What we are doing right now is sorting based on rank and marking off schedules as they are used. For example, Agent2 will get Schedule 3 because he is ranked the highest. However, when I move to Agent3, Schedule 3 has already been taken. There is a master list of schedules, and we are meticulously crossing them off and checking each Agent. This wouldn't be bad if there were only 5 agents like in the example, but there are over 2000, and you can imagine how tedious that can become. Not to mention the human error that is introduced.

What I am looking for is a solution, or some kind soul to point me in the right direction as to how I can automate this process. I know Excel pretty well and Access fairly well, and I am not sure how I should tackle this.

Thank you,

~bothell

 

[Zack Barresse]

how are the 2000 agents ranked? 1-2000? can you explain that a little more?

and how many choices does each one have? will there only be 5 choices per rank?

 

[earlyd]

One method I would suggest is having a macro sort through your list by rank and assign numbers as it progresses through the ranks, numbers already assigned would get flagged in an array (for example, you have a 2000-element array and as 3 gets assigned, the third element in the array gets a 1 instead of a 0 value) and that array gets checked as another 3 is encountered, etc. You need to decide if you are going to give preference to someone who only has 3 vs. someone else at same rank who has 3 and another value(s).

In short, I recommend a VBA macro and the algorithm for it would take a bit of thought. Sounds like a good small project for someone.

 

[Bothell]

Do you have any good sites or directions that you could point me in to learn about arrays and what not? I won't be able to whip this kind of thing up over night, but I like to learn new things and would love to dabble in this direction.

 

[earlyd]

Basically, this site is as good as any for learning about VBA macros. Any programming language text can teach you about arrays. However, if you've never programmed, you might consider consulting with someone who has that background for this.

You can create an array in a macro with:

Dim schedules (2000) as integer

This reserves a spot for up to 2000 "scheduled assigned" indicators either 1 or 0.

Dim schedulesAssigned (2000) as boolean reserves a spot for 2000 true or false values.

The key challenge is going to be writing the logic of the program - tough to explain in a post.

 

[Bothell]

The 2000 agents are ranked by their hire date then by a performance score then a years of service calculation.

 

[Seti]

In your example, there is a unique solution since the highest ranked agent only needs one choice, then the second highest ranked gets two choices, etc. Therefore, the lowest ranks need to have all possible options ranked. I am guessing that with 2000 agents, the lowest agent doesn't get 2000 choices. If time is not critical to the process, you could run 50 agents at a time and once they get their choices fixed, you could then remove these from the pool and do the next 50.

As an alternate idea, can you rank each possible choice in advance and then if an agents 5 (I made this up) choices are not available, you could then go the top choice still remaining from your own rankings?

Otherwise, it sounds a little bit like some AI might be needed here. For example, if agent 50 can get his 3rd choice and his 4th is also available, but agent 51 can only get his 5th choice if you assign agent 50 his 4th rather than 3rd, how would you handle this. In the same example, assume each agent ranks their top 20 choices and agent 51 can get 5 or 20 and agent 50 has his 3rd, 4th and 5th choices available, what would you do?

These questions are simply provided to help clarify the problem, not to directly lead to a solution.

 

[Bothell]

Seti - Thanks for the reply!

Unfortunately we have only 8 days to handle these schedules. There are 19 different work types and three different sites and three categories, all of which has to be analyzed individually.

At this point I know of no other solution than to track these individually and mark them off as they are done. Not fun, but until I learn VBA some more, I know of no other solution.

I appreciate all the feedback I got from everyone here.

~bothell