You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
Seat allocation with Date & Time
- Thread starter jayexcel1
- Start date
NARAYANK991
Excel Ninja
Hi Jay ,
There is no problem as such ; it all depends on how the list of candidates is sorted ; I have now sorted it differently. See if the problem is still with Boston , or is now with another city.
https://www.dropbox.com/s/b1acmfbf8jw4ky8/Seat allocation with Date Time Copy1.xlsm?dl=0
Narayan
There is no problem as such ; it all depends on how the list of candidates is sorted ; I have now sorted it differently. See if the problem is still with Boston , or is now with another city.
https://www.dropbox.com/s/b1acmfbf8jw4ky8/Seat allocation with Date Time Copy1.xlsm?dl=0
Narayan
Hi Narayan,
Just checked the latest file. The problem still persists.
To make it simpler, I think the macro should work like this:
The first point regarding First Center choice is achieved from the existing macro under comment "Check the First Preference City".
However, I'm not able to work out the other requirements.
Please help.
Jay
Just checked the latest file. The problem still persists.
To make it simpler, I think the macro should work like this:
- The macro should first look only in First Center Choice column one by one for all the applicants.
- If an applicant whose first center choice is not available then leave the entry blank and move on to the next applicant. I think some kind of loop should be used here.
- After processing all the applicants for the first column; move on to the second center choice column. As mentioned above, one by one process each applicant for the second center choice and if available, allocate them the date and time else go to the third center choice column.
- If the candidate can't be allocated as per his choices then mention "Not Applicable".
The first point regarding First Center choice is achieved from the existing macro under comment "Check the First Preference City".
However, I'm not able to work out the other requirements.
Please help.
Jay
NARAYANK991
Excel Ninja
Hi Jay ,
See if this is OK. The macro will take much longer now , since it is making 3 passes through the data.
https://www.dropbox.com/s/b1acmfbf8jw4ky8/Seat allocation with Date Time Copy1.xlsm?dl=0
Narayan
See if this is OK. The macro will take much longer now , since it is making 3 passes through the data.
https://www.dropbox.com/s/b1acmfbf8jw4ky8/Seat allocation with Date Time Copy1.xlsm?dl=0
Narayan
NARAYANK991
Excel Ninja
Hi Jay ,
I have not gone into the details , but at first glance Scenario 1 and Scenario 2 should not pose any problem.
Scenario 3 is going to be tough , since on Jan 3 there are 3 time slots. The code is written so that finding the date is done by indexing , and as long as there are only 2 time slots for every date , everything is fine. If there were 3 slots for every date , that could also be coded.
Can I suggest a workaround , which may not be too much trouble ?
For Jan 3 , let us have 4 slots , with the date repeated for the 3rd and 4th time slots i.e. for the 1st and 2nd time slots , the date cell above the time cells will be merged to have Jan 3 in the merged cell ; for the 3rd and 4th time slots , let us have another 2 cells above the time cells merged together , and let this merged cell have Jan 3. For the time slots , let the 3rd time slot have 20:30 ( or 8:30 PM ) in it , and let the next time slot have any time whatsoever. Only all the values in this time slot should be 0 , so that this time slot is there only for symmetry , and in reality it will neither contribute anything nor take away anything. With this symmetry , the code will not have to be changed.
Comments ?
Narayan
I have not gone into the details , but at first glance Scenario 1 and Scenario 2 should not pose any problem.
Scenario 3 is going to be tough , since on Jan 3 there are 3 time slots. The code is written so that finding the date is done by indexing , and as long as there are only 2 time slots for every date , everything is fine. If there were 3 slots for every date , that could also be coded.
Can I suggest a workaround , which may not be too much trouble ?
For Jan 3 , let us have 4 slots , with the date repeated for the 3rd and 4th time slots i.e. for the 1st and 2nd time slots , the date cell above the time cells will be merged to have Jan 3 in the merged cell ; for the 3rd and 4th time slots , let us have another 2 cells above the time cells merged together , and let this merged cell have Jan 3. For the time slots , let the 3rd time slot have 20:30 ( or 8:30 PM ) in it , and let the next time slot have any time whatsoever. Only all the values in this time slot should be 0 , so that this time slot is there only for symmetry , and in reality it will neither contribute anything nor take away anything. With this symmetry , the code will not have to be changed.
Comments ?
Narayan
NARAYANK991
Excel Ninja
Hi Jay ,
OK. I'll check out the code on these 3 scenarios , and upload the file once everything's working properly.
Narayan
OK. I'll check out the code on these 3 scenarios , and upload the file once everything's working properly.
Narayan