Student Allocations

[DesiMcK]

Hi

I have a tricky problem that I cannot solve and I hope someone will be able to assist me. Thank you in advance for any assistance.

We are running two sets of extra-curricular lessons and students have chosen two of out 23 options. Each option has maximum and I need to allocate fairly. If a students does not get their first choice, they should get their First Reserve Choice or teir Second Preferred Choice.

To complicate things, some sessions will only run once - details are on the attached sheet. Essentially, there are two times for the sessions to run - May and June. Some will run in May and then again in June. Some will only run once. A student who chooses an option can be placed in either the May or June sessions.

I have uploaded a sheet with the details.

Again, I appreciate everyone's help with this.

Thank you

Desi

ALLOCATIONS.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name	CHOICE 1	CHOICE 2	RESERVE 1	RESERVE 2						Maximum Number	May	June
2	PUPIL 1	Cake Making and Decorating	Debating club	British Sign Language	Dissection Club					African Dance	25	Y	N
3	PUPIL 2	Cake Making and Decorating	Fitness	Animation	Mountain Biking					Animation	25	Y	Y
4	PUPIL 3	Cake Making and Decorating	Clay Name plates	Fitness	Sketchup - 3D CAD Software used by Architects					Bootcamp	25	Y	Y
5	PUPIL 4	Fitness	Boxercise	Dissection Club	Mountain Biking					Boxercise	25	Y	Y
6	PUPIL 5	Circus Skills	Boxercise	Dissection Club	Mountain Biking					British Sign Language	25	Y	Y
7	PUPIL 6	Cake Making and Decorating	Animation	Fitness	Mountain Biking					Build your own computer	25	Y	Y
8	PUPIL 7	Mindfulness Gardens	Animation	Clay Name plates	Creative Writing for Fun					Cake Making and Decorating	20	Y	Y
9	PUPIL 8	Mindfulness Gardens	Psychology - Optical Illusions	Cake Making and Decorating	Creative Writing for Fun					Changing Tides	25	Y	Y
10	PUPIL 9	Cake Making and Decorating	Mindfulness Gardens	Mountain Biking	Clay Name plates					Circus Skills	25	N	Y
11	PUPIL 10	Psychology - Optical Illusions	Creative Writing for Fun	British Sign Language	Cake Making and Decorating					Clay Name plates	25	N	Y
12	PUPIL 11	Animation	Cake Making and Decorating	Creative Writing for Fun	Mountain Biking					Creative Writing for Fun	25	Y	Y
13	PUPIL 12	Cake Making and Decorating	Sports Business and Enterprise	Dissection Club	Yoga					Cultural Appreciation Across the World	25	N	Y
14	PUPIL 13	Cake Making and Decorating	Animation	Fitness	Mountain Biking					Debating club	25	Y	Y
15	PUPIL 14	Cake Making and Decorating	Debating club	Circus Skills	Clay Name plates					Dissection Club	25	Y	Y
16	PUPIL 15	Animation	Cake Making and Decorating	British Sign Language	Mandarin Chinese					Don't Be Trashy	25	Y	Y
17	PUPIL 16	British Sign Language	Animation	Mandarin Chinese	Creative Writing for Fun					Fitness	25	Y	Y
18	PUPIL 17	Animation	Build your own computer	Sketchup - 3D CAD Software used by Architects						Mandarin Chinese	25	Y	Y
19	PUPIL 18	Debating club	Circus Skills	British Sign Language	Cake Making and Decorating					Mindfulness Gardens	25	Y	Y
20	PUPIL 19	Psychology - Optical Illusions	Sketchup - 3D CAD Software used by Architects	Build your own computer	Animation					Mountain Biking	25	Y	Y
21	PUPIL 20	Sketchup - 3D CAD Software used by Architects	Psychology - Optical Illusions	Cake Making and Decorating	Clay Name plates					Psychology - Optical Illusions	25	Y	Y
22	PUPIL 21	Cake Making and Decorating	Sketchup - 3D CAD Software used by Architects	Psychology - Optical Illusions	Dissection Club					Sketchup - 3D CAD Software used by Architects	25	Y	Y
23	PUPIL 22	Cake Making and Decorating	Sketchup - 3D CAD Software used by Architects	Psychology - Optical Illusions	Dissection Club					Sports Business and Enterprise	25	Y	N
24	PUPIL 23	Cake Making and Decorating	Dissection Club	Psychology - Optical Illusions	Clay Name plates					Yoga	25	Y	Y
25	PUPIL 24	Mandarin Chinese	Clay Name plates	Circus Skills	British Sign Language
26	PUPIL 25	Mandarin Chinese	Clay Name plates	Circus Skills	British Sign Language
27	PUPIL 26	Mandarin Chinese	Psychology - Optical Illusions	Dissection Club	British Sign Language
28	PUPIL 27	Mandarin Chinese	Cultural Appreciation Across the World	Don't Be Trashy	Debating club
29	PUPIL 28	Mandarin Chinese	Cake Making and Decorating	Don't Be Trashy	Mindfulness Gardens
30	PUPIL 29	Debating club	Cake Making and Decorating	Don't Be Trashy	Psychology - Optical Illusions
31	PUPIL 30	Cake Making and Decorating	Cake Making and Decorating	Cake Making and Decorating	Cake Making and Decorating
32	PUPIL 31	Cake Making and Decorating	Clay Name plates	Dissection Club	Circus Skills
33	PUPIL 32	Cake Making and Decorating	Circus Skills	British Sign Language	Clay Name plates
34	PUPIL 33	Cake Making and Decorating	Clay Name plates	Clay Name plates	Build your own computer
35	PUPIL 34	Animation	Build your own computer	Cake Making and Decorating	Psychology - Optical Illusions
36	PUPIL 35	Cake Making and Decorating	Clay Name plates	Creative Writing for Fun	African Dance
37	PUPIL 36	Cake Making and Decorating	Fitness	Clay Name plates	African Dance
38	PUPIL 37	Circus Skills	Cake Making and Decorating	Clay Name plates	British Sign Language
39	PUPIL 38	Sports Business and Enterprise	Boxercise	Cake Making and Decorating	African Dance
40	PUPIL 39	Build your own computer	Fitness	Animation	Psychology - Optical Illusions
41	PUPIL 40	British Sign Language	Creative Writing for Fun	Clay Name plates	Psychology - Optical Illusions
42	PUPIL 41	Build your own computer	Sketchup - 3D CAD Software used by Architects	Fitness	Sports Business and Enterprise
43	PUPIL 42	Build your own computer	Sketchup - 3D CAD Software used by Architects	Fitness	Sports Business and Enterprise
44	PUPIL 43	Sketchup - 3D CAD Software used by Architects	Fitness	Fitness	Boxercise
45	PUPIL 44	Mountain Biking	Build your own computer	Cake Making and Decorating	African Dance
46	PUPIL 45	Mountain Biking	Build your own computer	Fitness	Sports Business and Enterprise
47	PUPIL 46	Fitness	Circus Skills	Build your own computer	Dissection Club
48	PUPIL 47	Creative Writing for Fun	Cake Making and Decorating	Circus Skills	Clay Name plates
49	PUPIL 48	Cake Making and Decorating	Sketchup - 3D CAD Software used by Architects	Build your own computer	Cake Making and Decorating
50	PUPIL 49	Build your own computer	Animation	Clay Name plates	Creative Writing for Fun
51	PUPIL 50	Cake Making and Decorating	Circus Skills	Mindfulness Gardens	British Sign Language
52	PUPIL 51	Dissection Club	Psychology - Optical Illusions	Yoga	British Sign Language
Sheet1

 

[DesiMcK]

hi

Can anyone point me in the right direction to start this please?


Thanks


Desi

 

[KRice]

I have an idea using Power Query to perform the allocations, but need to finish it (hopefully today). I have a question about your "allocate fairly" comment. "First come, first served" is one approach, but I'm assuming you receive the preferences from everyone at the same time, so a different approach is desired? I've taken the approach that if the 1st Choice cannot be granted to everyone requesting a seat in the same course, then everyone is assigned a random number, the random numbers are sorted, and if we suppose that there are only 20 seats available, then the 20 smallest random numbers are selected and that subset of students will be given their 1st Choice. The remaining students then become the group to consider for their 2nd Choice...and so on.

As I worked through an algorithm, I also wondered if there was a lower constraint on class size? You've mentioned a maximum number per session, but is there some minimum level of enrollment below which you would cancel the class?

 

[DesiMcK]

I have an idea using Power Query to perform the allocations, but need to finish it (hopefully today). I have a question about your "allocate fairly" comment. "First come, first served" is one approach, but I'm assuming you receive the preferences from everyone at the same time, so a different approach is desired? I've taken the approach that if the 1st Choice cannot be granted to everyone requesting a seat in the same course, then everyone is assigned a random number, the random numbers are sorted, and if we suppose that there are only 20 seats available, then the 20 smallest random numbers are selected and that subset of students will be given their 1st Choice. The remaining students then become the group to consider for their 2nd Choice...and so on.

As I worked through an algorithm, I also wondered if there was a lower constraint on class size? You've mentioned a maximum number per session, but is there some minimum level of enrollment below which you would cancel the class?

Oh my! I cannot express my gratitude for your help.

To answer your questions, minimum size 20 please.

Fairly allocate - I’d be happy with a random allocation as it was not first come first serve. All students completed the form at different times depending on computer availability.

Again, I am very grateful for your help.


Desi

 

[KRice]

This is tricky. A minimum size of 20? So if we have 15 students signed up for the same course X as their Choice 1 (I'll call these 15 students "Group A"), you would deny all of them and say that the minimum has not been met? Choice 2 would then be considered for the Group A students. Suppose we have another group of students (Group B) whose individual Choice 1's also could not be fulfilled...so their Choice 2 would then be considered. Now suppose that 5 of the Group B students happen to have selected course X as Choice 2. Do we then step back near the beginning and check if the minimum is met by compiling all of the denials for higher preference selections to determine if a minimum class size constraint can be satisfied? If so, in this case, the 15 Group A students (Choice 1 is course X) and the 5 Group B students whose Choice 2 is course X would satisfy the minimum size constraint of 20 and therefore would be enrolled in course X. I don't know if I can get this working correctly in Power Query, but I'll have a look.

 

[DesiMcK]

I am so sorry - I typed my previous reply on my phone and I must have big fingers!!

Minimum should be 10, not 20. However, if easoer, ignore the minimum siz - I can manually adjust if needed.

Sorry for the confusion.

 

[KRice]

Desi,
I have a file for you to try, but since the Power Query code is tied into multiple worksheets, it is easier to make the file available here for download:

MrExcel_20220405.xlsx

Shared with Dropbox

www.dropbox.com

Please review the ReadMe worksheet, as it explains the general approach, some issues encountered, and instructions for running the queries to perform the allocations to match students to courses. You may find that some students are not assigned to courses. This can occur for a variety of reasons described in the ReadMe worksheet, and those students would have been denied seats in each of the four iterations. Details of those iterations can be reviewed on a summary worksheet if you examine the [Allocation.Type] column for each iteration. Students left without a course allocation will be identified in the final results table on the Desi worksheet with a "0" for their course allocation. In the linked file, I expanded the number of students to 151 and randomly generated a weighted distribution of courses for the additional students to see how the queries performed with a larger data set. As described in the ReadMe worksheet, this approach is somewhat clunky because it requires manual intervention by the user to refresh the queries. I was getting unreliable results when I combined all of the steps in one sequence, probably because the automatic random number generation creates a feedback loop of sorts. I think I've tamed that behavior with this approach, albeit at the expense of requiring more user involvement. I'm hopeful that it works for your purposes. Let me know if you encounter any problems.

 

[DesiMcK]

Wow! I cannot explress my grtitude. I have been able to use your file and this has simplified the process for me.

Please accept my sincerely thanks for all the time and expertose you have dedicated to this.

Desi

 

[KRice]

That's good to hear...thanks for the update. I'm curious about the scale of your allocation effort. Approximately how many courses and how many students signing up in total?

 

[DesiMcK]

295 students with 22 courses running. I had to make a few manual decisions to get things to work but your spreadsheet did most of the work.

Many thanks again


Desi