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

 

[KRice]

I'm happy to help. I would expect some manual clean up to be required, especially for those students who end up with no course allocation after the 4th iteration. One idea is to set all of the course minimums to 0 and run the queries, then assess whether those courses with modest enrollments need to be canceled and the enrollees reassigned to other courses. By doing that, someone who is denied a seat early on in a course that eventually meets the minimum threshold wouldn't be penalized. But if you try that approach, know that you'll overwrite your previous efforts, and the list of allocations will change for classes where there is competition for seats. I'm glad to hear you have a solution in hand.