How do I Randomly Assign People to a Group Without Doubling Up on the Same Group in Google Sheets

Combinations non repeating

Random Combinations Without Repetition Using Arrays, TRANSPOSE and Randomize Ranges

Say you have a list of  30 people and a 30 list of groups. After every half hour for six hours the people must change to another group, but they cannot go back to a group they have already been in.

How can we schedule 12 sessions where every person can randomly go to another group without going to the same group twice?

This is pretty much the same issue I face when scheduling my teachers to proctor exams randomly each quarter for different classes. The rules I need to follow are:

  1. No teacher should proctor the same class twice.
  2. Assignment to proctor a class should seem random.
  3. The teacher that teaches the class cannot proctor their own class.
  4. Each week has an exam over the term. The term runs for 8 weeks plus a final requiring a total of 9 proctor sessions.

The Solution

Step 1

I’ll start of my with my classes and their assigned teacher:

Classes and their teachers

Step 2

Along Row 2 I will list out the 9 exam sessions I need.

Initial Setup of Teachers for each class

Step 3 – Arrays

Next I am going to reorder my list so that all the teachers from B4:B14 will move up a row and the teacher in B3 will move to the bottom. I could do this by carefully copying and pasting each column, but that is time consuming.

This is where the magic kicks in: Arrays.

In Google Sheets, we can create an array of data in one cell and that will paste a whole list either vertically by row or horizontally by column.

In Google Sheets, to create an array you simply do the following:

A list down ROWS ={value1; value2; value3}

For Example:

Google Sheet Row Array

A list across COLUMNS = ={value1, value2, value3}

For Example:

Google Sheet Column Array

In both these examples the Array formula is added to the A1 cell only.

Back to the proctor list. When these arrays become truly powerful is when we refer to other cells.

In our example I am going to make an array in cell C3 based on the teachers list from B4:B14 and then I add B3 to the end of the array. The formula will look a little something like this:

={B4:B14;B3}

Combined Array

Hitting <Enter> will reveal the following array for the first session.

 

Combined Array 2

Next, grab that little box on the bottom right of cell C3 and drag it all the way across to cell K3.

 

9 combinations without repitition

You know have 9 unique lists of teachers that do not proctor 1 class more than once.

Step 4 – Transpose

This is great, but the students are gonna pretty quickly figure out who will be proctoring their exam the following week. We need to randomise things a bit.

First we need to grab the 9 numbers in C2:K14 and TRANSPOSE them so they are vertical in a column.

To do this, click on cell A16 and add the following formula:

=TRANSPOSE(C2:K14)

Hit <Enter> and you will get the following list:

Google Transpose Result

You will noticed that the data has now turned 90 degrees.

Step 5 – Randomize Range

Next we are going to randomise these numbers before putting them back across the top row.

Select all the newly transposed data: A16M24, hit CTRL + C to copy and then CTRL + SHIFT + V to paste as value(without the formula).

Select A16:A24 again and  go to the Data dropdown and click Randomize Range.

Google Randomize Range

You will have a newly randomised set of numbers.

Google Randomize Range Result

Step 7 – Sort your transposed data

Select all the transposed data from A6:M24. Got to the Data dropdown and click Sort range.

Google Sort Range

A popup will appear. We want to sort by column A so click Sort.

Google Sort by Column A

Step 6 – Transpose the numbers back to Row 1. 

First delete all the data we created in the Arrays (Step 3) from C2 to K14. We are going to transpose the data in A16M24 back into the C2 Cell. The formula is as follows:

=TRASPOSE(A16:M24)

transpose back results

Hit <Enter> and now you have a random order for your list of teachers. To proctor on each week.

transpose back results2

Step 7: Tidy Up

Select all the newly transposed data A2:K14, copy and paste as values.

Select all the old transposed data A14:M24 and delete it.

Combinations non repeating

That’s it you now have a proctor lists for each well with non-repeating combinations.

Leave a Reply