Formula help: randomly assign students to groups based in criteria

[Tkelly705]

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! 

[#2]

//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;

[#3]

Thank you so much - this is really kind, but it's beyond me. I don't understand half of what you have written! I think I'll have to stick with manual entry unless I can find a template. 

Thanks again thought. 

@peiyezhu 

[#4 · Accepted solution]

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

[#5]

This looks perfect - thank you so much!!!

[#6]

@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. 

@Patrick2788 

@Peter Bartholomew 

@SergeiBaklan 

[#7]

Since you seem to like a challenge, here's my related conundrum. 
My school does a choice day where students input their top choices (previously we've just said to do top 4 but we could have them pick top 5 or 6 if it helped with the coding/grouping) and then students are assigned to 4 different sessions throughout the day based on their requests. For example, one student might pick Choice 1, 3, 5, 6 and 9 and need to be put into four sessions based on those selections. They might end up in Choice 3 for their first station, choice 1 for their second, and then have to get a random option for their third and fourth stations because all their other choices filled up. 

Totally understandable if that's too many levels of complex but I thought I'd throw it out there! We've had the same one teacher organize the results by hand for the whole school every year and I'd love to help simplify the process if possible. Open to suggestions!

Thanks!

[#8]

I do like a challenge but I've been real busy with my real job lately.  That said I think what I created already is really close to what you need.  A few questions:

a) do you like the way my previous answer finds the 1st choice answer above? (I'm guessing so)

b) on the 2nd choice I see calling the above but basically saying the student can't be re-assigned the same activity.

c) now comes the big choice, after the 1st choice is made, what order should we use for the 2nd choice (and then 3rd, 4th, etc).  Three options that come to mind are: a) use the same random order, b) create a new random order each time, c) use the reverse of the prior order (this reminds me of fantasy draft order, lol)

[#9]

They might end up in Choice 3 for their first station, choice 1 for their second,

and then have to get a random option for their third and fourth stations because all their other choices filled up. 

filled up?

Session A becomes unavailable like below?

https://answers.microsoft.com/en-us/msoffice/forum/all/how-do-i-create-an-event-registration-system-...