Random Sort People into Groups of 4

[TerrorTot38]

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

=IF(D5=TRUE,RAND(),"Not Valid")

Column F Copied down to F28 First Row 5

=IF(E5<>"Not Valid", RANK(E5,$E$5:$E$28,1), -99)

Column G Copied down to F28 First Row 5

=CEILING.MATH(F5/4,1)

Column C Row 3	Column D Row 3	Column E Row 3	Column F Row 3	Column G Row 3
Player	Present	Random	Rank	Game
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

=FILTER(Players!$C$5:$C$28, Players!$G$5:$G$28=B2,"")

which is copied into each individual column. Which would be B3

	Column B	Column C	Column D	Column E
	1​	2​	3​	4​
RANDOM Groups Row 3	Horse	Bird	Penguin	Mouse
	Cow	Elephant	Pig	Octopus
	Dog	Ferret	Turtle	Squid
	Ardvark	Owl	Bear	Cat
Game 1 (Row 10)	Court 1	Court 2	Court 3	Court 4
	Pig	Bird	Horse	Elephant
	Cat	Penguin	Dog	Cow
	Ferret	Mouse	Bear	Octopus
	Owl	Turtle	Ardvark	Squid
Game 2 (Row 16)	Court 1	Court 2	Court 3	Court 4
	Cow	Penguin	Bird	Pig
	Squid	Mouse	Elephant	Octopus
	Turtle	Horse	Bear	Ferret
	Dog	Cat	Owl	Ardvark

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

 

[StephenCrump]

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

 

[Eric W]

Here is your sheet as I understand it, for the benefit of anyone else who wishes to look at it:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H
1
2
3
4			Player	Present	Random	Rank	Game	Sorted
5			Duck	FALSE	Not Valid	-99	-24	Horse
6			Bird	FALSE	Not Valid	-99	-24	Penguin
7			Penguin	TRUE	0.882637472	13	4	Bear
8			Dragon	FALSE	Not Valid	-99	-24	Elephant
9			Mouse	TRUE	0.6988751	7	2	Owl
10			Pig	TRUE	0.704867366	8	2	Mouse
11			Elephant	TRUE	0.35570855	2	1	Ardvark
12			Horse	TRUE	0.578252133	6	2	Turtle
13			Cow	TRUE	0.759477222	9	3	Ferret
14			Moose	FALSE	Not Valid	-99	-24	Squid
15			Octopus	TRUE	0.812456094	11	3	Octopus
16			Fish	FALSE	Not Valid	-99	-24	Dog
17			Squid	TRUE	0.783118112	10	3	Cat
18			Turtle	TRUE	0.978033971	14	4	Pig
19			Cat	TRUE	0.202973214	1	1	Cow
20			Dog	TRUE	0.875108895	12	3
21			Ferret	TRUE	0.392979145	3	1
22			Dinosaur	FALSE	Not Valid	-99	-24
23			Bear	TRUE	0.549006496	4	1
24			Ardvark	TRUE	0.55194806	5	2
25			Tiger	FALSE	Not Valid	-99	-24
26			Lion	FALSE	Not Valid	-99	-24
27			Bee	FALSE	Not Valid	-99	-24
28			Owl	TRUE	0.998287413	15	4
Players

Cell Formulas
Range	Formula
E5:E28	E5	=IF(D5=TRUE,RAND(),"Not Valid")
F5:F28	F5	=IF(E5<>"Not Valid", RANK(E5,$E$5:$E$28,1), -99)
G5:G28	G5	=CEILING.MATH(F5/4,1)
H5:H19	H5	=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
	A	B	C	D	E
1
2		1	2	3	4
3		Horse	Penguin	Bear	Elephant
4		Owl	Mouse	Ardvark	Turtle
5		Ferret	Squid	Octopus	Dog
6		Cat	Pig	Cow
7
8
9
10	Game 1	Court 1	Court 2	Court 3	Court 4
11		Horse	Penguin	Bear	Elephant
12		Owl	Mouse	Ardvark	Turtle
13		Ferret	Squid	Octopus	Dog
14		Cat	Pig	Cow
15
16	Game 2	Court 1	Court 2	Court 3	Court 4
17
18
19
20
Sheet2

Cell Formulas
Range	Formula
B3:E6	B3	=IFERROR(INDEX(Players!H5#,SEQUENCE(ROUNDUP(COUNTA(Players!H5#)/4,0),4)),"")
B11:E14	B11	=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.

 

[StephenCrump]

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:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3									Random	Event/Court
4			Player	Present				Sorted	Allocation	1	2	3	4	5	6	7	8
5			Duck	FALSE				Penguin	1	4	4	3	3	1	2	4	2
6			Bird	FALSE				Dog	5	4	3	4	1	3	1	2	2
7			Penguin	TRUE				Elephant	10	1	2	3	2	3	1	3	4
8			Dragon	FALSE				Cat	14	3	3	2	2	3	2	1	1
9			Mouse	TRUE				Pig	2	3	1	3	1	4	4	2	4
10			Pig	TRUE				Cow	9	2	4	4	2	4	4	1	3
11			Elephant	TRUE				Ardvark	15	2	1	2	4	1	1	2	3
12			Horse	TRUE				Ferret	12	4	2	2	3	2	4	1	4
13			Cow	TRUE				Bear	16	2	2	1	1	3	2	4	3
14			Moose	FALSE				Squid	8	1	1	4	3	2	2	3	3
15			Octopus	TRUE				Horse	11	2	3	3	4	2	3	3	1
16			Fish	TRUE				Mouse	4	3	4	1	4	2	1	4	4
17			Squid	TRUE				Octopus	13	1	3	1	3	1	4	2	1
18			Turtle	TRUE				Fish	6	4	1	1	2	4	3	4	1
19			Cat	TRUE				Turtle	7	1	4	2	1	4	3	3	2
20			Dog	TRUE				Owl	3	3	2	4	4	1	3	1	2
21			Ferret	TRUE
22			Dinosaur	FALSE						From: http://golfsoftware.com/tools/schedule/playall.html
23			Bear	TRUE							Group 1	Group 2	Group 3	Group 4
24			Ardvark	TRUE						Event 1	7-8-10-13	9-11-15-16	2-3-4-14	1-5-6-12
25			Tiger	FALSE						Event 2	2-6-8-15	3-10-12-16	5-11-13-14	1-4-7-9
26			Lion	FALSE						Event 3	4-6-13-16	7-12-14-15	1-2-10-11	3-5-8-9
27			Bee	FALSE						Event 4	2-5-7-16	6-9-10-14	1-8-12-13	3-4-11-15
28			Owl	TRUE						Event 5	1-3-13-15	4-8-11-12	5-10-14-16	2-6-7-9
29										Event 6	4-5-10-15	1-8-14-16	3-6-7-11	2-9-12-13
30										Event 7	3-9-12-14	2-5-13-15	7-8-10-11	1-4-6-16
31										Event 8	6-11-13-14	1-3-5-7	8-9-15-16	2-4-10-12
32
Sheet1

Cell Formulas
Range	Formula
H5:H20	H5	=SORTBY(FILTER(C5:C28,D5:D28),RANDARRAY(COUNTIF(D5:D28,TRUE)))
I5:I20	I5	=SORTBY(SEQUENCE(ROWS(H5#)),RANDARRAY(ROWS(H5#)))
J5:Q20	J5	=XMATCH("*-"&$I5&"-*","-"&INDEX(Schedule,J$4,)&"-",2)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Schedule	=Sheet1!$K$24:$N$31	J5:Q20

 

[TerrorTot38]

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.

 

[TerrorTot38]

Here is your sheet as I understand it, for the benefit of anyone else who wishes to look at it:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H
1
2
3
4			Player	Present	Random	Rank	Game	Sorted
5			Duck	FALSE	Not Valid	-99	-24	Horse
6			Bird	FALSE	Not Valid	-99	-24	Penguin
7			Penguin	TRUE	0.882637472	13	4	Bear
8			Dragon	FALSE	Not Valid	-99	-24	Elephant
9			Mouse	TRUE	0.6988751	7	2	Owl
10			Pig	TRUE	0.704867366	8	2	Mouse
11			Elephant	TRUE	0.35570855	2	1	Ardvark
12			Horse	TRUE	0.578252133	6	2	Turtle
13			Cow	TRUE	0.759477222	9	3	Ferret
14			Moose	FALSE	Not Valid	-99	-24	Squid
15			Octopus	TRUE	0.812456094	11	3	Octopus
16			Fish	FALSE	Not Valid	-99	-24	Dog
17			Squid	TRUE	0.783118112	10	3	Cat
18			Turtle	TRUE	0.978033971	14	4	Pig
19			Cat	TRUE	0.202973214	1	1	Cow
20			Dog	TRUE	0.875108895	12	3
21			Ferret	TRUE	0.392979145	3	1
22			Dinosaur	FALSE	Not Valid	-99	-24
23			Bear	TRUE	0.549006496	4	1
24			Ardvark	TRUE	0.55194806	5	2
25			Tiger	FALSE	Not Valid	-99	-24
26			Lion	FALSE	Not Valid	-99	-24
27			Bee	FALSE	Not Valid	-99	-24
28			Owl	TRUE	0.998287413	15	4
Players

Cell Formulas
Range	Formula
E5:E28	E5	=IF(D5=TRUE,RAND(),"Not Valid")
F5:F28	F5	=IF(E5<>"Not Valid", RANK(E5,$E$5:$E$28,1), -99)
G5:G28	G5	=CEILING.MATH(F5/4,1)
H5:H19	H5	=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
	A	B	C	D	E
1
2		1	2	3	4
3		Horse	Penguin	Bear	Elephant
4		Owl	Mouse	Ardvark	Turtle
5		Ferret	Squid	Octopus	Dog
6		Cat	Pig	Cow
7
8
9
10	Game 1	Court 1	Court 2	Court 3	Court 4
11		Horse	Penguin	Bear	Elephant
12		Owl	Mouse	Ardvark	Turtle
13		Ferret	Squid	Octopus	Dog
14		Cat	Pig	Cow
15
16	Game 2	Court 1	Court 2	Court 3	Court 4
17
18
19
20
Sheet2

Cell Formulas
Range	Formula
B3:E6	B3	=IFERROR(INDEX(Players!H5#,SEQUENCE(ROUNDUP(COUNTA(Players!H5#)/4,0),4)),"")
B11:E14	B11	=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.

 

[TerrorTot38]

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:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3									Random	Event/Court
4			Player	Present				Sorted	Allocation	1	2	3	4	5	6	7	8
5			Duck	FALSE				Penguin	1	4	4	3	3	1	2	4	2
6			Bird	FALSE				Dog	5	4	3	4	1	3	1	2	2
7			Penguin	TRUE				Elephant	10	1	2	3	2	3	1	3	4
8			Dragon	FALSE				Cat	14	3	3	2	2	3	2	1	1
9			Mouse	TRUE				Pig	2	3	1	3	1	4	4	2	4
10			Pig	TRUE				Cow	9	2	4	4	2	4	4	1	3
11			Elephant	TRUE				Ardvark	15	2	1	2	4	1	1	2	3
12			Horse	TRUE				Ferret	12	4	2	2	3	2	4	1	4
13			Cow	TRUE				Bear	16	2	2	1	1	3	2	4	3
14			Moose	FALSE				Squid	8	1	1	4	3	2	2	3	3
15			Octopus	TRUE				Horse	11	2	3	3	4	2	3	3	1
16			Fish	TRUE				Mouse	4	3	4	1	4	2	1	4	4
17			Squid	TRUE				Octopus	13	1	3	1	3	1	4	2	1
18			Turtle	TRUE				Fish	6	4	1	1	2	4	3	4	1
19			Cat	TRUE				Turtle	7	1	4	2	1	4	3	3	2
20			Dog	TRUE				Owl	3	3	2	4	4	1	3	1	2
21			Ferret	TRUE
22			Dinosaur	FALSE						From: http://golfsoftware.com/tools/schedule/playall.html
23			Bear	TRUE							Group 1	Group 2	Group 3	Group 4
24			Ardvark	TRUE						Event 1	7-8-10-13	9-11-15-16	2-3-4-14	1-5-6-12
25			Tiger	FALSE						Event 2	2-6-8-15	3-10-12-16	5-11-13-14	1-4-7-9
26			Lion	FALSE						Event 3	4-6-13-16	7-12-14-15	1-2-10-11	3-5-8-9
27			Bee	FALSE						Event 4	2-5-7-16	6-9-10-14	1-8-12-13	3-4-11-15
28			Owl	TRUE						Event 5	1-3-13-15	4-8-11-12	5-10-14-16	2-6-7-9
29										Event 6	4-5-10-15	1-8-14-16	3-6-7-11	2-9-12-13
30										Event 7	3-9-12-14	2-5-13-15	7-8-10-11	1-4-6-16
31										Event 8	6-11-13-14	1-3-5-7	8-9-15-16	2-4-10-12
32
Sheet1

Cell Formulas
Range	Formula
H5:H20	H5	=SORTBY(FILTER(C5:C28,D5:D28),RANDARRAY(COUNTIF(D5:D28,TRUE)))
I5:I20	I5	=SORTBY(SEQUENCE(ROWS(H5#)),RANDARRAY(ROWS(H5#)))
J5:Q20	J5	=XMATCH("*-"&$I5&"-*","-"&INDEX(Schedule,J$4,)&"-",2)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Schedule	=Sheet1!$K$24:$N$31	J5: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.

 

[Eric W]

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
	A	B	C	D	E	F	G	H
1
2		1	2	3	4	5	6
3		Penguin	Pig	Owl	Moose	Dragon	Elephant
4		Cat	Bird	Bear	Dog	Ardvark
5		Ferret	Octopus	Turtle	Horse	Mouse
6		Cow	Squid	Fish	Dinosaur	Duck
Sheet2

Cell Formulas
Range	Formula
B3:G6	B3	=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?

 

[StephenCrump]

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.

 

[TerrorTot38]

You could change the Sheet2 formula as follows:

Book1
	A	B	C	D	E	F	G	H
1
2		1	2	3	4	5	6
3		Penguin	Pig	Owl	Moose	Dragon	Elephant
4		Cat	Bird	Bear	Dog	Ardvark
5		Ferret	Octopus	Turtle	Horse	Mouse
6		Cow	Squid	Fish	Dinosaur	Duck
Sheet2

Cell Formulas
Range	Formula
B3:G6	B3	=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?