Generate list of players for four school teams

imcl75 · Apr 3, 2024

Hi. I want to be able to create 4 football teams from a list of names which is updated weekly. The list starts as a list of all names (these are the names of all children in 3 year groups in my school) and to begin with I will enter an "x" next to their name if they want to play in the games that week. Then, I want to take all the children who have an x next to their name and randomly split them into 4 teams of (as close as possible) equal numbers. It would be useful to be able to have a few specific players from the Year 6 list (which I will identify) put into different teams rather than the same team so the best players get spread out . I've attached the sheet which I am starting with.... any help hugely appreciated as this will save me a lot of effort each week and stop me having to have loads of bits of paper with kids names on which I manually have to put into teams.

Snakehips · Apr 3, 2024

Hi @imcl75 I am happy to take a look at this for you in the hope that I can provide a helpful solution.
Given the essential need for a random split, will involve using an Excel 'random' function. Random functions are 'volatile' and as such their values change every time a spreadsheet is triggered to recalculate. There are ways to 'freeze' a set of randomly generated values. If you are running vba code,a Macro, to fulfil your task then it is no great problem to code to 'freeze' any randomly generated value. Otherwise, if you are reliant upon cell formulas, then there will likely be a need for the user to 'freeze' the randomly generated values at some point by a quick copy paste or similar.
Unless you tell me otherwise, I will assume that you may not wish or even be allowed to use vba?

A couple of other questions spring to mind.
Will numbers always be such that you will split total x's int 4 teams, irrespective of the number of x's per team?
Stupid extreme would be 12 x's = 3 per team

After you have placed the 'key' year sixers across the teams is it just a random free for all with the balance of the players?
Or is it better to attempt to spread each year separately over each of the 4 teams?

The more I type, the more I wonder whether using vba might not be an easier solution.
In ant event I will not be able to attempt do anything positive until tomorrow.

imcl75 · Apr 3, 2024

Snakehips said:
Hi @imcl75 I am happy to take a look at this for you in the hope that I can provide a helpful solution.
Given the essential need for a random split, will involve using an Excel 'random' function. Random functions are 'volatile' and as such their values change every time a spreadsheet is triggered to recalculate. There are ways to 'freeze' a set of randomly generated values. If you are running vba code,a Macro, to fulfil your task then it is no great problem to code to 'freeze' any randomly generated value. Otherwise, if you are reliant upon cell formulas, then there will likely be a need for the user to 'freeze' the randomly generated values at some point by a quick copy paste or similar.
Unless you tell me otherwise, I will assume that you may not wish or even be allowed to use vba?

A couple of other questions spring to mind.
Will numbers always be such that you will split total x's int 4 teams, irrespective of the number of x's per team?
Stupid extreme would be 12 x's = 3 per team

After you have placed the 'key' year sixers across the teams is it just a random free for all with the balance of the players?
Or is it better to attempt to spread each year separately over each of the 4 teams?

The more I type, the more I wonder whether using vba might not be an easier solution.
In ant event I will not be able to attempt do anything positive until tomorrow.

Thanks for the fast reply! Am happy to use macros / vba but very novice there (can record macros, assign to short cuts, buttons etc but don't know vba coding)

Once the lists (teams) are generated I will print them so if they changed after that it wouldn't be a problem. Triggering via a macro would be ideal.

There will always be 4 teams as we always get plenty of players interested. Totally random is fine after the key year 6 players have been split up. Whatever, and whenever you can take a look is great. Thanks again

Snakehips · Apr 3, 2024

Do you have a sensible likely maximum for number of names in the year lists?
Also a sensible max for players per team?

imcl75 · Apr 3, 2024

Snakehips said:
Do you have a sensible likely maximum for number of names in the year lists?
Also a sensible max for players per team?

There are about 60 children in each year group but lots of them never play. Maximum number would realistically be 100 and even that is in excess of real world number. 20 or so per team is a maximum - 20-a-side makes for a terrible game of football!

Snakehips · Apr 4, 2024

@imcl75 Ok, here it is.
Essentially, I chose to utilise standard Excel 365 functionality. To be honest, I didn't see the volatility of Rand() being too much of of an annoyance for the user. However, I have just included a small macro that effectively avoids the volatility. It would be a very simple matter to switch it back to not requiring via at all if you wish.

As it stands, you will need a button in the 'Teams' sheet and assign to it the macro 'RandTeams'
There are 'helper' columns in columns L:P of the Players sheet. You can choose to hide these if you wish.
In the 'Players' sheet:
In general, you will edit the year lists to have an 'x' assigned to the name of intended players.
However, in year 6 only, you will need to assign 'k' rather than 'x' to denote any 'key' players who you wish to ensure be randomly split across the four teams.

In the 'Teams' sheet:
To refresh the randomisation of the teams - Click the button to run the 'RandTeams' macro.

Picking Teams.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

1

Year 4

Year 5

Year 6

Player Pool

Ref

Random

Random Names

2

CHy4-1

x

CHy5-1

CHy6-1

CHy6-2

k

1.63204148

CHy6-4

3

CHy4-2

CHy5-2

x

CHy6-2

k

CHy6-4

k

1.88278006

CHy6-16

4

CHy4-3

CHy5-3

CHy6-3

x

CHy6-10

k

1.04794096

CHy6-2

5

CHy4-4

x

CHy5-4

CHy6-4

k

CHy6-16

k

1.65176345

CHy6-25

6

CHy4-5

x

CHy5-5

x

CHy6-5

x

CHy6-25

k

1.38310405

CHy6-10

7

CHy4-6

x

CHy5-6

CHy6-6

CHy4-1

x

0.05419298

CHy4-11

8

CHy4-7

CHy5-7

x

CHy6-7

x

CHy4-4

x

0.04868209

CHy4-13

9

CHy4-8

CHy5-8

CHy6-8

x

CHy4-5

x

0.77568949

CHy6-17

10

CHy4-9

CHy5-9

CHy6-9

CHy4-6

x

0.21191974

CHy5-5

11

CHy4-10

CHy5-10

x

CHy6-10

k

CHy4-11

x

0.99049223

CHy6-13

12

CHy4-11

x

CHy5-11

x

CHy6-11

CHy4-12

x

0.09497617

CHy6-5

13

CHy4-12

x

CHy5-12

x

CHy6-12

x

CHy4-13

x

0.94356146

CHy4-5

14

CHy4-13

x

CHy5-13

x

CHy6-13

x

CHy4-18

x

0.07302424

CHy4-24

15

CHy4-14

CHy5-14

CHy6-14

x

CHy4-21

x

0.30359707

CHy5-10

16

CHy4-15

CHy5-15

CHy6-15

x

CHy4-24

x

0.76983619

CHy6-21

17

CHy4-16

CHy5-16

x

CHy6-16

k

CHy4-27

x

0.56492531

CHy5-16

18

CHy4-17

CHy5-17

CHy6-17

x

CHy5-2

x

0.56241888

CHy5-11

19

CHy4-18

x

CHy5-18

CHy6-18

CHy5-5

x

0.82310079

CHy5-22

20

CHy4-19

CHy5-19

x

CHy6-19

x

CHy5-7

x

0.33253302

CHy5-19

21

CHy4-20

CHy5-20

CHy6-20

CHy5-10

x

0.76485303

CHy5-25

22

CHy4-21

x

CHy5-21

CHy6-21

x

CHy5-11

x

0.65203706

CHy6-7

23

CHy4-22

CHy5-22

x

CHy6-22

CHy5-12

x

0.27236244

CHy4-27

24

CHy4-23

CHy5-23

CHy6-23

x

CHy5-13

x

0.28334031

CHy5-2

25

CHy4-24

x

CHy5-24

CHy6-24

CHy5-16

x

0.74977019

CHy6-15

26

CHy4-25

CHy5-25

x

CHy6-25

k

CHy5-19

x

0.59206734

CHy6-12

27

CHy4-26

CHy5-26

CHy6-26

CHy5-22

x

0.59593102

CHy6-3

28

CHy4-27

x

CHy5-27

CHy6-27

CHy5-25

x

0.58617269

CHy6-23

29

CHy4-28

CHy5-28

CHy6-28

CHy6-3

x

0.38010436

CHy6-14

30

CHy4-29

CHy5-29

CHy6-29

CHy6-5

x

0.78985268

CHy5-7

31

CHy4-30

CHy5-30

CHy6-30

CHy6-7

x

0.57727037

CHy4-21

32

CHy6-8

x

0.02654532

CHy5-13

33

CHy6-12

x

0.43208187

CHy5-12

34

CHy6-13

x

0.8117463

CHy4-6

35

CHy6-14

x

0.34333776

CHy4-12

36

CHy6-15

x

0.44813105

CHy4-18

37

CHy6-17

x

0.89539295

CHy4-1

38

CHy6-19

x

0.00074591

CHy4-4

39

CHy6-21

x

0.76169693

CHy6-8

40

CHy6-23

x

0.37824756

CHy6-19

41

42

43

Players

Cell Formulas
Range	Formula
L2:M40	L2	=LET(plyrs,SORT((VSTACK(A2:B101,D2:E101,G2:H101)),2,1),FILTER(plyrs,INDEX(plyrs,,2)<>0))
P2:P40	P2	=LET(rnd,SORTBY(L2:L101,N2:N101,-1),FILTER(rnd,rnd<>0,))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G2:H101	Expression	=$H2="x"	text	NO
D2:E60	Expression	=$E2="x"	text	NO
A2:B60	Expression	=$B2="x"	text	NO
G2:H101	Expression	=$H2="k"	text	YES

Picking Teams.xlsm

A

B

C

D

1

Team A

Team B

Team C

Team D

2

CHy6-4

CHy6-16

CHy6-2

CHy6-25

3

CHy6-10

CHy4-11

CHy4-13

CHy6-17

4

CHy5-5

CHy6-13

CHy6-5

CHy4-5

5

CHy4-24

CHy5-10

CHy6-21

CHy5-16

6

CHy5-11

CHy5-22

CHy5-19

CHy5-25

7

CHy6-7

CHy4-27

CHy5-2

CHy6-15

8

CHy6-12

CHy6-3

CHy6-23

CHy6-14

9

CHy5-7

CHy4-21

CHy5-13

CHy5-12

10

CHy4-6

CHy4-12

CHy4-18

CHy4-1

11

CHy4-4

CHy6-8

CHy6-19

12

13

14

15

16

17

18

19

20

21

22

23

24

Teams

Cell Formulas
Range	Formula
A2:D21	A2	=LET(tmem,INDEX(Players!P2:P101,SEQUENCE(20,4,1,1),),IF(tmem=0,"",tmem))
Dynamic array formulas.

VBA:

VBA Code:

Sub RandTeams()
    With Sheets("Players").Range("N2:N101")
       .Formula = "=SORT(IF(L2="""","""",IF(M2=""x"",RAND(),RAND()+1)),,-1)"
        .Value = .Value
    End With
    Sheets("Teams").Select
End Sub

Edit: I see that I have an incorrect end of range row for a couple of the CF formulas. Should be 101 not 60. !!

Hope that helps?

imcl75 · Apr 4, 2024

Snakehips said:
@imcl75 Ok, here it is.
Essentially, I chose to utilise standard Excel 365 functionality. To be honest, I didn't see the volatility of Rand() being too much of of an annoyance for the user. However, I have just included a small macro that effectively avoids the volatility. It would be a very simple matter to switch it back to not requiring via at all if you wish.

As it stands, you will need a button in the 'Teams' sheet and assign to it the macro 'RandTeams'
There are 'helper' columns in columns L:P of the Players sheet. You can choose to hide these if you wish.
In the 'Players' sheet:
In general, you will edit the year lists to have an 'x' assigned to the name of intended players.
However, in year 6 only, you will need to assign 'k' rather than 'x' to denote any 'key' players who you wish to ensure be randomly split across the four teams.

In the 'Teams' sheet:
To refresh the randomisation of the teams - Click the button to run the 'RandTeams' macro.

Picking Teams.xlsm
A B C D E F G H I J K L M N O P
1 Year 4 Year 5 Year 6 Player Pool Ref Random Random Names
2 CHy4-1 x CHy5-1 CHy6-1 CHy6-2 k 1.63204148 CHy6-4
3 CHy4-2 CHy5-2 x CHy6-2 k CHy6-4 k 1.88278006 CHy6-16
4 CHy4-3 CHy5-3 CHy6-3 x CHy6-10 k 1.04794096 CHy6-2
5 CHy4-4 x CHy5-4 CHy6-4 k CHy6-16 k 1.65176345 CHy6-25
6 CHy4-5 x CHy5-5 x CHy6-5 x CHy6-25 k 1.38310405 CHy6-10
7 CHy4-6 x CHy5-6 CHy6-6 CHy4-1 x 0.05419298 CHy4-11
8 CHy4-7 CHy5-7 x CHy6-7 x CHy4-4 x 0.04868209 CHy4-13
9 CHy4-8 CHy5-8 CHy6-8 x CHy4-5 x 0.77568949 CHy6-17
10 CHy4-9 CHy5-9 CHy6-9 CHy4-6 x 0.21191974 CHy5-5
11 CHy4-10 CHy5-10 x CHy6-10 k CHy4-11 x 0.99049223 CHy6-13
12 CHy4-11 x CHy5-11 x CHy6-11 CHy4-12 x 0.09497617 CHy6-5
13 CHy4-12 x CHy5-12 x CHy6-12 x CHy4-13 x 0.94356146 CHy4-5
14 CHy4-13 x CHy5-13 x CHy6-13 x CHy4-18 x 0.07302424 CHy4-24
15 CHy4-14 CHy5-14 CHy6-14 x CHy4-21 x 0.30359707 CHy5-10
16 CHy4-15 CHy5-15 CHy6-15 x CHy4-24 x 0.76983619 CHy6-21
17 CHy4-16 CHy5-16 x CHy6-16 k CHy4-27 x 0.56492531 CHy5-16
18 CHy4-17 CHy5-17 CHy6-17 x CHy5-2 x 0.56241888 CHy5-11
19 CHy4-18 x CHy5-18 CHy6-18 CHy5-5 x 0.82310079 CHy5-22
20 CHy4-19 CHy5-19 x CHy6-19 x CHy5-7 x 0.33253302 CHy5-19
21 CHy4-20 CHy5-20 CHy6-20 CHy5-10 x 0.76485303 CHy5-25
22 CHy4-21 x CHy5-21 CHy6-21 x CHy5-11 x 0.65203706 CHy6-7
23 CHy4-22 CHy5-22 x CHy6-22 CHy5-12 x 0.27236244 CHy4-27
24 CHy4-23 CHy5-23 CHy6-23 x CHy5-13 x 0.28334031 CHy5-2
25 CHy4-24 x CHy5-24 CHy6-24 CHy5-16 x 0.74977019 CHy6-15
26 CHy4-25 CHy5-25 x CHy6-25 k CHy5-19 x 0.59206734 CHy6-12
27 CHy4-26 CHy5-26 CHy6-26 CHy5-22 x 0.59593102 CHy6-3
28 CHy4-27 x CHy5-27 CHy6-27 CHy5-25 x 0.58617269 CHy6-23
29 CHy4-28 CHy5-28 CHy6-28 CHy6-3 x 0.38010436 CHy6-14
30 CHy4-29 CHy5-29 CHy6-29 CHy6-5 x 0.78985268 CHy5-7
31 CHy4-30 CHy5-30 CHy6-30 CHy6-7 x 0.57727037 CHy4-21
32 CHy6-8 x 0.02654532 CHy5-13
33 CHy6-12 x 0.43208187 CHy5-12
34 CHy6-13 x 0.8117463 CHy4-6
35 CHy6-14 x 0.34333776 CHy4-12
36 CHy6-15 x 0.44813105 CHy4-18
37 CHy6-17 x 0.89539295 CHy4-1
38 CHy6-19 x 0.00074591 CHy4-4
39 CHy6-21 x 0.76169693 CHy6-8
40 CHy6-23 x 0.37824756 CHy6-19
41
42
43
Players
Cell Formulas
Range Formula
L2:M40 L2 =LET(plyrs,SORT((VSTACK(A2:B101,D2:E101,G2:H101)),2,1),FILTER(plyrs,INDEX(plyrs,,2)<>0))
P2:P40 P2 =LET(rnd,SORTBY(L2:L101,N2:N101,-1),FILTER(rnd,rnd<>0,))
Dynamic array formulas.
Cells with Conditional Formatting
Cell Condition Cell Format Stop If True
G2:H101 Expression =$H2="x" text NO
D2:E60 Expression =$E2="x" text NO
A2:B60 Expression =$B2="x" text NO
G2:H101 Expression =$H2="k" text YES

Picking Teams.xlsm
A B C D
1 Team A Team B Team C Team D
2 CHy6-4 CHy6-16 CHy6-2 CHy6-25
3 CHy6-10 CHy4-11 CHy4-13 CHy6-17
4 CHy5-5 CHy6-13 CHy6-5 CHy4-5
5 CHy4-24 CHy5-10 CHy6-21 CHy5-16
6 CHy5-11 CHy5-22 CHy5-19 CHy5-25
7 CHy6-7 CHy4-27 CHy5-2 CHy6-15
8 CHy6-12 CHy6-3 CHy6-23 CHy6-14
9 CHy5-7 CHy4-21 CHy5-13 CHy5-12
10 CHy4-6 CHy4-12 CHy4-18 CHy4-1
11 CHy4-4 CHy6-8 CHy6-19
12
13
14
15
16
17
18
19
20
21
22
23
24
Teams
Cell Formulas
Range Formula
A2:D21 A2 =LET(tmem,INDEX(Players!P2:P101,SEQUENCE(20,4,1,1),),IF(tmem=0,"",tmem))
Dynamic array formulas.

VBA:

VBA Code:

Sub RandTeams() With Sheets("Players").Range("N2:N101") .Formula = "=SORT(IF(L2="""","""",IF(M2=""x"",RAND(),RAND()+1)),,-1)" .Value = .Value End With Sheets("Teams").Select End Sub

Edit: I see that I have an incorrect end of range row for a couple of the CF formulas. Should be 101 not 60. !!

Hope that helps?

This works perfectly - thank you so much. I hope it wasn't a lot of work!

Snakehips · Apr 4, 2024

imcl75 said:
This works perfectly - thank you so much. I hope it wasn't a lot of work!

Great stuff - you are most welcome. Just be thankful you're not having to pay me by the hour!

HighAndWilder · Apr 4, 2024

imcl75 said:
This works perfectly - thank you so much. I hope it wasn't a lot of work!

Would a more equal allocation between the years be fairer?

imcl75 · Apr 4, 2024

HighAndWilder said:
Would a more equal allocation between the years be fairer?

I suppose yes, i.e. spreading out the children from the three different year groups across the 4 teams so one team doesn't end up with loads of the Y6 children - who are older and stronger if not actually better players. Is that an easy change?

Generate list of players for four school teams

imcl75

New Member

Excel Facts

Snakehips

Well-known Member

imcl75

New Member

Snakehips

Well-known Member

imcl75

New Member

Snakehips

Well-known Member

imcl75

New Member

Snakehips

Well-known Member

HighAndWilder

Active Member

imcl75

New Member

Similar threads

Forum statistics

Share this page

Generate list of players for four school teams

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Active Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock