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

 

[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.

Hi Eric, I have inserted the new formula into my B11

=INDEX($B$3#,0,COLUMNS($B11:B11))

but this I think only would work for Game 1 and couldn't use this for Game 2-5.

I had the idea of using referencing on the second sheet under Game 1. Below I have shown the formula as opposed to the data.

Column A	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
	=B3	=C3	=D3	=E3
	=B4	=C4	=D4	=E4
	=B5	=C5	=D5	=E5
	=B6	=C6	=D6	=E6

How would I then replicate this for Game 2 - 5? I guess I'm stuck using Copy - Paste Values. I tried a hack of copying the data to another sheet but this didn't work lol.

Any advice?

Thanks,
J

 

[Eric W]

How about this then? On your Player sheet, just put the list of names like this (no formulas):

Book1 (version 1).xlsb
	A	B	C	D
1
2
3
4			Player	Present
5			Duck	TRUE
6			Bird	TRUE
7			Penguin	TRUE
8			Dragon	TRUE
9			Mouse	TRUE
10			Pig	TRUE
11			Elephant	TRUE
12			Horse	TRUE
13			Cow	TRUE
14			Moose	TRUE
15			Octopus	TRUE
16			Fish	TRUE
17			Squid	TRUE
18			Turtle	TRUE
19			Cat	TRUE
20			Dog	TRUE
21			Ferret	TRUE
22			Dinosaur	TRUE
23			Bear	TRUE
24			Aardvark	TRUE
25			Tiger	FALSE
26			Lion	FALSE
27			Bee	FALSE
28			Owl	TRUE
29
Players

Then on the next sheet, try this:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H
1	Game 1	Court 1	Court 2	Court 3	Court 4	Court 5	Court 6	Court 7
2		Dragon	Bear	Penguin	Cat	Dog	Ferret
3		Bird	Duck	Pig	Cow	Fish
4		Owl	Octopus	Dinosaur	Moose	Horse
5		Aardvark	Mouse	Turtle	Elephant	Squid
6
7	Game 2	Court 1	Court 2	Court 3	Court 4	Court 5	Court 6	Court 7
8		Dog	Cat	Penguin	Bird	Dragon	Octopus
9		Mouse	Pig	Fish	Horse	Moose
10		Elephant	Turtle	Cow	Bear	Aardvark
11		Duck	Owl	Dinosaur	Squid	Ferret
12
13	Game 3	Court 1	Court 2	Court 3	Court 4	Court 5	Court 6	Court 7
14		Ferret	Dinosaur	Pig	Horse	Owl	Bear
15		Moose	Elephant	Cat	Aardvark	Squid
16		Dog	Bird	Turtle	Dragon	Fish
17		Cow	Octopus	Penguin	Duck	Mouse
18
19	Game 4	Court 1	Court 2	Court 3	Court 4	Court 5	Court 6	Court 7
20		Squid	Ferret	Aardvark	Penguin	Bird	Turtle
21		Dinosaur	Elephant	Cat	Horse	Fish
22		Moose	Bear	Mouse	Pig	Dragon
23		Octopus	Duck	Cow	Owl	Dog
24
25	Game 5	Court 1	Court 2	Court 3	Court 4	Court 5	Court 6	Court 7
26		Dragon	Octopus	Horse	Pig	Ferret	Turtle
27		Elephant	Moose	Penguin	Duck	Aardvark
28		Cat	Squid	Fish	Bird	Owl
29		Bear	Dog	Cow	Dinosaur	Mouse
Sheet2

Cell Formulas
Range	Formula
B2:G5,B26:G29,B20:G23,B14:G17,B8:G11	B2	=IFERROR(INDEX(SORTBY(FILTER(Players!$C$5:$C$30,Players!$D$5:$D$30),RANDARRAY(COUNTIF(Players!$D$5:$D$30,TRUE))),TRANSPOSE(SEQUENCE(ROUNDUP(COUNTIF(Players!$D$5:$D$30,TRUE)/4,0),4))),"")
Dynamic array formulas.

It randomly sorts all the names into 4-player groups, and repeats that for 5 games. It's just random, it doesn't incorporate any of the Social Golfer logic. It'll probably be good enough though. You might want to press F9 once or twice if there's some obvious issue you want to fix. In this example, Turtle sat out the last 2 games, so if you press F9 it will recalculate and you can make sure no one sits out more than once.

It's probably not worth building in the Social Golfer logic, but if you really wanted it, we'd have to randomly sort the names on the Players sheet, then use INDEX to organize them into the groups suggested by the Social golfer site. Like Stephen suggested, we'd save the tables for 5 rounds, 12 people, foursomes; 5 rounds, 16 people, foursomes; 5 rounds, 20 people, foursomes; etc.

 

[TerrorTot38]

How about this then? On your Player sheet, just put the list of names like this (no formulas):

Book1 (version 1).xlsb
	A	B	C	D
1
2
3
4			Player	Present
5			Duck	TRUE
6			Bird	TRUE
7			Penguin	TRUE
8			Dragon	TRUE
9			Mouse	TRUE
10			Pig	TRUE
11			Elephant	TRUE
12			Horse	TRUE
13			Cow	TRUE
14			Moose	TRUE
15			Octopus	TRUE
16			Fish	TRUE
17			Squid	TRUE
18			Turtle	TRUE
19			Cat	TRUE
20			Dog	TRUE
21			Ferret	TRUE
22			Dinosaur	TRUE
23			Bear	TRUE
24			Aardvark	TRUE
25			Tiger	FALSE
26			Lion	FALSE
27			Bee	FALSE
28			Owl	TRUE
29
Players

Then on the next sheet, try this:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H
1	Game 1	Court 1	Court 2	Court 3	Court 4	Court 5	Court 6	Court 7
2		Dragon	Bear	Penguin	Cat	Dog	Ferret
3		Bird	Duck	Pig	Cow	Fish
4		Owl	Octopus	Dinosaur	Moose	Horse
5		Aardvark	Mouse	Turtle	Elephant	Squid
6
7	Game 2	Court 1	Court 2	Court 3	Court 4	Court 5	Court 6	Court 7
8		Dog	Cat	Penguin	Bird	Dragon	Octopus
9		Mouse	Pig	Fish	Horse	Moose
10		Elephant	Turtle	Cow	Bear	Aardvark
11		Duck	Owl	Dinosaur	Squid	Ferret
12
13	Game 3	Court 1	Court 2	Court 3	Court 4	Court 5	Court 6	Court 7
14		Ferret	Dinosaur	Pig	Horse	Owl	Bear
15		Moose	Elephant	Cat	Aardvark	Squid
16		Dog	Bird	Turtle	Dragon	Fish
17		Cow	Octopus	Penguin	Duck	Mouse
18
19	Game 4	Court 1	Court 2	Court 3	Court 4	Court 5	Court 6	Court 7
20		Squid	Ferret	Aardvark	Penguin	Bird	Turtle
21		Dinosaur	Elephant	Cat	Horse	Fish
22		Moose	Bear	Mouse	Pig	Dragon
23		Octopus	Duck	Cow	Owl	Dog
24
25	Game 5	Court 1	Court 2	Court 3	Court 4	Court 5	Court 6	Court 7
26		Dragon	Octopus	Horse	Pig	Ferret	Turtle
27		Elephant	Moose	Penguin	Duck	Aardvark
28		Cat	Squid	Fish	Bird	Owl
29		Bear	Dog	Cow	Dinosaur	Mouse
Sheet2

Cell Formulas
Range	Formula
B2:G5,B26:G29,B20:G23,B14:G17,B8:G11	B2	=IFERROR(INDEX(SORTBY(FILTER(Players!$C$5:$C$30,Players!$D$5:$D$30),RANDARRAY(COUNTIF(Players!$D$5:$D$30,TRUE))),TRANSPOSE(SEQUENCE(ROUNDUP(COUNTIF(Players!$D$5:$D$30,TRUE)/4,0),4))),"")
Dynamic array formulas.

It randomly sorts all the names into 4-player groups, and repeats that for 5 games. It's just random, it doesn't incorporate any of the Social Golfer logic. It'll probably be good enough though. You might want to press F9 once or twice if there's some obvious issue you want to fix. In this example, Turtle sat out the last 2 games, so if you press F9 it will recalculate and you can make sure no one sits out more than once.

It's probably not worth building in the Social Golfer logic, but if you really wanted it, we'd have to randomly sort the names on the Players sheet, then use INDEX to organize them into the groups suggested by the Social golfer site. Like Stephen suggested, we'd save the tables for 5 rounds, 12 people, foursomes; 5 rounds, 16 people, foursomes; 5 rounds, 20 people, foursomes; etc.

Hi Eric,

This solution is what I am looking for however I copied the code into my 0365 on Sheet 2. This was the result:

Game 1	Court 1	Court 2	Court 3	Court 4	Court 5	Court 6	Court 7
	Cat	Moose	Penguin	Owl	Dinosaur
	Ardvark	Bear	Fish	Turtle	Pig
	Mouse	Octopus	Horse	Bird	Ferret
	Dog	Elephant	Cow	Squid
Game 2	Court 1	Court 2	Court 3	Court 4	Court 5	Court 6	Court 7
Game 3	Court 1	Court 2	Court 3	Court 4	Court 5	Court 6	Court 7
Game 4	Court 1	Court 2	Court 3	Court 4	Court 5	Court 6	Court 7
Game 5	Court 1	Court 2	Court 3	Court 4	Court 5	Court 6	Court 7

Any ideas what to do? As I'm new to this forum I'm unaware if you able to attach documents as I only have access to O365 online and not locally.

Thanks,
J

 

[Eric W]

It looks like game 1 worked ok. You should be able to just copy the B2 formula to B8, then B14, B20, and B26.

You can't attach documents directly here. Using the XL2BB tool (which I did) is as close as you can get, but I don't know if it works with O365 online. You can upload the file to a file sharing site, then post a link, but be aware that many people will not or can not download them for security reasons.

 

[TerrorTot38]

It looks like game 1 worked ok. You should be able to just copy the B2 formula to B8, then B14, B20, and B26.

You can't attach documents directly here. Using the XL2BB tool (which I did) is as close as you can get, but I don't know if it works with O365 online. You can upload the file to a file sharing site, then post a link, but be aware that many people will not or can not download them for security reasons.

Hi,

I tried the formula

=IFERROR(INDEX(SORTBY(FILTER(Players!$C$5:$C$30,Players!$D$5:$D$30),RANDARRAY(COUNTIF(Players!$D$5:$D$30,TRUE))),TRANSPOSE(SEQUENCE(ROUNDUP(COUNTIF(Players!$D$5:$D$30,TRUE)/4,0),4))),"")

in B2 but it comes up BLANK! Yet my data set is the same as yours. If I remove the IFERROR it displays #VALUE! error.

Any ideas why this could be happening? I've not even copied it down to B8, B14, B20 or B2 yet? I don't understand as the other day it was displaying fine.

Thanks,
Jess

 

[Eric W]

My first guess is that the TRUE/FALSE values in column D are not real TRUE/FALSE values, but text values instead. Try this:

=IFERROR(INDEX(SORTBY(FILTER(Players!$C$5:$C$30,Players!$D$5:$D$30="TRUE"),RANDARRAY(COUNTIF(Players!$D$5:$D$30,"TRUE"))),TRANSPOSE(SEQUENCE(ROUNDUP(COUNTIF(Players!$D$5:$D$30,"TRUE")/4,0),4))),"")

 

[TerrorTot38]

My first guess is that the TRUE/FALSE values in column D are not real TRUE/FALSE values, but text values instead. Try this:

=IFERROR(INDEX(SORTBY(FILTER(Players!$C$5:$C$30,Players!$D$5:$D$30="TRUE"),RANDARRAY(COUNTIF(Players!$D$5:$D$30,"TRUE"))),TRANSPOSE(SEQUENCE(ROUNDUP(COUNTIF(Players!$D$5:$D$30,"TRUE")/4,0),4))),"")

Hi Eric,

To check if this was the error I tried the formula above to no avail. I then changed my values from TRUE/FALSE to Yes and No. I then amended the formula as shown below:

=IFERROR(INDEX(SORTBY(FILTER(Players!$C$5:$C$30,Players!$D$5:$D$30="Yes"),RANDARRAY(COUNTIF(Players!$D$5:$D$30,"Yes"))),TRANSPOSE(SEQUENCE(,,,)+NCE(ROUNDUP(COUNTIF(Players!$D$5:$D$30,"Yes")/4,0),4))),"")

However this did not work either.

Any thoughts?

Thanks,
J

 

[Eric W]

That should have worked, I don't know why it didn't. Are you aware that you can use the mini-sheets that someone copied into a message to copy that to your Excel, even if you don't have XL2BB installed? Try this:

Open a blank workbook with 2 sheets in it. Rename one of the sheets to Players. Then go to my post #12 in this thread and click on the sheet icon:

Then go back to your workbook to the Players tab and select cell A1 and click Paste.

Go back to post #12, click on the sheet icon on the second mini-sheet, go to your Excel on the next tab, select A1 and click Paste.

See if that works for you and let me know.