Create Student Rosters Based on Student Choices and Class Parameters

R

rostioschool

New Member
Joined
Jun 7, 2024
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hi,
I work at a school and am wondering if there is a way to use Excel to help us create student rosters for our enrichment classes for 4th and 5th graders. Students complete a survey ranking their choices from 1st choice to 14th choice (14 choices total). The classes are either year long or semester long, and each semester has an A and B schedule, so students will have an A and B enrichment each semester. There are also maximum class sizes. We'd like to place students in enrichments that meet their top choice preferences. The one exception is if a student is flagged as Spanish Cohort, they must be placed in that course. I am also wondering if we can use student demographic information to ensure the classes are diverse. Is it possible to do this in Excel? I have not tried to do anything like this before but have seen other threads doing something similar.
Here is an example of the class specifications:
ClassGrade LevelsYear Long or SemesterA/B DayMaximum Class SizeTotal Classes to CreateTotal Possible StudentsChoice Preference
Spanish5thYear LongA
20​
1​
20​
Spanish Cohort column is not blank, 1st Choice
Spanish4thYear LongB
20​
1​
20​
1st Choice, 2nd Choice
Orchestra4thYear LongA
20​
1​
20​
1st Choice, 2nd Choice, 3rd Choice, 4th Choice
Orchestra5thYear LongB
20​
1​
20​
1st Choice, 2nd Choice, 3rd Choice, 4th Choice
STEAM4thSemesterA or B
20​
4​
80​
1st Choice, 2nd Choice, 3rd Choice
STEAM5thSemesterA or B
20​
4​
80​
1st Choice, 2nd Choice, 3rd Choice
Photography4thSemesterA or B
18​
4​
72​
1st Choice, 2nd Choice
Photography5thSemesterA or B
18​
4​
72​
1st Choice
PE4thSemesterA or B
20​
4​
80​
1st Choice
PE5thSemesterA or B
20​
4​
80​
1st Choice
Technology4thSemesterA or B
20​
4​
80​
1st Choice, 2nd Choice
Technology5thSemesterA or B
20​
4​
80​
1st Choice, 2nd Choice, 3rd Choice
Visual Art4thYear LongA
20​
1​
20​
1st Choice, 2nd Choice, 3rd Choice
Visual Art5thYear LongA
20​
1​
20​
1st Choice, 2nd Choice, 3rd Choice
Visual Art4thSemesterB
20​
2​
40​
3rd Choice, 4th Choice, 5th Choice
Visual Art5thSemesterB
20​
2​
40​
3rd Choice, 4th Choice, 5th Choice
Engineering4thSemesterA or B
20​
4​
80​
1st Choice, 2nd Choice, 3rd Choice
Engineering5thSemesterA or B
20​
4​
80​
1st Choice, 2nd Choice, 3rd Choice
Robotics4thSemesterA
20​
2​
40​
2nd Choice, 3rd Choice
Robotics5thSemesterA
20​
2​
40​
3rd Choice, 4th Choice
Robotics4thYear LongB
20​
1​
20​
1st Choice
Robotics5thYear LongB
20​
1​
20​
1st Choice, 2nd Choice

Here is an example of the student choices and demographics I'd like to consider when rostering. There are about 300 students total.
Student IDGradeBand and Guitar EnsembleChorusDanceEngineeringEnvironmental ScienceOrchestraPERoboticsSpanishTechnologyTheaterVisual ArtSTEAMPhotographySpanish CohortGenderRaceSWD
6375444​
4th6th Choice4th Choice2nd Choice9th Choice7th Choice13th Choice1st Choice12th Choice5th Choice8th Choice3rd Choice10th Choice11th Choice14th ChoiceFBlackY
6349179​
4th10th Choice12th Choice13th Choice11th Choice14th Choice8th Choice1st Choice7th Choice5th Choice6th Choice2nd Choice3rd Choice9th Choice4th ChoiceFBlackN
6348012​
4th2nd Choice11th Choice14th Choice7th Choice10th Choice8th Choice5th Choice1st Choice6th Choice3rd Choice12th Choice4th Choice9th Choice13th ChoiceMBlackN
6457206​
4th13th Choice14th Choice9th Choice7th Choice11th Choice10th Choice1st Choice2nd Choice5th Choice3rd Choice4th Choice6th Choice12th Choice8th ChoiceMBlackN
6376626​
4th7th Choice2nd Choice1st Choice9th Choice11th Choice6th Choice14th Choice5th Choice10th Choice4th Choice3rd Choice13th Choice12th Choice8th ChoiceFWhiteN
6375507​
4th10th Choice11th Choice13th Choice6th Choice12th Choice9th Choice3rd Choice4th Choice14th Choice5th Choice7th Choice8th Choice2nd Choice1st ChoiceMWhiteN
6354327​
4th11th Choice6th Choice13th Choice4th Choice9th Choice1st Choice3rd Choice2nd Choice14th Choice12th Choice5th Choice7th Choice8th Choice10th ChoiceMBlackN
6357003​
5th9th Choice8th Choice14th Choice2nd Choice13th Choice12th Choice1st Choice10th Choice7th Choice3rd Choice6th Choice5th Choice11th Choice4th ChoiceMBlackY
6312744​
5th12th Choice13th Choice14th Choice3rd Choice5th Choice11th Choice1st Choice2nd Choice10th Choice4th Choice7th Choice6th Choice9th Choice8th ChoiceMHispanicN
6324813​
5th3rd Choice4th Choice14th Choice8th Choice10th Choice13th Choice12th Choice9th Choice11th Choice7th Choice6th Choice5th Choice2nd Choice1st ChoiceFWhiteN
6376623​
4th13th Choice8th Choice3rd Choice14th Choice12th Choice11th Choice1st Choice2nd Choice6th Choice9th Choice4th Choice7th Choice10th Choice5th ChoiceMBlackN
6374700​
4th7th Choice9th Choice1st Choice5th Choice10th Choice11th Choice6th Choice4th Choice12th Choice13th Choice2nd Choice8th Choice14th Choice3rd ChoiceFBlackN
6428055​
4th13th Choice12th Choice5th Choice2nd Choice11th Choice10th Choice4th Choice9th Choice14th Choice8th Choice1st Choice7th Choice3rd Choice6th ChoiceFBlackN
6375549​
4th5th Choice13th Choice6th Choice4th Choice9th Choice3rd Choice1st Choice2nd Choice11th Choice10th Choice8th Choice7th Choice14th Choice12th ChoiceMBlackN
6312330​
5th7th Choice13th Choice14th Choice10th Choice6th Choice12th Choice11th Choice1st Choice2nd Choice4th Choice9th Choice3rd Choice8th Choice5th ChoiceSpanish/Grade 4 ExposureMWhiteN
6349575​
5th11th Choice5th Choice14th Choice3rd Choice8th Choice2nd Choice13th Choice4th Choice12th Choice1st Choice7th Choice9th Choice10th Choice6th ChoiceFWhiteN
6347526​
4th2nd Choice12th Choice13th Choice9th Choice7th Choice6th Choice5th Choice1st Choice14th Choice10th Choice3rd Choice8th Choice11th Choice4th ChoiceMMultiracialY
6375516​
4th2nd Choice3rd Choice14th Choice5th Choice12th Choice10th Choice6th Choice4th Choice9th Choice11th Choice13th Choice7th Choice1st Choice8th ChoiceMAsianN
6353334​
4th11th Choice10th Choice14th Choice8th Choice13th Choice5th Choice2nd Choice7th Choice12th Choice1st Choice4th Choice6th Choice9th Choice3rd ChoiceFBlackN
6390915​
4th14th Choice10th Choice6th Choice7th Choice13th Choice12th Choice1st Choice3rd Choice2nd Choice9th Choice5th Choice8th Choice11th Choice4th ChoiceMBlackN
6457185​
4th10th Choice13th Choice4th Choice7th Choice8th Choice6th Choice2nd Choice3rd Choice12th Choice11th Choice1st Choice5th Choice9th Choice14th ChoiceMBlackN
6348165​
4th9th Choice7th Choice5th Choice6th Choice10th Choice8th Choice14th Choice2nd Choice12th Choice1st Choice4th Choice11th Choice3rd Choice13th ChoiceMWhiteN
6320427​
5th1st Choice12th Choice14th Choice5th Choice8th Choice7th Choice3rd Choice2nd Choice13th Choice10th Choice9th Choice11th Choice6th Choice4th ChoiceSpanish/Grade 4 ExposureMBlackN
6348981​
5th9th Choice5th Choice14th Choice4th Choice3rd Choice11th Choice1st Choice2nd Choice12th Choice7th Choice6th Choice13th Choice8th Choice10th ChoiceMBlackN
6374757​
4th14th Choice5th Choice6th Choice13th Choice8th Choice7th Choice9th Choice12th Choice4th Choice10th Choice3rd Choice2nd Choice11th Choice1st ChoiceFWhiteN
6351690​
4th9th Choice8th Choice14th Choice5th Choice6th Choice7th Choice4th Choice12th Choice13th Choice3rd Choice11th Choice1st Choice10th Choice2nd ChoiceFWhiteN
6348369​
4th8th Choice11th Choice12th Choice10th Choice4th Choice6th Choice14th Choice3rd Choice9th Choice2nd Choice13th Choice1st Choice5th Choice7th ChoiceMWhiteN
6312732​
5th5th Choice1st Choice2nd Choice14th Choice8th Choice6th Choice13th Choice9th Choice7th Choice10th Choice11th Choice12th Choice4th Choice3rd ChoiceFWhiteN
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
Hi, im looking into this. It seems challenging.
Can you explain what the column "Choice Preference" in the Class specifications table is for? Or how should this information be used?
 
Upvote 0
Thank you! Yes, Choice Preference was my attempt at specifying how to prioritize students for each class. Generally, it is based on 1st, 2nd, 3rd choice etc., but there were a few caveats, like if a 5th grader has the Spanish cohort indicator in the student file, they need to be placed in the Spanish class. Or, since some classes like Visual Arts have a year long and semester long option, I wanted students with Visual Arts as their top preference to fill up the year long class first if possible, and then other students fill up the semester long option. I know there are a lot of different criteria to consider. If it's helpful, below is a sample of my current output where I am kind of doing this manually using Stata. The hard part is that the output needs to also ensure that each student is scheduled for an A and B course each semester after rosters are set, so essentially each student will have Fall A, Fall B, Spring A, and Spring B by the end of this. Let me know if anything doesn't make sense!
 
Upvote 0
Thanks for the answer. I still dont quite understand how you use the information in the column "Choice Preference"
For example if for the Photography class it says:

1st Choice, 2nd Choice

Does this mean that in this class are only admitted students that have priority 1 or 2 for Photography?
If that's true, and considering that in "Choice preference" you have values from 1 to 5, why are you giving the students 14 choices if you only admit students from 1 to 5?

If it says

3rd Choice, 4th Choice

Does this mean that students that have that class as 1st or 2nd choice are not admitted?
 
Upvote 0
Sorry for the confusion! No, the Choice Preferences do not necessarily preclude students who don't meet those criteria from being in the class. It was essentially my way of trying to delineate who would get top priority as much as possible, if I could figure out a way to set those types of rules (so for instance, for Visual Arts year long, try to prioritize students who listed Visual Arts as 1st or 2nd Choice before placing others, but it wouldn't prevent me from placing others if needed). For all intents and purposes for what I'm hoping to accomplish here, the Choice Preference column can probably be ignored.
 
Upvote 0
One more question.
If you have for example this class:

ClassGrade LevelsYear Long or SemesterA/B DayMaximum Class SizeTotal Classes to CreateTotal Possible Students
STEAM4thSemesterA or B20480


Does this mean that you have place for 40 students on day A and 40 students on day B?
 
Upvote 0
Hi, yes sorry for the confusion! The A/B schedules make it more complex. For this class example, it means that each class still has a max of 20 students, but there can be 4 possible classes for the year: Fall A, Fall B, Spring A, and Spring B for 4th graders (each with a max of 20 students). Alternatively, for Orchestra, since it's yearlong, 4th grade can only have one class of 20 students for the whole year, and those students will be scheduled for Orchestra for Fall A and Spring A. The last potential option is courses like Visual Arts, where there is a year-long option and semester-long option. For 4th grade, this means 20 students can be scheduled for year-long Visual Arts (Fall A and Spring A), another 40 can be scheduled for semester-long Visual Arts (20 during Fall B, and 20 during Spring B). Let me know if this makes sense! If I need to rearrange the class specifications table so it's more clear, I can.
 
Upvote 0
Thanks. It's clear. I just wanted to be sure.

Another thing.
I think the classes specificacion table is not complete.

Students ChoicesClasses Table
1Band and Guitar Ensemble
2Chorus
3Dance
4EngineeringEngineering
5Environmental Science
6OrchestraOrchestra
7PEPE
8RoboticsRobotics
9SpanishSpanish
10TechnologyTechnology
11Theater
12Visual ArtVisual Art
13STEAMSTEAM
14PhotographyPhotography


We have 14 clases in the stundent choice table but only 10 in the specificacion table.
 
Upvote 0
One more question.
How do you decide if a student takes the Year long class or the semester class?
Would it make sense to add one more option to the choice list?
For visual arts for example instead of this:

Student IDGradeOther classes…Visual ArtSpanish CohortGenderRaceSWD
54th…1Spanish classFBlackN


You add a column like this:

Student IDGradeOther classes…Visual Art - SemestralVisaul Art - AnnualSpanish CohortGenderRaceSWD
54th…12Spanish classFBlackN


This way they would have one more choice per class that is either year long or semester long.

The same for any other class that is either semestral or year long
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Applying date ordinal to an array of dates.
Replies
2
Views
140
HighAndWilder
H
A
  • Question Question
Calculate variance from combined rows with nested loop for all columns (VBA)
Replies
1
Views
978
anatomyst
A
MrDB4Excel
  • Question Question
Populate Date to Another Workbook
Replies
7
Views
169
MrDB4Excel
MrDB4Excel
J
  • Question Question
Fantasy Football Standings with multiple scoring points
Replies
1
Views
1K
jdellasala
J
MrDB4Excel
  • Solved
Get the Correct Month
2
Replies
10
Views
173
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,820
Messages
6,181,162
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top