Generate list of players for four school teams

[imcl75]

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.

 

[HighAndWilder]

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?

Not my solution. I started developing a solution which did spread the players out as evenly as possible but it used a mixture of VBA and functions instead of mostly functions. My solution took a tranche
of each year group allocated to each team instead of using a random selection. Snakehips will be able to advise.

 

[Snakehips]

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?

I did ask that question in my first response :

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?

Your response was:

Totally random is fine after the key year 6 players have been split up

Do you want me to try for a more even distribution of the ages?

 

[imcl75]

I did ask that question in my first response :

Your response was:

Do you want me to try for a more even distribution of the ages?

hi. it's still fine for it to be totally random. don't want to cause any hassle

 

[Snakehips]

If I get bored this evening and come up with a solution then I will post it.
Think I have an idea that will not be a great hassle other than it would require you to use a different character in each year group.
Eg. 'k' or 'x' in year six. Maybe 'f' for year four and 'v' for year five?

 

[imcl75]

If I get bored this evening and come up with a solution then I will post it.
Think I have an idea that will not be a great hassle other than it would require you to use a different character in each year group.
Eg. 'k' or 'x' in year six. Maybe 'f' for year four and 'v' for year five?

please don't spend time on this unless you are REALLY bored! I'm very happy to use it as it is. Team are made randomly (by hand) at present and that hasn't caused any issues so very happy to continue with the system of random assignment.

 

[Snakehips]

Maybe try this:
Teams sheet remains as was.
Players sheet now requires numeric input for players,
Typically, 4 for year four, 5 for year five, 6 for regular year six and 7 for KEY players.
Conditional formatting rules have changed to suit.
The vba code is now changed.

That should help spread the age groups randomly across the teams.
You could even downgrade a 'known to be useless' year six player by assigning them a 5 or a 4.
Or upgrade a brilliant year four player by giving them a 6.

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	4		CHy5-1			CHy6-1					CHy4-1	4	4.42803309		CHy6-2
3	CHy4-2			CHy5-2	5		CHy6-2	7				CHy4-4	4	4.19604002		CHy6-25
4	CHy4-3			CHy5-3			CHy6-3	6				CHy4-5	4	4.07818731		CHy6-10
5	CHy4-4	4		CHy5-4			CHy6-4	7				CHy4-6	4	4.38168736		CHy6-19
6	CHy4-5	4		CHy5-5	5		CHy6-5	6				CHy4-11	4	4.59057569		CHy6-16
7	CHy4-6	4		CHy5-6			CHy6-6					CHy4-12	4	4.38291265		CHy6-4
8	CHy4-7			CHy5-7	5		CHy6-7	6				CHy4-13	4	4.13364333		CHy6-15
9	CHy4-8			CHy5-8			CHy6-8	6				CHy4-18	4	4.01431423		CHy6-14
10	CHy4-9			CHy5-9			CHy6-9					CHy4-21	4	4.33236783		CHy6-17
11	CHy4-10			CHy5-10	5		CHy6-10	7				CHy4-24	4	4.38884337		CHy6-21
12	CHy4-11	4		CHy5-11	5		CHy6-11					CHy4-27	4	4.11009968		CHy6-23
13	CHy4-12	4		CHy5-12	5		CHy6-12	6				CHy5-2	5	5.84461348		CHy6-3
14	CHy4-13	4		CHy5-13	5		CHy6-13	6				CHy5-5	5	5.57283506		CHy6-13
15	CHy4-14			CHy5-14			CHy6-14	6				CHy5-7	5	5.42763156		CHy6-8
16	CHy4-15			CHy5-15			CHy6-15	6				CHy5-10	5	5.45333743		CHy6-7
17	CHy4-16			CHy5-16	5		CHy6-16	7				CHy5-11	5	5.98964271		CHy6-12
18	CHy4-17			CHy5-17			CHy6-17	6				CHy5-12	5	5.34998158		CHy6-5
19	CHy4-18	4		CHy5-18			CHy6-18					CHy5-13	5	5.57062803		CHy5-11
20	CHy4-19			CHy5-19	5		CHy6-19	7				CHy5-16	5	5.5447828		CHy5-22
21	CHy4-20			CHy5-20			CHy6-20					CHy5-19	5	5.25836925		CHy5-25
22	CHy4-21	4		CHy5-21			CHy6-21	6				CHy5-22	5	5.94992313		CHy5-2
23	CHy4-22			CHy5-22	5		CHy6-22					CHy5-25	5	5.85708721		CHy5-5
24	CHy4-23			CHy5-23			CHy6-23	6				CHy6-3	6	6.56462175		CHy5-13
25	CHy4-24	4		CHy5-24			CHy6-24					CHy6-5	6	6.0836028		CHy5-16
26	CHy4-25			CHy5-25	5		CHy6-25	7				CHy6-7	6	6.20484276		CHy5-10
27	CHy4-26			CHy5-26			CHy6-26					CHy6-8	6	6.40980707		CHy5-7
28	CHy4-27	4		CHy5-27			CHy6-27					CHy6-12	6	6.18182034		CHy5-12
29	CHy4-28			CHy5-28			CHy6-28					CHy6-13	6	6.44260325		CHy5-19
30	CHy4-29			CHy5-29			CHy6-29					CHy6-14	6	6.72306771		CHy4-11
31	CHy4-30			CHy5-30			CHy6-30					CHy6-15	6	6.7404562		CHy4-1
32												CHy6-17	6	6.65310689		CHy4-24
33												CHy6-21	6	6.63544584		CHy4-12
34												CHy6-23	6	6.56843027		CHy4-6
35												CHy6-2	7	7.91957556		CHy4-21
36												CHy6-4	7	7.2942979		CHy4-4
37												CHy6-10	7	7.76340805		CHy4-13
38												CHy6-16	7	7.61409708		CHy4-27
39												CHy6-19	7	7.6786385		CHy4-5
40												CHy6-25	7	7.7691039		CHy4-18
41
42
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=7	text	NO
G2:H101	Expression	=$H2>3	text	NO
D2:E101	Expression	=$E2>3	text	NO
A2:B101	Expression	=$B2>3	text	NO

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

 

[imcl75]

thanks!