ECO Project
Budget: $10 – $15 USD
I want to write a macro code for Excel to create a schedule and distribute a list of students fairly and randomly taking into consideration the following points:
inputs:
1. A list of students (Columns A)
2. A list of Areas and their corresponding pagers (Columns B and C)
3. A list of time slots (Column D)
4. list of days (Column F).
5. Maximum number of students in each shift {in a day} (specified in J1).
6. Maximum number of students in each shift {in a specific time slot} (specified in J2).
7. Maximum number of students in a specific area. (specified in J3).
Conditions:
1. Based on the number of days (input 4), the number of students in each sift (inputs 5 & 6), and the number of students in a specific area (input 7), students will be divided fairly and randomly in a way that each student will have a same number of shifts and days off.
2. Each Student will be required to visit each area at least 1 session based on the input Columns B and C.
3. Each Student will be required to try a different time slot based on the input Column D.
Clarification:
1. Students should have the same number of shifts and days off in between.
2. The distribution should be fair, and each student should have at least one opportunity (as possible) to try different areas and start times.
3. If the distribution can not be fair, and a student can not have an opportunity to try a specific area and/or start-time, please create another sheet called "missing" and specify those students and their areas and/or start-time that did not have from all distributions.
4. Multiple students can be in the same area or/and time slot (based on the inputs 4-7)
5. Columns have headers, therefore values start from the second row.
Results:
Students will be distributed based on the given criteria above and the results will be displayed in a schedule for each day. The schedule will be printed out based on the following:
1. Column A: name of student,
2. Column B: his areas
3. Column C: the corresponding pagers of his area
4. Coulmn D: his time slot.
Please note that the schedule will be sorted out based on the specified time slot (column D) for each day.
For example,
Inputs:
Column A: student 1, student 2, student 3, student 4, student 5, student 6, student 7, student 8
Column B: adult, pediatric,
Column C: 2456, 1234
Column D: 07:30 am, 13:00 pm
Column D: Day 1, Day 2, Day 3
J1: 4 "in aday"
J2: 1 "in time slot"
J3: 2 "in area"
Resutls:
1. a "result" sheet created, and a empty row is left between each day.
Day 1 schedule displayed in a "resutls" sheet:
Day Student Name Area pagers Time slot
Day 1 student 1 adult 2456 07:30 am
Day 1 student 3 pediatric 1234 07:30 am
Day 1 student 2 adult 2456 13:00 pm
Day 1 student 4 pediatric 1234 13:00 pm
Day 2 schedule displayed in a "resutls" sheet, and it could be:
Day Student Name Area pagers Time slot
Day 2 student 5 adult 2456 07:30 am
Day 2 student 7 pediatric 1234 07:30 am
Day 2 student 6 adult 2456 13:00 pm
Day 2 student 8 pediatric 1234 13:00 pm
Day 3 schedule displayed in a "resutls" sheet, and it could be:
Day Student Name Area pagers Time slot
Day 3 student 1 pediatric 1234 07:30 am
Day 3 student 3 adult 2456 07:30 am
Day 3 student 2 pediatric 1234 13:00 pm
Day 3 student 4 adult 2456 13:00 pm
2. a "missing" sheet was created to display the names of students who did not have an opportunity to try different areas and/or time slots on any day. Thus, the following value displayed
Student Name Area pagers Time slot
student 1 adult 1234 13:00 pm
student 1 pediatric 1234 13:00 pm
student 2 adult 1234 07:30 am
student 2 pediatric 1234 07:30 am
student 3 adult 2456 13:00 pm
student 3 pediatric 2456 13:00 pm
student 4 adult 2456 07:30 am
student 4 pediatric 2456 07:30 am
student 5 pediatric 1234 07:30 am
student 5 pediatric 1234 13:00 pm
student 6 pediatric 1234 07:30 am
student 6 pediatric 1234 13:00 pm
student 7 pediatric 1234 13:00 pm
student 8 pediatric 1234 07:30 am
3. a "Distrubation" Sheet was created to display the names of students who did have an opportunity to try different areas and/or time slots on any day. The schedule will be sorted out based on the student's name. Thus, the following value displayed
Student Name Day Area pagers Time slot
student 1 Day 1 adult 2456 07:30 am
student 1 Day 3 pediatric 1234 07:30 am
student 2 Day 1 adult 2456 13:00 pm
student 2 Day 3 pediatric 1234 13:00 pm
student 3 Day 1 pediatric 1234 07:30 am
student 3 Day 3 adult 2456 07:30 am
student 4 Day 1 pediatric 1234 13:00 pm
student 4 Day 3 adult 2456 13:00 pm
student 5 Day 2 adult 2456 07:30 am
student 6 Day 2 adult 2456 13:00 pm
student 7 Day 2 pediatric 1234 07:30 am
student 8 Day 2 pediatric 1234 13:00 pm
Please highlight each day and time slot with different colours.
Please create a complete code that runs smoothly in the Excel program without pending or causing it to fail, and explain the requirements of the code and conditions before writing the code to make sure that you understand the task.
inputs:
1. A list of students (Columns A)
2. A list of Areas and their corresponding pagers (Columns B and C)
3. A list of time slots (Column D)
4. list of days (Column F).
5. Maximum number of students in each shift {in a day} (specified in J1).
6. Maximum number of students in each shift {in a specific time slot} (specified in J2).
7. Maximum number of students in a specific area. (specified in J3).
Conditions:
1. Based on the number of days (input 4), the number of students in each sift (inputs 5 & 6), and the number of students in a specific area (input 7), students will be divided fairly and randomly in a way that each student will have a same number of shifts and days off.
2. Each Student will be required to visit each area at least 1 session based on the input Columns B and C.
3. Each Student will be required to try a different time slot based on the input Column D.
Clarification:
1. Students should have the same number of shifts and days off in between.
2. The distribution should be fair, and each student should have at least one opportunity (as possible) to try different areas and start times.
3. If the distribution can not be fair, and a student can not have an opportunity to try a specific area and/or start-time, please create another sheet called "missing" and specify those students and their areas and/or start-time that did not have from all distributions.
4. Multiple students can be in the same area or/and time slot (based on the inputs 4-7)
5. Columns have headers, therefore values start from the second row.
Results:
Students will be distributed based on the given criteria above and the results will be displayed in a schedule for each day. The schedule will be printed out based on the following:
1. Column A: name of student,
2. Column B: his areas
3. Column C: the corresponding pagers of his area
4. Coulmn D: his time slot.
Please note that the schedule will be sorted out based on the specified time slot (column D) for each day.
For example,
Inputs:
Column A: student 1, student 2, student 3, student 4, student 5, student 6, student 7, student 8
Column B: adult, pediatric,
Column C: 2456, 1234
Column D: 07:30 am, 13:00 pm
Column D: Day 1, Day 2, Day 3
J1: 4 "in aday"
J2: 1 "in time slot"
J3: 2 "in area"
Resutls:
1. a "result" sheet created, and a empty row is left between each day.
Day 1 schedule displayed in a "resutls" sheet:
Day Student Name Area pagers Time slot
Day 1 student 1 adult 2456 07:30 am
Day 1 student 3 pediatric 1234 07:30 am
Day 1 student 2 adult 2456 13:00 pm
Day 1 student 4 pediatric 1234 13:00 pm
Day 2 schedule displayed in a "resutls" sheet, and it could be:
Day Student Name Area pagers Time slot
Day 2 student 5 adult 2456 07:30 am
Day 2 student 7 pediatric 1234 07:30 am
Day 2 student 6 adult 2456 13:00 pm
Day 2 student 8 pediatric 1234 13:00 pm
Day 3 schedule displayed in a "resutls" sheet, and it could be:
Day Student Name Area pagers Time slot
Day 3 student 1 pediatric 1234 07:30 am
Day 3 student 3 adult 2456 07:30 am
Day 3 student 2 pediatric 1234 13:00 pm
Day 3 student 4 adult 2456 13:00 pm
2. a "missing" sheet was created to display the names of students who did not have an opportunity to try different areas and/or time slots on any day. Thus, the following value displayed
Student Name Area pagers Time slot
student 1 adult 1234 13:00 pm
student 1 pediatric 1234 13:00 pm
student 2 adult 1234 07:30 am
student 2 pediatric 1234 07:30 am
student 3 adult 2456 13:00 pm
student 3 pediatric 2456 13:00 pm
student 4 adult 2456 07:30 am
student 4 pediatric 2456 07:30 am
student 5 pediatric 1234 07:30 am
student 5 pediatric 1234 13:00 pm
student 6 pediatric 1234 07:30 am
student 6 pediatric 1234 13:00 pm
student 7 pediatric 1234 13:00 pm
student 8 pediatric 1234 07:30 am
3. a "Distrubation" Sheet was created to display the names of students who did have an opportunity to try different areas and/or time slots on any day. The schedule will be sorted out based on the student's name. Thus, the following value displayed
Student Name Day Area pagers Time slot
student 1 Day 1 adult 2456 07:30 am
student 1 Day 3 pediatric 1234 07:30 am
student 2 Day 1 adult 2456 13:00 pm
student 2 Day 3 pediatric 1234 13:00 pm
student 3 Day 1 pediatric 1234 07:30 am
student 3 Day 3 adult 2456 07:30 am
student 4 Day 1 pediatric 1234 13:00 pm
student 4 Day 3 adult 2456 13:00 pm
student 5 Day 2 adult 2456 07:30 am
student 6 Day 2 adult 2456 13:00 pm
student 7 Day 2 pediatric 1234 07:30 am
student 8 Day 2 pediatric 1234 13:00 pm
Please highlight each day and time slot with different colours.
Please create a complete code that runs smoothly in the Excel program without pending or causing it to fail, and explain the requirements of the code and conditions before writing the code to make sure that you understand the task.