Rotating unique groups with no repeat
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.
Array formula in cell C3:
Copy cell C3 and paste to cell range C3:H9.
Download excel *.xlsx file
Rotating unique groups with no repeat.xlsx
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
Today I have two functions I would like to demonstrate, they calculate all possible combinations from a cell range. What […]
Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]
List permutations without repetition [UDF]
This blog post describes how to create permutations, repetition is NOT allowed. Permutations are items arranged in a given order meaning […]
Chris asks: Maximum Number Allowed is 4 digit and the number is from 0 to 9. After I fill in […]
Permutations with and without repetition
I discussed the difference between permutations and combinations in my last post, today I want to talk about two kinds […]
List permutations with repetition [UDF]
This blog post demonstrates a custom function (UDF) that creates permutations. Repetition is allowed. The custom function lets you specify the […]
List all permutations with a condition
I got a question a while ago about permutations, in essence how to find every permutation between 0 and 9 […]
List permutations with repetition and how many to choose from
Noel asks: Is there a way where i can predict all possible outcomes in excel in the below example. Total […]
6 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.
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.
Is it possible to do a table at random with 6 and 6 instead of 7 and 7 and have no repeats