# Randomize seating problem

#### LenaH

##### New Member
Dear all,

i have a list of Clubs, as shown below. I would like to avoid having same players from the same clubs sit together.
Is there a way to randomize it?

A table of players usually consists of 4 players. Of course, you can't avoid it fully, but a little randomization would surely help.

Tourney.xlsm
NO
1PlayerClub
2Player 1LA
3Player 2LA
4Player 3DE
5Player 4LA
6Player 5LA
7Player 6LA
8Player 7LA
9Player 8LA
10Player 9DE
11Player 10DE
12Player 11DE
13Player 12DE
14Player 13DE
15Player 14DE
16Player 15DE
17Player 16DE
18Player 17DE
19Player 18DE
20Player 19LV
21Player 20LV
22Player 21LV
23Player 22LV
24Player 23LV
25Player 24LV
26Player 25LV
27Player 26LV
28Player 27LV
29Player 28LV
30Player 29LV
31Player 30MN
32Player 31MN
33Player 32MN
34Player 33MN
35Player 34CANM
PlayerPool

Any ideas?
Lena

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

#### C with no eyes

##### Board Regular
Drag =RAND() down as a new helper column. Sort by that column, the order will change every time.

#### AhoyNC

##### Well-known Member
Here is a link to a site that has a program (free) to generate random pairings.

#### Rick Rothstein

##### MrExcel MVP
A table of players usually consists of 4 players. Of course, you can't avoid it fully, but a little randomization would surely help.
What do you mean by "usually"... what other numbers could there be?

Can you give us an idea of the approximate minimum number of players per team and the approximate maximum number of players per team?

#### LenaH

##### New Member
What do you mean by "usually"... what other numbers could there be?

Can you give us an idea of the approximate minimum number of players per team and the approximate maximum number of players per team?
Hi Rick, it is mostly (90%) four players, and the rest (10%) three players.

#### Rick Rothstein

##### MrExcel MVP
How is the number of three player tables determined? If you want us to distribute a your players into tables of different sizes, we need to know what determines when you switch to three players. For example, do you have a fixed number of four player tables after which any remaining unassigned players are placed at? Or do you have a fixed number of tables (all the same size) and you must fill them all, first with four players per table until it requires three players per table to fill them all? Or some other controlling scheme?

#### LenaH

##### New Member
How is the number of three player tables determined? If you want us to distribute a your players into tables of different sizes, we need to know what determines when you switch to three players. For example, do you have a fixed number of four player tables after which any remaining unassigned players are placed at? Or do you have a fixed number of tables (all the same size) and you must fill them all, first with four players per table until it requires three players per table to fill them all? Or some other controlling scheme?
On my initial list, I only want to avoid having too many players of the same club together. Nothing too fancy, just a little randomization. The problem occurred last time, when a group showed up at the registration and they all sat together at the beginning of the tourney, because that's how they showed up.

As you can see above, the first 18 players were registered only from LA and DE.

Here's how I determined the tables, dependant on the player numbers:

Tourney.xlsm
ABCD
153 Player4 PlayerPlayerCount
16Tables11834
17Rest12
18
1974 playertables
201223 player tables
Template
Cell Formulas
RangeFormula
B16B16=QUOTIENT(\$D\$16,3)
C16C16=QUOTIENT(\$D\$16,4)
D16D16=PlayerPool!W2
B17B17=MOD(\$D\$16,3)
C17C17=MOD(\$D\$16,4)
C19C19=IF(\$C\$17=0,\$D\$16/4,IF(\$C\$17=1,(C16-2),IF(\$C\$17=2,(C16-1),IF(\$C\$17=3,C16))))
B20B20=IF(\$B\$17=0,(\$D\$16)/3,IF(\$B\$17=1,(\$D\$16-(B16*3)+B16),IF(\$B\$17=2,(\$D\$16-(B16*3)+B16-1),"")))
C20C20=IF(\$C\$17=1,(\$D\$16-(C19*4))/3,IF(\$C\$17=2,(\$D\$16-(C19*4))/3,IF(\$C\$17=3,(\$D\$16-(C19*4))/3,"")))

#### Rick Rothstein

##### MrExcel MVP
I am afraid I am somewhat confused now. Are you saying you have a total of 34 players and you need to distribute them among as many tables that are needed? So the number of tables and how many sit at each can vary? Or are you saying to use 9 tables with 7 tables holding 4 players each and 2 tables holding 3 players each?

In order to be able to effectively randomize the seating minimizing the doubling up of players from the same team, we would need to know the total number of teams and the total number of players per team. Can you post such a table (like in your first post, but complete)?

#### LenaH

##### New Member
Player count varies. Table count varies. Club count varies.

In my example I have 34 player, which are divided to 7 tables of 4 players and 2 tables of 3 players.
I can have up to 150 players, it's just an example.

Each player, belongs to a certain club and simply spoken, I just want to shake it all up a little.

Thanks for taking your time, to understand my problem.
Lena

#### Rick Rothstein

##### MrExcel MVP
Without knowing the actual count of each team, there is no way (that I can see) to give you a formula or macro solution... there are too many variable to account for given all the teams can have different number of players. With that said, I can give you a strategy to follow when assigning seats. First, the total number of tables needed (where that total is the minimum possible) would be the total number of players divided by 4 with only one table of 3 required if 4 does not divide the total number of players evenly. So, if N is the total number of players, the formula for the number of tables of 4 would be...

=INT(N/4)

and the number of tables of three players (either 0 or 1) would be....

=0+(MOD(A1/4,4)>0)

Now, as to the strategy for assigning teams (which player is immaterial... you can pick those randomly at the end)... take the team with the highest number of players and assign one to each table until you run out of players (if you have more players on a team than tables, then you will have to obviously double some tables up with players from the same team)... you can just randomly pick a table to assign each to (I am thinking some is walking around with the place cards an plopping them on a table). Then take the team with the next highest number of players and do the same thing... and then so on for the then next highest and so one. You would want to minimize loading table with say a third team while other tables had zero players assigned to them. The idea is to fill all tables with at least one team before putting a second team at any table. Similarly, fill each table with two teams before placing a third team on any single table and continue until you run out of team players. This should minimize the number of players from any one team at any single table (my guess is no table would ever have more than two players at the same table, and this would occur on the minimum number of tables).