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.
This works well, unique random numbers between 1 to 7 are produced in column B. 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. I will explain why later in this post but first you need to know how the original formula works. 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. 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. 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. 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.