22 people randomized into 4 groups

[msa969]

I am a teacher with little spare time at the start of a lesson, I like to do quizzes with my tutor group and wish to separate 22 people into 4 random groups, eg. table 1, 2,3,4. I wish to hit a button or something to do this. How can I do this?

I want each group to have a minimum of 5 people each time, and two groups will have 6. Example

student1
student2
student3
student4
student5
student6
student7
student8
student9
student10
student11
student12
student13
student14
student15
student16
student17
student18
student19
student20
student21
student22

Example: I do this manually and it takes me about 5 to 10 minutes to do at the start of each lesson

Please Login or Register  to view this content.

So in the above example group 1 and group 4 has 6 students.

I have attached the spreadsheet, worksheet 1 is called students, and worksheet 2 is called group

Please help,

[BadlySpelledBuoy]

Here is one possible solution using a couple of helper columns. One to generate a random number and one to rank those random numbers.
Then using an INDEX/MATCH you can use the ranks to look up the names. Press F9 to randomise again.

BSB

[BadlySpelledBuoy]

I realised there was a slight issue with the formulas in the previous version meaning names were repeated.
This one should work as expected.

BSB

[msa969]

Hi thanks what does the MATCH(ROWS($1:5)+COUNTA($A$27:B$32),$D$1:$D$22,0)) do?

[BadlySpelledBuoy]

INDEX/MATCH works as a lookup. The section of formula you're asking about is from cell C31 in the workbook and needs to look up the name in the list that corresponds to the number 10 (in the rank list).

ROWS($1:5) returns the number of rows included in the range of rows 1:5 which is of course 5.
COUNTA($A$27:B$32) returns the number of non blank cells in the cell range A27:B32 which in this workbook is also 5.
Add the two together and you get the required 10 for the lookup.

So the full formula for that cell is effectively:

Please Login or Register  to view this content.

In plain English this means, return the value from B1:B22 that corresponds to the position of 10 in range D1:D22.

If you can get your head around INDEX/MATCH as a lookup tool then it will, hopefully, all fall into place and make far more sense.

BSB