Sorting students into groups with preferences

[Apmtz]

Good afternoon,
I am a teacher assistant working on creating an automatic grouping workbook on excel. I have the preferences of the students already and need help grouping them. Attached is the worksheet with all student's topic options and voting results. If anyone can help me it would be much appreciated, thank you!

[Gregb11]

I don't understand. What's your question? Can you mock up the results you want to see? How do you want to group the students?

[Apmtz]

I would like to group the students by topics such as Nuisance, GMO, Water, Onions, Pesticide, Immigration, Beef, Animal Welfare. They have put their preferences from 1-8 with 1 being their most desirable topic and 8 being their least desirable topic. I know I could do it manually but would like to learn how to do this if I am presented with a larger data set.

[Gregb11]

But what do you want the results to look like? What do you mean by "group the students by topics"? Take only a few students and mock up the results you'd like to see and then we can probably present you with a formula that will work for as many students as you like.

[janmorris]

please provide an example spreadsheet that has been grouped (manually), along with explanation how you manually calculated/formulated your grouping.

[Apmtz]

Ok, I have attached the file with how I would group the students manually. What I did was went to each student starting from the top and looked at which topic was most desirable to that student. Again 1 being most desired and 8 being least desired. I decided that there would be a group with 1 student being that there were 8 groups with 15 students. Then, I started with the first student and gave them their most desired topic until the group was full. Once a group was full and I was unable to add any more students, I gave the remaining students their second most desirable choice and so on until all groups were full.

[JeteMc]

This is not as automatic as I imagine you would like and perhaps it will inspire someone to come up with a better proposal.
1. Produce a list of topics as modeled in L7:L14
2. Populate M7:M14 using: =COUNTIFS(J$3:J$17,L7)
3. Populate N7:N14 using: =IFERROR(INDEX(L$7:L$14,AGGREGATE(15,6,(ROW(L$7:L$14)-ROW(L$6))/(M$7:M$14<M$6),ROWS(N$7:N7))),"")
Note that cell M6 contains the desired group size.
4. Populate J3:J17 using data validation drop downs that have the following source: =OFFSET($N$7,0,0,SUMPRODUCT(--($N$7:$N$14<>"")))
Once a topic has been selected equal to the desired group size it will be removed from column N and the drop downs.
As the number of drop down options decreases it is a bit easier to select the topic for the student.
Note that I banded the rows as it made it a bit easier to keep up with the values I was evaluating.
5. The names in L3:S4 are populated using: =IFERROR(INDEX($A$3:$A$17,AGGREGATE(15,6,(ROW($A$3:$A$17)-ROW($A$2))/($J$3:$J$17=L$1),ROWS(L$3:L3))),"")
Note that L1:S1 are populated with group names copied from B2:I2.
Let us know if you have any questions.

[Apmtz]

Thank you so much Jete! I am taking a look at it right now.

[menem]

My result is different from your a bit.

by adding many of helper columns.

K3
=IF(COUNTIF(V$2:V2,"x")<2,B3,"-")
copy throu R17

T3
=MIN(K3:R3)
copy throu T17

V3
=IF(B3=$T3,"x","")
copy throu Ac17

Result area
AE11
=IFERROR(INDEX($A$2:$A$17,AGGREGATE(15,6,(ROW($A$2:$A$17)-ROW($A$1))/(1/(V$2:V$17="x")),COUNTA(AE$10:AE10))),"")
copy throu AL12

Regards.