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

Google Sheets: TRANSPOSE, arrays, Randomize Ranges

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 off my with my classes and their assigned teacher:

Step 2

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

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:

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

For Example:

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 teacher’s 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}

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

 

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

 

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:

You will notice 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.

You will have a newly randomised set of numbers.

Step 7 – Sort your transposed data

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

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

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:

=TRANSPOSE(A16:M24)

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

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.

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

Did you enjoy the tutorial? Want to upskill and get a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.