Random Sort People into Groups of 4

TerrorTot38

New Member
Joined
Feb 2, 2022
Messages
21
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Hi All,

I have been working on sorting members of a club into Random Names into Groups of 4 People. I have 2 sheets that I use for this. This is sheet 1 below for the purpose of this I have used animals instead of names.

Following Formulas used below:
Column E Copied down to E28 First Row 5
Excel Formula:
=IF(D5=TRUE,RAND(),"Not Valid")
Column F Copied down to F28 First Row 5
Excel Formula:
=IF(E5<>"Not Valid", RANK(E5,$E$5:$E$28,1), -99)
Column G Copied down to F28 First Row 5
Excel Formula:
=CEILING.MATH(F5/4,1)

Column C Row 3Column D Row 3Column E Row 3Column F Row 3Column G Row 3
PlayerPresentRandomRankGame
Duck
FALSE​
Not Valid
-99​
-24​
Bird
TRUE​
0.969600152​
16​
4​
Penguin
TRUE​
0.516272895​
9​
3​
Dragon
FALSE​
Not Valid
-99​
-24​
Mouse
TRUE​
0.060517056​
1​
1​
Pig
TRUE​
0.589193816​
10​
3​
Elephant
TRUE​
0.782223488​
12​
3​
Horse
TRUE​
0.225618692​
6​
2​
Cow
TRUE​
0.859498081​
15​
4​
Moose
FALSE​
Not Valid
-99​
-24​
Octopus
TRUE​
0.134450165​
4​
1​
Fish
FALSE​
Not Valid
-99​
-24​
Squid
TRUE​
0.128703939​
3​
1​
Turtle
TRUE​
0.28877907​
7​
2​
Cat
TRUE​
0.852239151​
14​
4​
Dog
TRUE​
0.758370693​
11​
3​
Ferret
TRUE​
0.852133831​
13​
4​
Dinosaur
FALSE​
Not Valid
-99​
-24​
Bear
TRUE​
0.173209906​
5​
2​
Ardvark
TRUE​
0.102160204​
2​
1​
Tiger
FALSE​
Not Valid
-99​
-24​
Lion
FALSE​
Not Valid
-99​
-24​
Bee
FALSE​
Not Valid
-99​
-24​
Owl
TRUE​
0.40193213​
8​
2[/CODE]​

Then on Sheet 2 I have the following:

In Row B3:E3 is use the filter function
Excel Formula:
=FILTER(Players!$C$5:$C$28, Players!$G$5:$G$28=B2,"")
which is copied into each individual column. Which would be B3

Column BColumn CColumn DColumn E
1​
2​
3​
4​
RANDOM Groups Row 3HorseBirdPenguinMouse
CowElephantPigOctopus
DogFerretTurtleSquid
ArdvarkOwlBearCat
Game 1 (Row 10)Court 1Court 2Court 3Court 4
PigBirdHorseElephant
CatPenguinDogCow
FerretMouseBearOctopus
OwlTurtleArdvarkSquid
Game 2 (Row 16)Court 1Court 2Court 3Court 4
CowPenguinBirdPig
SquidMouseElephantOctopus
TurtleHorseBearFerret
DogCatOwlArdvark

Is there away to produce the same result not using Filter? Also to copy data from Random Groups into Game 1 and Game 2 without using Paste Special - Values Only.

I am also happy to not use =RAND() if there is another way to do this.

Sorry I'm not using XL2BB as my O365 doesn't support it.

Feel free to as ask any questions and will answer them as best I can.

Thanks
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
It's very easy to allocate N people randomly into groups of 4.

The hard part is iterating this, and making sure the individual groups remain mixed up, e.g. ensuring that Player A doesn't always end up in the same group as Player B. I assume this is what you're trying to do? It's sometimes described as the Social Golfer problem.

There's a tool here you may find useful: Golf Pairings Generator: Play All Golfers Scheduler - GolfSoftware.com
 
Upvote 0
Here is your sheet as I understand it, for the benefit of anyone else who wishes to look at it:

Book1 (version 1).xlsb
ABCDEFGH
1
2
3
4PlayerPresentRandomRankGameSorted
5DuckFALSENot Valid-99-24Horse
6BirdFALSENot Valid-99-24Penguin
7PenguinTRUE0.882637472134Bear
8DragonFALSENot Valid-99-24Elephant
9MouseTRUE0.698875172Owl
10PigTRUE0.70486736682Mouse
11ElephantTRUE0.3557085521Ardvark
12HorseTRUE0.57825213362Turtle
13CowTRUE0.75947722293Ferret
14MooseFALSENot Valid-99-24Squid
15OctopusTRUE0.812456094113Octopus
16FishFALSENot Valid-99-24Dog
17SquidTRUE0.783118112103Cat
18TurtleTRUE0.978033971144Pig
19CatTRUE0.20297321411Cow
20DogTRUE0.875108895123
21FerretTRUE0.39297914531
22DinosaurFALSENot Valid-99-24
23BearTRUE0.54900649641
24ArdvarkTRUE0.5519480652
25TigerFALSENot Valid-99-24
26LionFALSENot Valid-99-24
27BeeFALSENot Valid-99-24
28OwlTRUE0.998287413154
Players
Cell Formulas
RangeFormula
E5:E28E5=IF(D5=TRUE,RAND(),"Not Valid")
F5:F28F5=IF(E5<>"Not Valid", RANK(E5,$E$5:$E$28,1), -99)
G5:G28G5=CEILING.MATH(F5/4,1)
H5:H19H5=SORTBY(FILTER(C5:C28,D5:D28),RANDARRAY(COUNTIF(D5:D28,TRUE)))
Dynamic array formulas.


Your original formulas are in E:G, I added the H formula to show a different way to sort them randomly. Then you can reference them on Sheet2 as follows:

Book1 (version 1).xlsb
ABCDE
1
21234
3HorsePenguinBearElephant
4OwlMouseArdvarkTurtle
5FerretSquidOctopusDog
6CatPigCow
7
8
9
10Game 1Court 1Court 2Court 3Court 4
11HorsePenguinBearElephant
12OwlMouseArdvarkTurtle
13FerretSquidOctopusDog
14CatPigCow
15
16Game 2Court 1Court 2Court 3Court 4
17
18
19
20
Sheet2
Cell Formulas
RangeFormula
B3:E6B3=IFERROR(INDEX(Players!H5#,SEQUENCE(ROUNDUP(COUNTA(Players!H5#)/4,0),4)),"")
B11:E14B11=INDEX($B$3#,0,COLUMNS($B11:B11))
Dynamic array formulas.


I put FALSE on "Bird" to show what happens if there aren't enough present to fill the groups. If there are more than enough, the groups will start to have more than 4 in each group.

As far as copying them down to the Game 1 and Game 2 sections, an INDEX as shown works fine. But I suspect your question is how to keep them from reshuffling as the sheet recalculates. In that case, you need to Paste Values like you do, or use VBA.

As far as Stephen Crump's suggestion about the Social Golfer issue, let us know if that's of interest. It is a much bigger subject.
 
Upvote 0
I think you've ruled out using VBA (in another thread).

So building on @Eric W's base, you could hard-code an optimised schedule using the link in Post #2, and generate multiple rounds like this:

ABCDEFGHIJKLMNOPQ
1
2
3RandomEvent/Court
4PlayerPresentSortedAllocation12345678
5DuckFALSEPenguin144331242
6BirdFALSEDog543413122
7PenguinTRUEElephant1012323134
8DragonFALSECat1433223211
9MouseTRUEPig231314424
10PigTRUECow924424413
11ElephantTRUEArdvark1521241123
12HorseTRUEFerret1242232414
13CowTRUEBear1622113243
14MooseFALSESquid811432233
15OctopusTRUEHorse1123342331
16FishTRUEMouse434142144
17SquidTRUEOctopus1313131421
18TurtleTRUEFish641124341
19CatTRUETurtle714214332
20DogTRUEOwl332441312
21FerretTRUE
22DinosaurFALSEFrom: http://golfsoftware.com/tools/schedule/playall.html
23BearTRUEGroup 1Group 2Group 3Group 4
24ArdvarkTRUEEvent 17-8-10-139-11-15-162-3-4-141-5-6-12
25TigerFALSEEvent 22-6-8-153-10-12-165-11-13-141-4-7-9
26LionFALSEEvent 34-6-13-167-12-14-151-2-10-113-5-8-9
27BeeFALSEEvent 42-5-7-166-9-10-141-8-12-133-4-11-15
28OwlTRUEEvent 51-3-13-154-8-11-125-10-14-162-6-7-9
29Event 64-5-10-151-8-14-163-6-7-112-9-12-13
30Event 73-9-12-142-5-13-157-8-10-111-4-6-16
31Event 86-11-13-141-3-5-78-9-15-162-4-10-12
32
Sheet1
Cell Formulas
RangeFormula
H5:H20H5=SORTBY(FILTER(C5:C28,D5:D28),RANDARRAY(COUNTIF(D5:D28,TRUE)))
I5:I20I5=SORTBY(SEQUENCE(ROWS(H5#)),RANDARRAY(ROWS(H5#)))
J5:Q20J5=XMATCH("*-"&$I5&"-*","-"&INDEX(Schedule,J$4,)&"-",2)
Dynamic array formulas.
Named Ranges
NameRefers ToCells
Schedule=Sheet1!$K$24:$N$31J5:Q20
 
Upvote 0
It's very easy to allocate N people randomly into groups of 4.

The hard part is iterating this, and making sure the individual groups remain mixed up, e.g. ensuring that Player A doesn't always end up in the same group as Player B. I assume this is what you're trying to do? It's sometimes described as the Social Golfer problem.

There's a tool here you may find useful: Golf Pairings Generator: Play All Golfers Scheduler - GolfSoftware.com
Yes this is the scenario I am trying to find. I have tried Golf Pairing before but unfortunately doesn't work how I need and prefer to run it locally.
 
Upvote 0
Here is your sheet as I understand it, for the benefit of anyone else who wishes to look at it:

Book1 (version 1).xlsb
ABCDEFGH
1
2
3
4PlayerPresentRandomRankGameSorted
5DuckFALSENot Valid-99-24Horse
6BirdFALSENot Valid-99-24Penguin
7PenguinTRUE0.882637472134Bear
8DragonFALSENot Valid-99-24Elephant
9MouseTRUE0.698875172Owl
10PigTRUE0.70486736682Mouse
11ElephantTRUE0.3557085521Ardvark
12HorseTRUE0.57825213362Turtle
13CowTRUE0.75947722293Ferret
14MooseFALSENot Valid-99-24Squid
15OctopusTRUE0.812456094113Octopus
16FishFALSENot Valid-99-24Dog
17SquidTRUE0.783118112103Cat
18TurtleTRUE0.978033971144Pig
19CatTRUE0.20297321411Cow
20DogTRUE0.875108895123
21FerretTRUE0.39297914531
22DinosaurFALSENot Valid-99-24
23BearTRUE0.54900649641
24ArdvarkTRUE0.5519480652
25TigerFALSENot Valid-99-24
26LionFALSENot Valid-99-24
27BeeFALSENot Valid-99-24
28OwlTRUE0.998287413154
Players
Cell Formulas
RangeFormula
E5:E28E5=IF(D5=TRUE,RAND(),"Not Valid")
F5:F28F5=IF(E5<>"Not Valid", RANK(E5,$E$5:$E$28,1), -99)
G5:G28G5=CEILING.MATH(F5/4,1)
H5:H19H5=SORTBY(FILTER(C5:C28,D5:D28),RANDARRAY(COUNTIF(D5:D28,TRUE)))
Dynamic array formulas.


Your original formulas are in E:G, I added the H formula to show a different way to sort them randomly. Then you can reference them on Sheet2 as follows:

Book1 (version 1).xlsb
ABCDE
1
21234
3HorsePenguinBearElephant
4OwlMouseArdvarkTurtle
5FerretSquidOctopusDog
6CatPigCow
7
8
9
10Game 1Court 1Court 2Court 3Court 4
11HorsePenguinBearElephant
12OwlMouseArdvarkTurtle
13FerretSquidOctopusDog
14CatPigCow
15
16Game 2Court 1Court 2Court 3Court 4
17
18
19
20
Sheet2
Cell Formulas
RangeFormula
B3:E6B3=IFERROR(INDEX(Players!H5#,SEQUENCE(ROUNDUP(COUNTA(Players!H5#)/4,0),4)),"")
B11:E14B11=INDEX($B$3#,0,COLUMNS($B11:B11))
Dynamic array formulas.


I put FALSE on "Bird" to show what happens if there aren't enough present to fill the groups. If there are more than enough, the groups will start to have more than 4 in each group.

As far as copying them down to the Game 1 and Game 2 sections, an INDEX as shown works fine. But I suspect your question is how to keep them from reshuffling as the sheet recalculates. In that case, you need to Paste Values like you do, or use VBA.

As far as Stephen Crump's suggestion about the Social Golfer issue, let us know if that's of interest. It is a much bigger subject.
At one evening when we play we can have 12 players, 14 players or 20 players. It needs to be 4 players per game otherwise it won't work hence why I have sorted the ranking the by group to give a group number and this is what is filtered.

Unfortunately I can't access VBA as I only have O365 online not locally.
 
Upvote 0
I think you've ruled out using VBA (in another thread).

So building on @Eric W's base, you could hard-code an optimised schedule using the link in Post #2, and generate multiple rounds like this:

ABCDEFGHIJKLMNOPQ
1
2
3RandomEvent/Court
4PlayerPresentSortedAllocation12345678
5DuckFALSEPenguin144331242
6BirdFALSEDog543413122
7PenguinTRUEElephant1012323134
8DragonFALSECat1433223211
9MouseTRUEPig231314424
10PigTRUECow924424413
11ElephantTRUEArdvark1521241123
12HorseTRUEFerret1242232414
13CowTRUEBear1622113243
14MooseFALSESquid811432233
15OctopusTRUEHorse1123342331
16FishTRUEMouse434142144
17SquidTRUEOctopus1313131421
18TurtleTRUEFish641124341
19CatTRUETurtle714214332
20DogTRUEOwl332441312
21FerretTRUE
22DinosaurFALSEFrom: http://golfsoftware.com/tools/schedule/playall.html
23BearTRUEGroup 1Group 2Group 3Group 4
24ArdvarkTRUEEvent 17-8-10-139-11-15-162-3-4-141-5-6-12
25TigerFALSEEvent 22-6-8-153-10-12-165-11-13-141-4-7-9
26LionFALSEEvent 34-6-13-167-12-14-151-2-10-113-5-8-9
27BeeFALSEEvent 42-5-7-166-9-10-141-8-12-133-4-11-15
28OwlTRUEEvent 51-3-13-154-8-11-125-10-14-162-6-7-9
29Event 64-5-10-151-8-14-163-6-7-112-9-12-13
30Event 73-9-12-142-5-13-157-8-10-111-4-6-16
31Event 86-11-13-141-3-5-78-9-15-162-4-10-12
32
Sheet1
Cell Formulas
RangeFormula
H5:H20H5=SORTBY(FILTER(C5:C28,D5:D28),RANDARRAY(COUNTIF(D5:D28,TRUE)))
I5:I20I5=SORTBY(SEQUENCE(ROWS(H5#)),RANDARRAY(ROWS(H5#)))
J5:Q20J5=XMATCH("*-"&$I5&"-*","-"&INDEX(Schedule,J$4,)&"-",2)
Dynamic array formulas.
Named Ranges
NameRefers ToCells
Schedule=Sheet1!$K$24:$N$31J5:Q20
This looks of interest to myself how would I generate this for different number of players for example 12, 16, 14 etc...

Is the a way to ensure the player in the last column only appear in there once? As we only have enough space for 12 players but 14 can attend hence the random players and game generator I am creating.
 
Upvote 0
At one evening when we play we can have 12 players, 14 players or 20 players. It needs to be 4 players per game otherwise it won't work hence why I have sorted the ranking the by group to give a group number and this is what is filtered.

Unfortunately I can't access VBA as I only have O365 online not locally.
You could change the Sheet2 formula as follows:

Book1
ABCDEFGH
1
2123456
3PenguinPigOwlMooseDragonElephant
4CatBirdBearDogArdvark
5FerretOctopusTurtleHorseMouse
6CowSquidFishDinosaurDuck
Sheet2
Cell Formulas
RangeFormula
B3:G6B3=IFERROR(INDEX(Players!H5#,TRANSPOSE(SEQUENCE(ROUNDUP(COUNTA(Players!H5#)/4,0),4))),"")
Dynamic array formulas.


That will always put the players in groups of 4, with any extras on the right.

Without VBA, then you're stuck with the Paste Values method.

I'll let Stephen continue with the Social Golfers set up. His approach of saving the appropriate pairings for each potential size of players seems viable, but it might require saving a lot of tables somewhere. One thought though: Stephen, is there any reason to use a random sort in your I5 formula? Since the H5 formula already sorts the names randomly, doesn't that just mix them up again?
 
Upvote 0
I'll let Stephen continue with the Social Golfers set up. His approach of saving the appropriate pairings for each potential size of players seems viable, but it might require saving a lot of tables somewhere. One thought though: Stephen, is there any reason to use a random sort in your I5 formula? Since the H5 formula already sorts the names randomly, doesn't that just mix them up again?
I'd envisage storing perhaps 5 template draws, e.g. 8, 12, 16, 20 or 24 players, each over 10 rounds, say.

But I'm probably over-engineering the solution. On further reflection, the Social Golfer problem assumes a static player pool. Here, the participant pool may vary week to week depending on who turns up, so the event template won't necessarily totally mix up the players from week to week.

Your simple approach, iterated, will probably suffice for the OP's purposes.

One thought though: Stephen, is there any reason to use a random sort in your I5 formula? Since the H5 formula already sorts the names randomly, doesn't that just mix them up again?
Ha ha. You're quite right. It was a last minute (and redundant!) addition.
 
Upvote 0
You could change the Sheet2 formula as follows:

Book1
ABCDEFGH
1
2123456
3PenguinPigOwlMooseDragonElephant
4CatBirdBearDogArdvark
5FerretOctopusTurtleHorseMouse
6CowSquidFishDinosaurDuck
Sheet2
Cell Formulas
RangeFormula
B3:G6B3=IFERROR(INDEX(Players!H5#,TRANSPOSE(SEQUENCE(ROUNDUP(COUNTA(Players!H5#)/4,0),4))),"")
Dynamic array formulas.


That will always put the players in groups of 4, with any extras on the right.

Without VBA, then you're stuck with the Paste Values method.

I'll let Stephen continue with the Social Golfers set up. His approach of saving the appropriate pairings for each potential size of players seems viable, but it might require saving a lot of tables somewhere. One thought though: Stephen, is there any reason to use a random sort in your I5 formula? Since the H5 formula already sorts the names randomly, doesn't that just mix them up again?
Hi Eric, on the formula suggest it references H5? On my original example this isn't used. Can you explain why the column is required? Is there a way not to use H5?
 
Upvote 0

Forum statistics

Threads
1,223,961
Messages
6,175,651
Members
452,664
Latest member
alpserbetli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top