Excel/VBA Soccer/Football Lineup Generator

[haluxuxjr]

Hi, everybody!

I would like to request some assistance for the following: each weekend me and a group of friends play a football match ( 7vs7 ), and we always have trouble when making the starting lineups, so I want to create a formula or a macro that can create random, balanced teams based on the player's positions and skills.

Initially I have 3 columns: player name, position (GK - goalkeeper, DF - defender and AT - attacker) and skill (from 1 to 5).

I have to fill a lineup that deploys 2 teams with 1 GK, 3 DF and 3 AT each, trying that each squad is balanced (skill) and the players play in their preferred position.

It would be very interesting to find a way to add this consideration into the lineup generator.

Thank you in advance!

 

[Eric W]

Welcome to the MrExcel forum!

A few years back, I wrote a macro that pretty much does what you want. And it appears you found a copy of it in the other thread you are in. The main difference is that that macro has the MF position. You could probably make it work by assigning some of your players as MF, or I could update the macro. But with the advent of some new functions, I tried to do it with formulas. Consider:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Name	Position	Skill		Team A	Total Skill	Team B	Total Skill	Difference		Team A	Total Skill	Team B	Total Skill	Difference
2	a	GK	2		a,c,d,e,i,j,k	21	b,h,g,f,n,m,l	23	2		a,c,d,e,i,j,n	22	b,h,g,f,n,m,i	22	0
3	b	GK	4		a,c,d,e,i,j,l	21	b,h,g,f,n,m,k	23	2		a,c,d,e,i,k,n	22	b,h,g,f,n,l,i	22	0
4	c	DF	3		a,c,d,e,i,j,m	18	b,h,g,f,n,m,j	26	8		a,c,d,e,i,l,n	22	b,h,g,f,n,k,i	22	0
5	d	DF	5		a,c,d,e,i,j,n	22	b,h,g,f,n,m,i	22	0		a,c,d,f,i,j,k	22	b,h,g,e,n,m,l	22	0
6	e	DF	1		a,c,d,e,i,k,l	21	b,h,g,f,n,l,k	23	2		a,c,d,f,i,j,l	22	b,h,g,e,n,m,k	22	0
7	f	DF	2		a,c,d,e,i,k,m	18	b,h,g,f,n,l,j	26	8		a,c,d,f,i,k,l	22	b,h,g,e,n,l,k	22	0
8	g	DF	3		a,c,d,e,i,k,n	22	b,h,g,f,n,l,i	22	0		a,c,d,f,j,m,n	22	b,h,g,e,m,j,i	22	0
9	h	DF	4		a,c,d,e,i,l,m	18	b,h,g,f,n,k,j	26	8		a,c,d,f,k,m,n	22	b,h,g,e,l,j,i	22	0
10	i	AT	2		a,c,d,e,i,l,n	22	b,h,g,f,n,k,i	22	0		a,c,d,f,l,m,n	22	b,h,g,e,k,j,i	22	0
11	j	AT	4		a,c,d,e,i,m,n	19	b,h,g,f,n,j,i	25	6		a,c,d,g,j,k,m	22	b,h,g,d,m,l,j	22	0
12	k	AT	4		a,c,d,e,j,k,l	23	b,h,g,f,m,l,k	21	2		a,c,d,g,j,l,m	22	b,h,g,d,m,k,j	22	0
13	l	AT	4		a,c,d,e,j,k,m	20	b,h,g,f,m,l,j	24	4		a,c,d,g,k,l,m	22	b,h,g,d,l,k,j	22	0
14	m	AT	1		a,c,d,e,j,k,n	24	b,h,g,f,m,l,i	20	4		a,c,d,h,i,m,n	22	b,h,g,c,n,j,i	22	0
15	n	AT	5		a,c,d,e,j,l,m	20	b,h,g,f,m,k,j	24	4		a,c,e,g,j,k,n	22	b,h,f,d,m,l,i	22	0
16					a,c,d,e,j,l,n	24	b,h,g,f,m,k,i	20	4		a,c,e,g,j,l,n	22	b,h,f,d,m,k,i	22	0
17					a,c,d,e,j,m,n	21	b,h,g,f,m,j,i	23	2		a,c,e,g,k,l,n	22	b,h,f,d,l,k,i	22	0
18					a,c,d,e,k,l,m	20	b,h,g,f,l,k,j	24	4		a,c,e,h,j,k,l	22	b,h,f,c,m,l,k	22	0
19					a,c,d,e,k,l,n	24	b,h,g,f,l,k,i	20	4		a,c,f,g,j,k,l	22	b,h,e,d,m,l,k	22	0
20					a,c,d,e,k,m,n	21	b,h,g,f,l,j,i	23	2		a,c,f,h,i,j,n	22	b,h,e,c,n,m,i	22	0
21					a,c,d,e,l,m,n	21	b,h,g,f,k,j,i	23	2		a,c,f,h,i,k,n	22	b,h,e,c,n,l,i	22	0
22					a,c,d,f,i,j,k	22	b,h,g,e,n,m,l	22	0		a,c,f,h,i,l,n	22	b,h,e,c,n,k,i	22	0
Sheet4

Cell Formulas
Range	Formula
E2:E801	E2	=LET(d,A2:C15,gk,FILTER(d,B2:B15="gk"),df,FILTER(d,B2:B15="df"),at,FILTER(d,B2:B15="at"),s,BASE(SEQUENCE(93312,,0),6,7),t,IF((MID(s,2,1)>=MID(s,3,1))+(MID(s,2,1)>=MID(s,4,1))+(MID(s,3,1)>=MID(s,4,1))+(MID(s,5,1)>=MID(s,6,1))+(MID(s,5,1)>=MID(s,7,1))+(MID(s,6,1)>=MID(s,7,1)),"",INDEX(gk,LEFT(s)+1,1)&","&INDEX(df,MID(s,2,1)+1,1)&","&INDEX(df,MID(s,3,1)+1,1)&","&INDEX(df,MID(s,4,1)+1,1)&","&INDEX(at,MID(s,5,1)+1,1)&","&INDEX(at,MID(s,6,1)+1,1)&","&INDEX(at,MID(s,7,1)+1,1)),tf,FILTER(t,t<>""),tf)
F2:F801	F2	=LET(d,A2:C15,gk,FILTER(d,B2:B15="gk"),df,FILTER(d,B2:B15="df"),at,FILTER(d,B2:B15="at"),s,BASE(SEQUENCE(93312,,0),6,7),t,IF((MID(s,2,1)>=MID(s,3,1))+(MID(s,2,1)>=MID(s,4,1))+(MID(s,3,1)>=MID(s,4,1))+(MID(s,5,1)>=MID(s,6,1))+(MID(s,5,1)>=MID(s,7,1))+(MID(s,6,1)>=MID(s,7,1)),"",INDEX(gk,LEFT(s)+1,3)+INDEX(df,MID(s,2,1)+1,3)+INDEX(df,MID(s,3,1)+1,3)+INDEX(df,MID(s,4,1)+1,3)+INDEX(at,MID(s,5,1)+1,3)+INDEX(at,MID(s,6,1)+1,3)+INDEX(at,MID(s,7,1)+1,3)),tf,FILTER(t,t<>""),tf)
G2:G801	G2	=LET(d,A2:C15,gk,FILTER(d,B2:B15="gk"),df,FILTER(d,B2:B15="df"),at,FILTER(d,B2:B15="at"),s,BASE(SEQUENCE(93312,,0),6,7),t,IF((MID(s,2,1)>=MID(s,3,1))+(MID(s,2,1)>=MID(s,4,1))+(MID(s,3,1)>=MID(s,4,1))+(MID(s,5,1)>=MID(s,6,1))+(MID(s,5,1)>=MID(s,7,1))+(MID(s,6,1)>=MID(s,7,1)),"",INDEX(gk,2-LEFT(s),1)&","&INDEX(df,6-MID(s,2,1),1)&","&INDEX(df,6-MID(s,3,1),1)&","&INDEX(df,6-MID(s,4,1),1)&","&INDEX(at,6-MID(s,5,1),1)&","&INDEX(at,6-MID(s,6,1),1)&","&INDEX(at,6-MID(s,7,1),1)),tf,FILTER(t,t<>""),tf)
H2:H801	H2	=SUM(C2:C15)-F2#
I2:I801	I2	=ABS(F2#-H2#)
K2:O801	K2	=SORTBY(E2:I801,I2:I801)
Dynamic array formulas.

I started with the table in A1:C15. It doesn't need to be sorted by position, but there must be EXACTLY 2 GK, 6 DF, and 6 AT. Then the formula at E2 figures out every combination of a valid team, F2 gets the total skill, G2 gets the opposite team, H2 gets the opposite teams skill, and I2 gets the difference. Then the K2 formula merely sorts that table by difference. So you can see right off what combinations will work for you. I considered adding a RANDARRAY to it, so that instead of showing everything in the same order every time, it would mix up all the teams with the same difference. That formula would look like:

=SORTBY(E2:I801,I2:I801,1,RANDARRAY(800),1)

Let me know what you think!