• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Seat allocation with Date & Time

jayexcel1

Member
I have a project of scheduling candidates across three cities where I have to assign candidates a particular date and time as per their preferred choice of city.

Assumption:
I have with me two sets of data:

1) Detail of test centres where a single city can have multiple LABS (Centre Code). For eg. in Barcelona city there are 9 labs; LAB1 can accomodate maximum 11 candidates in a particular date and time, 11 candidates on 2nd jan 10:00. I will be having this data as a matrix as shown in the attached file (Capacity Matrix).

2) Candidate information will be available as a single list as shown in the attached file(List-1)

Aim:
The aim is to assign a particular candidate(identified here with unique application id) a specific date and time in his/her first preferred city at a particular Centre Code.

Also, I want to limit giving schedule in a particular city to ONLY 80% of its capacity; for eg Boston can have maximum 81 candidates; so I need to stop giving schedule to candidates in Boston once the "number of candidates scheduled reaches 81*0.80=64.8~65; after 65 candidates have been given Boston, stop and move on to the next city and the same process is followed.

Output:
The result should be populated as shown in "candidate schedule" of the attached file.
 

Attachments

  • Seat allocation with date and time.xlsx
    33.9 KB · Views: 26
Hi ,

An interesting problem ; some clarifications may help.

1. Out of the total 850 candidates , 792 have given Barcelona as their first preference , 55 have given Boston as their first preference , and only 3 candidates have given Chicago as their first preference.

2. Since there are a total of 144 seats available in the 15 labs , and there are 2 time slots per day , can we assume that a total of 288 candidates can be given seats on one day ? If so , then the 850 candidates can be completed in at most 3 days. The break-up city-wise is 18 x 2 i.e. 36 seats per day in Boston , 41 x 2 i.e. 82 seats per day in Chicago , and the remaining i.e. 85 x 2 i.e. 170 seats per day in Barcelona. I have not taken into account the barred time slots ( the ones which are coloured RED ) ; if these are taken into account , we may need more than 3 days. But this in no way changes the basic logic.

2. Is it to be assumed that a lot of candidates will not get their city of first preference ? If so , then it would be better to start by giving the 3 candidates who have opted for Chicago as their first preference , their chosen city ; next we allocate Boston to those who have preferred it as their first city of choice , and when we are done with these 55 , we start allotting seats to the remaining ; we can complete the balance seats available in Chicago , and then allot all the remaining candidates to the labs in Barcelona.

Will this be OK ?

Narayan
 
Hi ,

An interesting problem ; some clarifications may help.

1. Out of the total 850 candidates , 792 have given Barcelona as their first preference , 55 have given Boston as their first preference , and only 3 candidates have given Chicago as their first preference.

2. Since there are a total of 144 seats available in the 15 labs , and there are 2 time slots per day , can we assume that a total of 288 candidates can be given seats on one day ? If so , then the 850 candidates can be completed in at most 3 days. The break-up city-wise is 18 x 2 i.e. 36 seats per day in Boston , 41 x 2 i.e. 82 seats per day in Chicago , and the remaining i.e. 85 x 2 i.e. 170 seats per day in Barcelona. I have not taken into account the barred time slots ( the ones which are coloured RED ) ; if these are taken into account , we may need more than 3 days. But this in no way changes the basic logic.

2. Is it to be assumed that a lot of candidates will not get their city of first preference ? If so , then it would be better to start by giving the 3 candidates who have opted for Chicago as their first preference , their chosen city ; next we allocate Boston to those who have preferred it as their first city of choice , and when we are done with these 55 , we start allotting seats to the remaining ; we can complete the balance seats available in Chicago , and then allot all the remaining candidates to the labs in Barcelona.

Will this be OK ?

Narayan


Hi Narayan,

1. 792 candidates have given preference to Barcelona but only 537 can be accommodated there (considering the cells colored RED in Capacity Matrix). Likewise, for Boston 81 candidates can be accommodated and Chicago 210 candidates can be given their first preferred city.

2. We need to consider the cells colored RED while giving the candidates their first preferred city. For eg Barcelona can have max 65 candidates scheduled for 1st Jan 10:00 time slot, whereas 85 candidates can be scheduled for 2nd Jan 10:00 slot.

3. Yes, it might be possible that some candidates might not get their First preferred choice of city. For eg Barcelona has actually more candidates than it can accommodate; 792-537=255 candidates to be precise. So in an ideal scenario, these 255 candidates could be given their Second choice of preferred city. And if there second choice of city is also not available then move on to third preference.

Hope this would help.

Jay
 
Hi Jay ,

I have clearly mentioned that I have NOT considered the RED coloured cells in arriving at my figures ; I have also mentioned that this will not invalidate the basic logic. Can we move on to the next point which I wanted you to clarify / confirm viz. the order of seat allocation.

Since Chicago has the lowest number of candidates whose first preference is Chicago , can we start by allocating these candidates their first choice city ?

Since Boston has the next lowest number of candidates whose first preference is Boston , can we next allocate these candidates their first choice city ?

Lastly , can we start allocating candidates whose first preference city is Barcelona ?

Narayan
 
Hi Jay ,

Can you confirm a couple of points ?

1. Will everything in this scenario remain fixed i.e. in future , are the number of cities likely to increase ?

2. Will the number of time slots per day increase from the existing 2 ?

3. Instead of using the RED colour to identify a barred slot , can we put in the number 0 ? The cells can still be coloured RED using Conditional Formatting.

Narayan
 
Hi Narayan,

1. Before scheduling the candidates, I will have the number of cities with me and it will be fixed. Here for easy representation I mentioned only 3 cities, but it could be 25, 30, or even 40 cities in an actual scenario. However, as I mentioned, this will be fixed in advance and scheduling will only begin after that.

2. No, the time slots would remain fixed at 2.

3. Yes, it would be fine if 0 is used instead of RED colour.

Jay
 
Hi ,

See your file now.

There were 2 cases where the third preference city and the second preference city were the same ; I have changed these , since the way I have written the code , such cases will not be handled ; all the three preference cities will have to be different. If this is not going to be so , let me know , and the code will have to be rewritten.

Narayan
 

Attachments

  • Seat allocation with date and time.xlsm
    143 KB · Views: 26
Hi Narayan,

As I don't have much experience in vba; would it be possible for you to explain the logic you have used in writing the code. I want to first understand the code and then move forward rather than blankly copy pasting it. Would appreciate if you could help me out with understanding of the code.

Jay
 
Hi ,

There are two parts to the logic :

1. Getting the input data in the order of frequency of cities in the First Preferred City column ; for this , there are formulae which return the counts ; e.g. in the First Preferred City column , Barcelona has the highest count , followed by Boston , and then Chicago.

2. This information is used to create a Custom Sort list ; at present , the step is manual , which means you need to run the macro SortTable to ensure that the input data in the list of candidates is sorted in the right order.

3. Now if you run the macro Allocate_Seats , it does the following :

a. Get a candidate's information ( ID , First Preferred City , Second Preferred City and Third Preferred City ) from the list of candidates

b. Match the First Preferred City with the List of Cities in column C on the Capacity Matrix tab

c. Against this match go through the date and time slots in columns F through M to get a slot whose value is greater than 0

d. If such a slot is found , then allot that date and time slot to that candidate

e. Reduce that slot value by 1

f. Put all the information on the Candidate Schedule tab

Go back to step a.

Suppose in step c , there are no slots whose value is greater than 0 ; go to the next cell down in the City column and repeat steps c through f.

Suppose that there is no slot available for the First Preferred City , check if there are any for the Second Preferred City , and if there are none , repeat for the Third Preferred City.

In stepping down through the cells in the City column , if we come to the last city in the table , wrap around to the first city.

I hope I have not ended up confusing you !

Narayan
 
Hi Narayan,

While going through the output in Candidate Schedule tab, I observed that some candidates have been scheduled at slots that was not available for scheduling (marked RED); for e.g. Chicago has 3 candidates scheduled on 2nd Jan 15:15 slot in Lab 12 as is the case with Barcelona Lab 3 on 4th Jan 15:15 slot. RED cells or cells having 0 should not have any candidates on that particular slot. Please suggest.

Jay
 
Hi Jay ,

Please check this file ; the problem was with the wrong offset. I think it should be OK now.

Narayan
 

Attachments

  • Seat allocation with date and time.xlsm
    145.9 KB · Views: 16
Hi Narayan,

Great. The code now works perfectly. Some points that requires your guidance:
  1. When I'm copying the code to another worksheet with the same data structure and tab names and running SortTable, a pop-up appears as Object required!!. Any idea, what or where could I be wrong?
  2. As you had mentioned above, SortTable is to ensure that the input data in the list of candidates is sorted in the right order; then what is the purpose of Subs CreateCustomList and DeleteCustomList? Please help me understand these two.
  3. You have added an additional tab "Lists" where G2:G4 has city names and B7:B9 has numeric values. What role does it play in the logic? How would this data change if I have more than 3 cities with me?
Jay
 
Hi Jay ,

Please go to the Name Manager in Excel , and examine the named ranges that have been put in ; you will need to do the same with your workbook.

The formulae in G and H give the unique list of cities with their counts ; the SortTable procedure first does a normal sort on the Unique Application ID ; I am assuming that with the data sorted in this order , the list of cities will be in the order of their counts , with the most frequently occurring city at the top , and the least frequently occurring city at the bottom.

Now , the CreateCustomList procedure inserts the names of these cities as a custom sort list in the Sort feature.

Since this custom list will not be required elsewhere , running the DeleteCustomList procedure will remove it from the Sort feature.

As far as the Lists sheet is concerned , it is for the above two procedures ; you need not bother to do anything since only the CreateCustomList and DeleteCustomList procedures use it.

Narayan
 
Hi Narayan,

I'm encountering some problem with the macro. It worked fine with the old dataset; but I have now with me bigger numbers and I just used the same old sheet to copy paste the new data. Below are the issues that I'm facing now:

  • AllocateSeats macro returns the error "Unknown error. Aborting procedure" and stops midway.
  • Also, some of the data in Candidate Schedule sheet under columns "Test Date & Test Time" shows the column headers!!!
  • Old issue is cropping up viz "candidates have been scheduled at slots that was not available for scheduling (marked RED)". You had mentioned earlier for this that it was due to wrong offset and had corrected the same. Now I have just put in the new values in the same structure and still the old problem cropped up. for eg Lab 74 Nashville
Can you please look into it and help me out.

Jay
 

Attachments

  • Seat allocation with Date Time Copy.zip
    1,013 KB · Views: 10
Hi Jay ,

There are 2 problems here :

1. The dates and times are not being entered correctly in the schedule ; I will check this out , and rectify it. See the attached file here :

https://www.dropbox.com/s/b1acmfbf8jw4ky8/Seat allocation with Date Time Copy1.xlsm

2. The message which appears before the procedure aborts ; this is happening because all available seats for a particular candidate's 3 choices are all filled up. At candidate ID ABC22824 , whose location preferences are Greensboro , Nashville and Pittsburgh , Greensboro which had 2757 seats available , Nashville which had 570 seats available , and Pittsburgh which had 955 seats available , have all had all their seats already allocated !

What is to be done in this situation ?

Narayan
 
Hi Jay ,

There are 2 problems here :

1. The dates and times are not being entered correctly in the schedule ; I will check this out , and rectify it. See the attached file here :

https://www.dropbox.com/s/b1acmfbf8jw4ky8/Seat allocation with Date Time Copy1.xlsm

2. The message which appears before the procedure aborts ; this is happening because all available seats for a particular candidate's 3 choices are all filled up. At candidate ID ABC22824 , whose location preferences are Greensboro , Nashville and Pittsburgh , Greensboro which had 2757 seats available , Nashville which had 570 seats available , and Pittsburgh which had 955 seats available , have all had all their seats already allocated !

What is to be done in this situation ?

Narayan
Hi Jay ,

There are 2 problems here :

1. The dates and times are not being entered correctly in the schedule ; I will check this out , and rectify it. See the attached file here :

https://www.dropbox.com/s/b1acmfbf8jw4ky8/Seat allocation with Date Time Copy1.xlsm

2. The message which appears before the procedure aborts ; this is happening because all available seats for a particular candidate's 3 choices are all filled up. At candidate ID ABC22824 , whose location preferences are Greensboro , Nashville and Pittsburgh , Greensboro which had 2757 seats available , Nashville which had 570 seats available , and Pittsburgh which had 955 seats available , have all had all their seats already allocated !

What is to be done in this situation ?

Narayan



Hi Narayan,

Thanks for the reply.
Is it possible that; in case a particular candidate's 3 choices are all filled up (as mentioned above), that particular record is still populated in the Candidate Schedule tab with field values for Test Date, Test Time, Centre Code and City as "NA" or blank.
And, the macro moves on to the next record instead of aborting.

This would help in getting me to know which of the candidates I have challenge in giving schedule; also, it would be better if all other (non-problematic) records are processed at one go.

Jay
 
Hi Narayan,

Sorry for the trouble again. But would require your help once again. On careful analysis, I found that the macro was not giving schedule to first preferred cities even if there was available seats. For e.g. Boston had 2053 available seats and 1995 applications but only 56 seats were allocated as per first preference. Ideally, it should have been that all 1995 applicants should be accommodated in Boston itself. Similar issue with other cities too. I have attached the analysis sheet for your reference. Would request you to please look into it.

City # of Applications Capacity Less or More seats # of first preferred as per macro
Austin 1103 1276 173 1103
Boston 1995 2053 58 56 ISSUE
Charlotte 1521 1587 66 1521
Chicago 663 624 -39 624
Colorado 1637 682 -955 668 ISSUE
Detroit 284 3692 3408 284
Glendale 119 1368 1249 119
Greensboro 4238 2757 -1481 2584 ISSUE
Houston 484 3183 2699 484
Memphis 1294 1189 -105 1157 ISSUE
Nashville 843 570 -273 570
New Orleans 236 616 380 236
New York 1324 796 -528 513 ISSUE
Pittsburgh 1126 955 -171 860 ISSUE
San Jose 3380 1476 -1904 1437 ISSUE
Tampa 466 1148 682 466
Grand Total 20713 23972
 

Attachments

  • analysis.xlsx
    690.6 KB · Views: 9
Hi ,

Can you put the data in columns A through D in the original file , so that I can run the macro on this data , and see what the problem is ?

Or is this data the same as what is available in the earlier file ?

Narayan
 
Back
Top