Massive Sorting question.

Hey guys, I'm not the most adept excel user and I do have some minor experience with C++ but that's the extent of it.

I was wondering if you had any tips for something I'm trying to put together.


I'll start with a list, we'll say 1500 names all given a rank from 1-1500. Each of these 1500 names will have a title that dictates to which group they belong. Each name will also have a list of preferences. We'll say 1-6. so they could be in any order. It will look kind of like this:

NAME RANK GROUP 4 3 5 2 1 6

What I'm going to need to do is sort the list by group first, then sort each of those groupings by rank. Then I'll have to assign, based on ranking their preferences in the 1-6. To make things more complicated, each of those 1-6 can only have a certain number of people assigned to them.

finally i need the decisions output in a list format by grouping, then name, with the 1-6 option they had selected.

So I know exactly what im trying to do, looking for the best way to go about it, any tips would be appreciated.

yeah sorry, column e was supposed to be included. I'll do the rest shortly.

spotted a mistake so I've changed it.

I also put headings in.

Well first off thank you, Your sample sheets have proven invaluable to my creation of this sheet but I've run into more issues I have to deal with. Right now I'm testing it with a list of 1500 fake people in 6 fake groups with 6 fake choices. Now the sheet itself needs to accomadate 2000 people 11 groups with up to 11 preference options.

What I'm having trouble with now is trying to find a way to remove a person from the list once they've secured a choice. Because I also need to have the "caps" on the various preferences being taken down as they are used up. In the current form the cap resets for each choice every time we get into a new preference.

I think I know a way to make the caps dwindle down appropriately but I may not be able to do it for 11 choices since Excel only allows nesting of 7 functions. As for removing people once they have a choice, I'm not entirely sure how to approach it, everything I think of eventually runs into some kind of snag.

Here is an example of what I've got...

It's a rar inside a zip, only way I could make it small enough

http://www.rarlab.com/download.htm

If you can't open rar files, my favorite is winrar

I see you used my RANDSEQ function. It cannot reside in a sheet module or the ThisWorkbook module; it needs to go in a code module.

Originally Posted by shg

I see you used my RANDSEQ function. It cannot reside in a sheet module or the ThisWorkbook module; it needs to go in a code module.

Yea I did try and put it in there, I'll move it over to the proper module on the main sheet. I ended up just using the formula daddylonglegs wrote in another sheet and copying over the values to get the numbers I needed.

I'm joining this late, so I may be completely adrift ...

Is the objective to allow each person to receive their highest ranked choice, subject to a limit of the number of slots available for each choice, and allowing the higher ranked people to make their selections first? If that's close, is there more to it?

There was more than formatting; the spreadsheet makes assignments based on rank, preferences, and the number of slots available for each preference.

You have an implicit knowledge of the problem that isn't coming across to me, sorry. I don't know what 'call types' refers to, or the relevance of groups, or ranks.

If you want to start at 10,000 feet and explain the problem from the top down, my ears are open.

Ok, I'll see if I can do that. I am getting a series lists that totals up to around 7,000 different people. This 7,000 is going to be split into various amounts, but it shouldnt ever surpass 2,000. Each of this smaller groups will have their own ranks so if one of them was 1500 people they'd be 1-1500 while another place that had 500 would be 1-500. Each of those lists would also have it's own list of available preferences and each person in it would belong to yet another group that describes their specific abilities.

So with my examples somone from the 1500 group might look like so:

Rank Name Group2 Pref 1 pref 2 pref 3 pref 4 pref 5 pref 6

While the one from the 500 group might look like

Rank name group1 pref 1 pref 2 pref 3 pref 4 pref 5 pref 6 pref 7 pref 8 pref 9

There will never be more than 2,000 people in a list, there will never be more than 11 preference options for a list and there will never be more than 12 groups in a list. So that's what im trying to build to.

Now as for the actual sorting. Each preference will be a certain % of the total headcount.

So for example in a 4 preference list we'll say they each get 25% of the total headcount. Now each of those preferences will further be divided into the groups for that list. So if there are 4 preferences and 4 groups each group might only have access to 25% of that 25%. Now all these headcounts and %'s need to be variables that can be changed by the user inputting the current list. They are never going to be as cut and dry as 25% unfortunately.

So I want people to be able to go into this blank sheet, set the groups, the %'s for preference/group allocation, then put the list of agents in and have them sorted based on those parameters.

I don't know if that makes it any clearer, it's a frustrating project.

Still not seeing it -- you backed up to 1,000 feet, but I still need the 10,000 foot view.

1. Are the groups entirely independent, i.e., can assignments be made within a group without regard for assignments in any other group, so each group is effectively a completely independent problem?

2. What is the significance of 'groups within groups'?

3. What is the sparse resource? Does it correspond exactly to the preferences?

4. Rank determines the order in which people select their highest remaining preference variable?

Originally Posted by shg

Still not seeing it -- you backed up to 1,000 feet, but I still need the 10,000 foot view.

1. Are the groups entirely independent, i.e., can assignments be made within a group without regard for assignments in any other group, so each group is effectively a completely independent problem?

2. What is the significance of 'groups within groups'?

3. What is the sparse resource? Does it correspond exactly to the preferences?

4. Rank determines the order in which people select their highest remaining preference variable?

1. Well the groups are independent but only if the limits are sorted by group as well. Which is how they are supposed to be anyway. So if you divide the list of people into their groups and sort them into preferences also divided by groups then it's fine.

2. Hmm, ok, we'll say these are call center agents, now each person can take a certain type of call, that is their "group" there can be up to 11 of these groups, each person will only be assigned to one. Each person has picked from 1-11 preferences of schedule types. Now each schedule type or "preference" is allotted a certain percentage of the total amount of agents on that list. So if my list was 1000 people, it would split that 1000 Based on the % allotted to that schedule preference. So in my 25% example each preference would have 250 people.

So I've got a 250 person limit total for each schedule preference. Now, for each of these preferences with 250 people as the limit I have to further divide by the groups or calltypes in that list. So if we had 4 call types we'd take each of those 250's and split it further by another 4 percentages. So in reality you'd be taking a list of agents split into the 4 groups or calltypes and sorting it into the list of preferences also broken into calltypes. Each calltype having it's own limit of how many people are allowed to go into it.

3. The reason there are limits on it is because we only need so many people working at any given time, so each preference has a limit on how many people are needed in that timespan and is further divided by how many people are needed in that timespan for each group or calltype.

4. Yes the rank will decide which choice it is they get. so even when split by the calltypes, their rank is still of prime importance.

1. Well the groups are independent but only if the limits are sorted by group as well.

So is the limits (which are the numbers of people that can be assigned to each schedule, correct?) are allocated proportionately to the top-level division of groups?

2. Hmm, ok, we'll say these are call center agents, now each person can take a certain type of call, that is their "group" there can be up to 11 of these groups, each person will only be assigned to one. Each person has picked from 1-11 preferences of schedule types.

I thought the 11 (variable by group, I know) preferences were for schedule; please explain the significance of call type.

I think I could understand if you would explain the top-level problem. It doesn't need to be true, but it must an exact analogy to the situation, and account for all variables applicable to the assignment. For instance,

"I have a call center that has to be staffed 24/7. There are varying activity levels by time of day, which means that staffing level must vary accordingly. My agents have varying seniority and varying skills, ..."

You take it from there.

Originally Posted by shg

So is the limits (which are the numbers of people that can be assigned to each schedule, correct?) are allocated proportionately to the top-level division of groups?


I thought the 11 (variable by group, I know) preferences were for schedule; please explain the significance of call type.

I think I could understand if you would explain the top-level problem. It doesn't need to be true, but it must an exact analogy to the situation, and account for all variables applicable to the assignment. For instance,

"I have a call center that has to be staffed 24/7. There are varying activity levels by time of day, which means that staffing level must vary accordingly. My agents have varying seniority and varying skills, ..."

You take it from there.

I have many call centers all open 24/7. There are eleven versions of schedules available for these call centers to use. Each center can use any number of those schedule options to provide coverage for the day to keep the center open. There are varying activity levels over the course of the day and different calltypes that come into the centers. Because of this, the schedule options, depending on what time of day they take place in are only able to have a certain % of the overall staffing. So the afternoon and evening schedules would get a larger allocation than say the overnight or early morning schedules.

Further into this we have different kinds of calls coming in, also at varying activity levels during the day, every person in a call center has a MAIN call type they take, for schedule assignment purposes we will use this as the group they belong to. Each of these various schedule choices is further split into these main call types. Because while Call type A may be busy in the morning and less in the evening, B is busy in the evening and not in the morning. So A would have a larger percentage of the morning schedules open to it and B would have a larger percentage of the available evening schedules open to it.

Each center has a different number of agents not surpassing 2,000, up to 11 schedule options and up to 12 call types. But the same principals of sorting apply to all of them.




Right the limits are the maximum amount of people that can be assigned to a schedule choice. The limit for each schedule choice is divided up into the different calltypes. Each agent is going to just have one call type assigned to them and they should be getting assigned to schedules based on rank and that call type.

The agents are ranked, they put a choice by all the schedule options, in order of preference. Schedules should be handed out based on the main calltype an agent has and the rank they have.
So if i've got bob who is ranked number 20 in the entire call center and he's part of call type 6. His rank will only be compared against people also in call type 6, and those people will all only be put into schedules that are designated call type 6.

Is that what you were looking for?

That is the correct sorting process but the data is already seperated by call center at the source, we are getting one sheet from each center with their agents/call types/preference options. So what I'm trying to create is a sheet that any of those sites can take. Input the parameters for their specific site and have it sort them whether it be 10 call types and 4 preferences or 6 preferences and 4 call types. I also need to make the page where the limitations are put in as well as they will change often and this sheet will be used more than just this one time.

The final output would be sorted by call type and name or rank since we only work with one site worth of data at a time. The sheet i posted further up has the type of data we'd be getting, the tab labeled band info is where the limits are set but I hadnt expanded it into call types yet because I hadn't figured out how to get around the 7 nested function limit of excel.

Here's an example of the inputs the user would be putting in. Including the 1500 fake agents I've been working with

The b1 g1 and whatnot were just the group numbers i assigned to the fake calltypes on the original sheet. Because I was using concatenate that number was part of the unique ID each person had. It doesnt really have any bearing on the information there, sorry for the confusion.

Band info is just another name for the schedule preference options. The FTSR FTM FTD and whatnot are the different schedule choices, we just call them bands. Schedule bands.

Basically what the preferences does is let the user setup the different limits. The first box is the main one, it has the total amount of slots available divided up by the different preferences available to a site. My fake site has only 6 schedule options, that's what the 123456 is for in that 2nd column. The 3rd column is the total headcount at the top (1500) multiplied by the percentage in the 3rd column.

So if we take A3 B3 C3 and D3 and go across. FTM is an option for my fake site, the 1 signifies that fact that it is option 1 in the list that agents can put a preference for C3 shows how much of the total allotment is allowed for that schedule option. so in this case 225. D3 is a user defined percentage for that option, so in this case FTM gets 15% of the overall 1500 allowed here which ends up at 225.

Now all the other boxes are populated based on the number of calltypes available at that site. My fake site has six to choose from. So in that 2nd box starting at F1, it's calltype 1. Since each schedule option is further divided by the calltypes these boxes will contain the limits divided up by group.

Taking the FTM row again we see that call type 1 agents are allotted 30% of the total FTM slots, giving them 68 spots for that particular schedule option. This continues on with call type 2 call type 3 and so on.

The far right column is there to make sure there havent been any errors made, if P1 is 0 then all the splits into call types adds up to the total slots allowed for that site and the percentages in the next columns show that you've allotted 100% of the slots into the various calltypes.

So when I'm trying to assign schedules to agents based on the limits, it would take the agents divide them into their calltype groups then put them up against these limits for each call type here. So all the Call 1 agents would go against the 2nd box, all the call 2 against the 3rd, and so on until they were all assigned.

Yea it doesnt appear to be missing anything. Though I think the maximum amount of preferences is 11 rather than 10, I dont have the name for the last one available to me, so I should've just added one more row to the list. Oh and for the .5 numbers I just rounded them up.

Oh and for the .5 numbers I just rounded them up.

Not to get down to the trivia level, but the VBA Round function does banker's rounding: 0.5 gets rounded to the nearest even number (0.5 -> 0, 1.5 -> 2). That's different than the worksheet ROUND function, which always rounds 0.5 up.

Does that matter?

I'll post another whack at it sometime this evening.

Hmm, I think the worksheet round function would end up more accurate in the end but in reality I don't think it's that huge of a deal.

That is actually perfect, and much less confusing than my attempt with just excel formulae

So apparently I was misinformed about the actual parameters that guide how many slots each call type gets. Rather than taking the total site headcount and dividing that up into bands, then dividing those into the types it's supposed to be set up in such a way that the user inputs the headcount for each specific call type then sets the percentage allocations for the schedules available to that calltype.

Also I tried various ways of cutting and pasting my agent info into the sorting table, since the agents information will be turned in as numbers rather than the actual choices, i couldnt get it to function properly.

I've attached an example of the form we'll be getting information on, this particular site has 10 schedule options, I'm not positive on the number of calltypes. The only information I planned on taking off of this sheet would be the name/Calltype/preferences/rank. the rest isnt really important as far as the sorting process. But you can see on there that the schedule options themselves are in a fixed location and the agents are just inputting numbers to show order of preference.

Any help would be much appreciated, I'm in way over my head with this stuff, the sheet ive been working on is up to 162mb in size working purely with excel formulas.

Originally Posted by shg

Maybe that actually makes more sense, but,
That part doesn't. Each agent has a call type, so when all the info is entered, the number of agents by call type is calculated, not entered--correct?

1. Can you control the Band Assignment form to be used, so that the info can be extracted from it automatically, or do you want to cut and paste from the Band Assignment form to the assignment spreadsheet?

2. In particular, why is the rank on a separate sheet from all of the other information? It could be a single added column on the Band Assignment sheet. That, combined with the fact that there are several redundant pieces of info -- name, emp ID, TID, DOH on the Rank tab, is made to order to cause problems if this info is entered on a sparate sheet.

3. What's the maximum number of bands that should be accommodated? The ones in your example do not cover a 24-hour period.

4. In allocating call types by schedule bands, what would prompt a supervisor to differentiate between FTSR and PTSR, or FTM and PTM, considering that the actual work times are exactly the same?

BTW, please let me know if you are intent on a formula-based solution. I can't help with that, but perhaps someone else could.

Yes the number of agents per call type could be calculated, that's how I'm doing it in my version actually.

Unfortunately there are roughly 10 or 11 different versions of that band spreadsheet, it isn't something I created nor can I alter the format. While the general formatting is the same the difference between them all would be the number of Bands. I too wondered why the ranking was put on a seperate page from everything else. But that's the way it has to be unfortunately.

Basically i envisioned whoever was using the sheet inputting the name, rank, and preferences of the agents and now the percentages for the caps on the bands for each call type.

There will be 11 bands maximum, not all sites are 24 hours but the largest amount of bands any site has is 11.

there would be less staffing allocated to the ones beginning with P most likely as that indicates part time, F is full time. So part time schedules may only be allocated 5% of the call type headcount while full time would get 20%. These numbers vary by the way just an example, they need to be something that can be altered by the user.

I'm not really intent on a formula based solution, I'm running on a deadline so I have to have some form of this operational as soon as I can and formula based is all I know how to do. It's slow running and excessively large so not really Ideal in the end. Call it a backup plan. I'd much rather be an expert in VBA but it's not something I've started to learn yet

Try this ...

Yep that's exactly what I needed, far quicker to deal with than my monster spreadsheet too. Thank you very much, about that case of scotch lol...