Rotating unique groups with no repeat
This article demonstrates a formula
What's on this page
Kristina asks:Hi Oscar,Your formula works great, however, I was wondering if there is capability to add another countif criteria so that it produces a random unique number different from the one above AS WELL AS the one to the left.I have 7 groups, 7 tables, and 7 rotations for which each group will move to a different table.
With your formula, I am able to successfully assign each group a random order of the 7 tables to rotate too with no repeats (however I am unable to guarantee that for each rotation only one group is at each table), OR I am able to produce a schedule that has max one group per table per rotation, but then I am not able to have each group have no repeats for the tables they are rotating too.
Is there a way to tweak the formula to satisfy both requirements where each group has a random 1-7 table rotation, AND for each rotation there is only one group per table? Thanks!! Appreciate the help.
Answer
This is not as easy as it may seem. I first tried using the same technique as in the post but the formula returned errors which is really not surprising if you think of it. I will explain why later in this post but first you need to know how the original formula works.
The original formula uses the COUNTIF function to take previous values vertically into account and return a new unique random number between 1 and 7. This works well, unique random numbers between 1 to 7 are produced in column B.
Array formula in cell B5:
I tweaked the formula so it also considers previous values horizontally to make sure a table never has a duplicate group number, see picture below.
Here is the entire table, the formulas return almost always a #num error somewhere in the table. Press F9 to recalculate values if you have the attached workbook open.
So why is this happening? Look at cell D9, number 4 and 1 is to the left of cell D9. This means that only numbers 2,3,5,6 or 7 can be populated in cell D9 or there will be a duplicate on row 9.
In this case, the values above cell D9 are 7,4,5,3,6 and 2 so the only value left is 1 or there will be a duplicate in column D. Number 1 is not possible in cell D9 so the formula returns a #NUM error.
The formulas above cell D9 do not take this into consideration while producing a random value, they only look back at previous values. It is not possible for the formulas to look at cells below or to the right because excel calculates each cell in this order.
It begins with the upper most left cell containing a formula, in this case B2 and then continues with the next cell below and so on, until there are no more formulas in that column. Excel then continues with the first formula in the next column, in this case cell C2.
This problem needs a new approach
My second attempt to solve this problem is to move each value one cell for each rotation, the picture below shows how in the second rotation.
The problem with this approach is that only the first rotation has random values, the remaining rotations are not random. However, this technique makes it absolutely certain that a table has no repeats. Perhaps it is random enough?
Formula in cell C3:
To make it even more random I changed the formula so the first value in each column is random (row 3) but the sequence is the same as in rotation 1 (column B). As before, it takes previous values horizontally into account when calculating a new unique value.
Array formula in cell B3:
Copy cell B3 and paste to cell range B4:B9.
Explaining formula in cell B3
Step 1 - Create array containing a sequence from 1 to 7
The ROW function returns a number representing the row number of a cell reference.
ROW($1:$7)
returns {1; 2; 3; 4; 5; 6; 7}.
Step 2 - Count numbers in column B based on sequence 1 to 7
This step checks that previous cells above is not repeated again.
Cell reference $B$2:B2 grows when cell B3 is copied and pasted to cells below.
The COUNTIF function counts cells that are equal to a condition or criteria.
COUNTIF($B$2:B2, ROW($1:$7))
becomes
COUNTIF($B$2:B2, {1; 2; 3; 4; 5; 6; 7})
becomes
COUNTIF(0, {1; 2; 3; 4; 5; 6; 7})
and returns {0; 0; 0; 0; 0; 0; 0}.
Step 3 - Check if number in array is erqual to 0 (zero)
The equal sign compares the values in the array to 0 (zero), the result is either TRUE or FALSE.
COUNTIF($B$2:B2, ROW($1:$7))=0
becomes
{0; 0; 0; 0; 0; 0; 0}=0
and returns {TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}.
Step 4 - Convert boolean value to numerical value
The SUM function is not able to add boolean values, we need to convert the boolean values to their numerical equivalents. TRUE = 1 and FALSE = 0 (zero).
--(COUNTIF($B$2:B2, ROW($1:$7))=0)
becomes
--({TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE})
and returns {1; 1; 1; 1; 1; 1; 1}.
Step 5 - Sum numerical values
The SUM function adds numerical values and ignores text and blanks, it then returns the total.
SUM(--(COUNTIF($B$2:B2, ROW($1:$7))=0))
becomes
SUM({1; 1; 1; 1; 1; 1; 1})
and returns 7.
Step 6 - Create a raandom number between 1 and the sum
The RANDBETWEEN function returns a number between a bottom number and a top number.
RANDBETWEEN(bottom, top)
RANDBETWEEN(1, SUM(--(COUNTIF($B$2:B2, ROW($1:$7))=0)))
becomes
RANDBETWEEN(1, 7)
and returns 7 (or any number between 1 and 7).
Step 7 - Extrakt the k-th largest number
The LARGE function returns the k-th largest number ina cell range or array.
LARGE(array, k)
LARGE((COUNTIF($B$2:B2, ROW($1:$7))=0)*ROW($1:$7), RANDBETWEEN(1, SUM(--(COUNTIF($B$2:B2, ROW($1:$7))=0))))
becomes
LARGE({1; 2; 3; 4; 5; 6; 7}, 7)
and returns 1.
Step 8 - Check if the current cell is less than eight
The ROW function returns a number representing the row number of a cell reference.
ROW(1:1)<8
becomes
1<8
The less than character checks if a number is lees than another number.
1<8
returns TRUE.
Step 9 - Return nothing if larger current cell is larger than seven
The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.
IF(logical_test, [value_if_true], [value_if_false])
IF(ROW(1:1)<8, LARGE((COUNTIF($B$2:B2, ROW($1:$7))=0)*ROW($1:$7), RANDBETWEEN(1, SUM(--(COUNTIF($B$2:B2, ROW($1:$7))=0)))), "")
becomes
IF(ROW(1:1)<8, 1, "")
becomes
IF(TRUE, 1, "")
and returns 1.
Array formula in cell C3:
Copy cell C3 and paste to cell range C3:H9.
Permutations category
Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]
I discussed the difference between permutations and combinations in my last post, today I want to talk about two kinds […]
I got a question a while ago about permutations, in essence how to find every permutation between 0 and 9 […]
Excel categories
10 Responses to “Rotating unique groups with no repeat”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form
By adding the following macro to the spreadsheet that creates #NUM errors, assigning a Short Cut Key, you can achieve the completely random rotation you desire without the frustration of repeating F9 over and over. J11 also shows how many recalculations it takes to get the result.
Sub Recalculate()
Range("J11") = 1
While Range("I11") = True
ActiveSheet.Calculate
Range("J11") = Range("J11") + 1
Wend
End Sub
I left out a critical formula. In I11 type:
=ISERROR(SUM(B3:H9))
Anytime there is a #NUM, the result is TRUE.
I am trying to divide my classes into groups of 3 without repeats. I have classes of 20-24. Because all classes do not have numbers divisible by 3, sometimes I will have a group or two with 4 students. Usually this can be done 6-7 unique ways without repeats. I know this because I used to do this using the website https://m.gotmath.com/splitup/ (it assigned each student a number 1-7 and that was their group #). However, that website is not working anymore and I'm sadly unable to figure out how to do random non-repeating triads on my own. I'm sure this can be done in Excel, but I can't figure it out. Can you help?
I am trying to do this but with 4 groups & 35 people. I can't seem to edit your formula without getting an #NUM! error. Can you help?
I need 1 more dimension.
I have 100 people attending an event. I have 10 tables with 10 seats with 11 rotations. I need every person to meet every other person, each person will meet 9 new people every rotation. I would like to script it with input variables of # of attendees, # of tables, # of seats per table, and number of rotations so I can reuse it for other events.
Hi Mark
Did you ever get something working for your scenario ?
I am looking to solve a similar problem
I have the same question, and am hoping for a posted solution. I have 15 individuals, and want to form groups of 3, rotating as many times as needed so that all individuals get to be in a group with all other individuals, and no one is ever in a group more than once with the same individual. Can someone help? And, if so, is it possible to then adjust the formula if the next class I have only 14 individuals, or perhaps 16, and still want students to meet in groups of 3?
Is it possible to do a table at random with 6 and 6 instead of 7 and 7 and have no repeats
I am trying to create a table that manages the following factors:
1 - List of teachers (usually 10)
2 - List of Students (Typically 40)
3 - List of Class Sessions (up to 15)
(fixed factor - class six is no more than 6 students)
For each Class Session, we want to consider 2 factors:
FIRST: each student to experience each teacher at least once, then
SECOND: each Class Session we want each student to experience a new group of students
Can you help me build this table??
I keep creating and modifying other tables and getting errors... need another data genius to help please :)
Perhaps you can assist. I have 5 threesomes (teams), 15 players, playing one round of golf for 7 rounds. Each day the teams are mixed up so that no one person ever plays with another more than once. Each round they are scrambled with no repeats.
How would I do that?