Unique and complicated question - for me at least

Bothell

New Member
Joined
Aug 4, 2003
Messages
20
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
CDEFGHI
2Choices
3NameRank12345
4Agent141432
5Agent213
6Agent3534251
7Agent43524
8Agent5243
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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

Well-known Member
Joined
Dec 10, 2002
Messages
1,199
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

New Member
Joined
Aug 4, 2003
Messages
20
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

Well-known Member
Joined
Dec 10, 2002
Messages
1,199

ADVERTISEMENT

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

New Member
Joined
Aug 4, 2003
Messages
20
The 2000 agents are ranked by their hire date then by a performance score then a years of service calculation.
 

Seti

Well-known Member
Joined
May 19, 2002
Messages
2,916
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

New Member
Joined
Aug 4, 2003
Messages
20
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,871
Messages
5,766,855
Members
425,380
Latest member
CubeGirl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top