Forum Discussion
Formula help: randomly assign students to groups based in criteria
Any help/advice gratefully received.
I am planning a school wide event. In it every student will choose four activities they would be interested to participate (on a Microsoft form). They will then be assigned one of their four options. Each activity has a max capacity.
So, what I want to do is create a formula/table etc that will assign students to an activity until that activity is full and then move on to their 2nd/3rd/4th option until they are all assigned something. Is that possible? If so how?
Any help would be amazing!
Hi Tkelly705 ,
See attached example of a solution that may help you with this.
The formula in column F counts how many students have been assigned to the current student's first choice, and if that is less than the max, he is assigned to his first option, if not we move on to check the second option, etc.
Regards,
Zach
8 Replies
Hi Tkelly705 ,
See attached example of a solution that may help you with this.
The formula in column F counts how many students have been assigned to the current student's first choice, and if that is less than the max, he is assigned to his first option, if not we move on to check the second option, etc.
Regards,
Zach
- This looks perfect - thank you so much!!!
Tkelly705 I know you already have a solution but I thought this was a real interesting challenge. So in the attached I created a complex set of LAMBDA functions include 2 that are recursively calling themselves to solve this challenge. In the attached workbook is an example:
The inputs to the main LAMBDA call include the RANGE with the selection table (must be 1 column as a unique ID followed by any number of picks) and a Table with the list of options and corresponding max # spots for that option.
This solution will:
A) randomize the order of the table
B) go down the 1st choices and allow up to the max # for each option
C) continue with each next lower choice columns
D) For EACH row IF that row did not match (i.e. all selected options were already max) then it will auto assign a non-full option based on which ever option has most spots open first. (an example of this is the last line in the image above where E was given even though they didn't select E as an preferred option)
E) Undo the randomized order so that the result correctly lines back up
F) I added in an addition output column to show the 'randomized' order that was used during the process (i found this helpful just for my sake to understand why/how each row ended up getting the choice they got)
NOTE: I didn't test for error cases like duplicate or missing entries and such so I hope you don't have any.
FYI: another interesting challenge if you guys are interested. I probably way over complicated it.
//a1 1,a2 2,a3 2，a4 3 like college entry？
select * from assign_students_to_activities;
create temp table aa as
select row_number() over () rank,* from assign_students_to_activities where instr(activities,'a1');
create temp table aa2 as
select 'a1' assign,* from aa where rank<=1;
select * from aa2;
//second round
create temp table bb as
select row_number() over () rank,* from assign_students_to_activities where instr(activities,'a2') and student not in (select student from aa2);
create temp table bb2 as
select 'a2' assign,* from bb where rank<=2;
select * from bb2;
//3
create temp table cc as
select row_number() over () rank,* from assign_students_to_activities where instr(activities,'a3') and student not in (
select student from aa2
union all
select student from bb2
);
create temp table cc2 as
select 'a3' assign,* from cc where rank<=2;
select * from cc2;
//4
create temp table dd as
select row_number() over () rank,* from assign_students_to_activities where instr(activities,'a4') and student not in (select student from aa2
union all
select student from bb2 union all select student from cc2);
select 'a4' assig
//a1 1,a2 2,a3 2，a4 3 select * from assign_students_to_activities; create temp table aa as select row_number() over () rank,* from assign_students_to_activities where instr(activities,'a1'); create temp table aa2 as select 'a1' assign,* from aa where rank<=1; select * from aa2; //second round create temp table bb as select row_number() over () rank,* from assign_students_to_activities where instr(activities,'a2') and student not in (select student from aa2); create temp table bb2 as select 'a2' assign,* from bb where rank<=2; select * from bb2; //3 create temp table cc as select row_number() over () rank,* from assign_students_to_activities where instr(activities,'a3') and student not in ( select student from aa2 union all select student from bb2 ); create temp table cc2 as select 'a3' assign,* from cc where rank<=2; select * from cc2; //4 create temp table dd as select row_number() over () rank,* from assign_students_to_activities where instr(activities,'a4') and student not in (select student from aa2 union all select student from bb2 union all select student from cc2); select 'a4' assign,* from dd where rank<=3;n,* from dd where rank<=3;
