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
 
Sort by date Sort by votes
Oops! Please see below for the full Class Specifications table. Not sure what happened there.

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
Chorus4thYear LongA or B
25​
2​
50​
1st Choice, 2nd Choice, 3rd Choice
Chorus5thYear LongA or B
25​
2​
50​
1st Choice, 2nd Choice
Band and Guitar5thYear LongA
20​
1​
20​
1st Choice, 2nd Choice, 3rd Choice
Band and Guitar4thYear LongB
20​
1​
20​
1st Choice, 2nd Choice, 3rd Choice, 4th Choice
Environmental Science4thSemesterA or B
18​
4​
72​
1st Choice, 2nd Choice, 3rd Choice, 4th Choice, 5th Choice
Environmental Science5thSemesterA or B
18​
4​
72​
1st Choice, 2nd Choice, 3rd Choice, 4th Choice
Dance4thYear LongA or B
30​
2​
60​
1st Choice
Dance5thYear LongA or B
30​
2​
60​
1st Choice
Theater4thYear LongA
20​
1​
20​
1st Choice
Theater5thYear LongA
20​
1​
20​
1st Choice, 2nd Choice
Theater4thSemesterB
20​
2​
40​
2nd Choice, 3rd Choice
Theater5thSemesterB
20​
2​
40​
3rd Choice, 4th Choice
 
Upvote 0

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
felixstraube said:
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
Click to expand...
That is a good thought. We already had the students complete the survey for next year and they are out for summer now, so don't think we can update for this round, but will make a note to consider that for next year. Currently, I tried to place students who had a year long course as their first choice in the year long course if possible, but depending on their other choices, they may end up in the semester long course to accommodate other choices. This part was more manual because it required looking at their other choices and the A/B availability to make decisions. But generally, if we are able to create some sort of algorithm, I would want to start with placing students with, for example, Robotics as their 1st choice in the year-long Robotics course vs. semester-long if it worked out.
 
Upvote 0
Hi, sorry for the long wait. I had little time this week.

Here is a first version: Rosters - VBA v0.1.zip

I used VBA to solve this. I didn't find a clean way to solve it any other way (it doesn't mean that there is not any, but it did not occure to me how).

So first of all i changed the Classes Specification table. And add one entry for every day with a column of day. That is if for example robotics had class for day A and day B, then you will have 2 rows. One for each day.
Then I also added a "# of assigned students" column where you store how many students are already assign to that class.

The students table is pretty much the same, i just added "Day A Class" anda "Day B Class", where you store to what class the student is assign for each day (A or B).

How to use it?
Delete the example data, add you real students information, adjust the table size to the right size if needed:
1718391764037.png


And hit the "Process" button at the top of the StudentsChoices sheet.

There are some issues which we could correct.
With some example data it occured that STEAM for example was assigned to a student for day A, and for day B, Band and Guitar Ensemble (which was is 2nd choice) could not be assigned because Band and Guitar Ensemble was only available on day A. But STEAM was available on both days. So we could switch the STEAM day so that 1st and 2nd choice are met.
But this will take me some time.
Try the example and let me know what you think.

Sheet "StudentChoicesRNG" is just to create random example data. You can delete it.
 
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,161
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