Got a request last week to help make someones life easier by turning a monotonous, multi-hour task, into an automated breeze. The input would be a list of names, as well as a list of selections, and the corresponding top 4 choices for each person from this selection base. This input table would be provided in .xlsx (Excel) format.
The example below shows 5 sample students, as well as their top 4 choices from a list of companies.
The objective of this task, would be to then take this input, and spit out an interview schedule for each student, across 4 30-minute time-slots. The schedules would also need to take into account the companies schedule as well, as there are only 4 time-slots available, and one company shouldn't be overloaded during a single slot, and underpopulated for other slots, etc.
An example of the desired schedule output is reflected below.
This highlights the 4 available time-slots, as well as each student, and their corresponding schedule based on their top 4 preferences. You can also see, that for some students, the companies don't go in order across the time-slots, as these were re-arrange to account for under/overloading a company during specific time-slots.
A summary table for showing number of students per time-slot per company is also generated after schedule creation.
Currently, 2 schedules are created using 2 different sorting methods to try and optimize the population per-slot for each company. Schedule2 has proved to be a more ideal output the majority of the time; however, in this early revision of the script, the optimization is still not perfect.
As the input and output file were both desired to be in .xlsx (Excel) format, I decided to go the Excel macro route, rather than have extract the data in .csv to manipulate with an outside script (such as python, perl, etc), then have to feed back into Excel.
The macro route allowed me to keep everything embedded in the actual .xlsx file, as well as limit the number of docs that needed to be maintained, etc.
For a minimal number of student inputs, the schedule creation is rather trivial to do by hand; however, with the latest input expected to exceed >; 200 students, you can see how automation can save significant time.
Currently, the script can churn through the 200 students in ~15seconds. A pretty significant amount of time-savings if you asked me.
Anyways, I may write-up the nitty-gritty details later, but a small clip is available below.
The example below shows 5 sample students, as well as their top 4 choices from a list of companies.
The objective of this task, would be to then take this input, and spit out an interview schedule for each student, across 4 30-minute time-slots. The schedules would also need to take into account the companies schedule as well, as there are only 4 time-slots available, and one company shouldn't be overloaded during a single slot, and underpopulated for other slots, etc.
An example of the desired schedule output is reflected below.
This highlights the 4 available time-slots, as well as each student, and their corresponding schedule based on their top 4 preferences. You can also see, that for some students, the companies don't go in order across the time-slots, as these were re-arrange to account for under/overloading a company during specific time-slots.
A summary table for showing number of students per time-slot per company is also generated after schedule creation.
Currently, 2 schedules are created using 2 different sorting methods to try and optimize the population per-slot for each company. Schedule2 has proved to be a more ideal output the majority of the time; however, in this early revision of the script, the optimization is still not perfect.
As the input and output file were both desired to be in .xlsx (Excel) format, I decided to go the Excel macro route, rather than have extract the data in .csv to manipulate with an outside script (such as python, perl, etc), then have to feed back into Excel.
The macro route allowed me to keep everything embedded in the actual .xlsx file, as well as limit the number of docs that needed to be maintained, etc.
For a minimal number of student inputs, the schedule creation is rather trivial to do by hand; however, with the latest input expected to exceed >; 200 students, you can see how automation can save significant time.
Currently, the script can churn through the 200 students in ~15seconds. A pretty significant amount of time-savings if you asked me.
Anyways, I may write-up the nitty-gritty details later, but a small clip is available below.
Did you ever write out the details? I'm in this same situation and would love to have some help with it! Thanks :)
ReplyDelete