Sorting Office Names into Groups of 3 Randomly.

[Cking619]

My admin is trying to put together an excel spreadsheet to randomly assign 9 separate offices to 3 different meetings each week for an entire year without repeating in the same meeting the following week.

I've tried randomizing and am not very familiar with how to accomplish the task. Im attaching (or trying to) the link to the agenda example.

Thank you in advance for any assistance.

[bob33]

Does attached work?

[Cking619]

No and the reason is, in the example you provided, the first 3 columns are never in the same meeting, or conjoining meetings. The idea is to randomize it with the possibilities of all offices intermingling with each other at some point.

Does that make sense?

[bob33]

Can't you look at the content I provided to see if it fits your criteria?
If you don't know what you want, how can others help?

[Cking619]

Can't you look at the content I provided to see if it fits your criteria?
If you don't know what you want, how can others help?

Yes, and as I stated, cycling through 123 doesn't alleviate my concern.

[JeteMc]

Here is a possible starting point.
In most randomizations of patterns without repetition that I have encountered, the patterns are set first and then the application of the patterns is randomized.
In the attached file I have set up the pattern for meeting 1 such that all of the offices meet with Tampa and one other office in R3:AS11. Should you decide to use this method we can attempt to fill in patterns for meetings 2 and 3.
R2:AS2 is populated using the RAND function and R1:AS1 is populated using the RANK.EQ function.
In table 66 column 1 is populated manually down through week 28. Note that there would be 84 combinations of 9 offices chosen 3 at a time, and since there are 3 meetings being held each week that would mean 28 weeks in the rotation.
The Tampa:Atlanta columns are populated using: =INDEX($R$3:$AS$11,MATCH(Table22[[#Headers],[Tampa]],Table66[[Group]:[Group]],0),MATCH($B4,$R$1:$AS$1,0))
Let us know if you have any questions.