Winning combination team for soccer players

[sam_owen]

Hello All,

Thanks for having this wonderful forum and I request your help with a small challenge of mine. I am new to excel and not sure if excel can do this at all, but I give it a try.

We have a small team of players(around 30) out of which around 20 who come every week to play soccer. The team is made by splitting those who came for the came on ground to try to make two equal teams. From the data collected, I am trying to figure out if a combination of players( 2/3/4) when in one team has a better chance of winning. With 'countif' I can see who win the most, but not which team can have more wins.
Put it simply does the team have more wins if Frank & Anthony play on one side or if Jacob,Kim and Nir plays on one side. WIN in the sheet below means the the player was in the winning team and LOST vice versa.

NAME	Wins	Loss	Game 1	Game 2	Game 3	Game 4	Game 5
Frank	2​	3​	WIN	WIN	LOST	Lost	LOST
Anthony	2​	3​	Lost	WIN	LOST	Win	LOST
Thomas	3​	2​	Win	WIN	WIN	Lost	LOST
Jacob	3​	2​	Lost	WIN	WIN	Win	LOST
Shib	2​	3​	Win	WIN	LOST	Lost	LOST
Marod	2​	3​	Lost	LOST	LOST	Win	WIN
Ire	2​	3​	Win	LOST	LOST	Lost	WIN
Jim	3​	2​	Lost	LOST	WIN	Win	WIN
Nir	3​	2​	Win	LOST	WIN	Lost	WIN
Kim	3​	2​	Lost	LOST	WIN	Win	WIN

Thanks very much and I am curious to know if this data or additional data can show some insights.

 

[Peter_SSs]

Welcome to the MrExcel board!

For future reference, don't put your table in any code tags. I have removed them for you on this occasion.

 

[Snakehips]

@sam_owen Maybe something like this?
Win / Lost data in columns K >>> AX
Use column J to select players present for Team 'A' or Team 'B'
Selected players show in columns C & D and the win count displays in C2 & D2
Alter your selections until you get the balance of wins that you are happy with.
Columns F & G are 'helper' columns that stop the formulas from getting ugly.
The helper columns may be hidden.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1			TEAM A	TEAM B		Helper A	Helper B			Select Team	NAME	Wins	Loss	Game 1	Game 2	Game 3	Game 4	Game 5
2		Win Count	4	9		2	999			A	Frank	2	3	WIN	WIN	LOST	Lost	LOST
3			PLAYERS	PLAYERS		3	999			A	Anthony	2	3	Lost	WIN	LOST	Win	LOST
4			Frank	Jacob		999	999				Thomas	3	2	Win	WIN	WIN	Lost	LOST
5			Anthony	Nir		999	5			B	Jacob	3	2	Lost	WIN	WIN	Win	LOST
6				Kim		999	999				Shib	2	3	Win	WIN	LOST	Lost	LOST
7						999	999				Marod	2	3	Lost	LOST	LOST	Win	WIN
8						999	999				Ire	2	3	Win	LOST	LOST	Lost	WIN
9						999	999				Jim	3	2	Lost	LOST	WIN	Win	WIN
10						999	10			B	Nir	3	2	Win	LOST	WIN	Lost	WIN
11						999	11			B	Kim	3	2	Lost	LOST	WIN	Win	WIN
12						999	999
Sheet3

Cell Formulas
Range	Formula
C2	C2	=SUMIF($J$1:$J$35,"A",$L$1:$L$35)
D2	D2	=SUMIF($J$1:$J$35,"B",$L$1:$L$35)
F2:F12	F2	=IF($J2="A",ROW(),999)
G2:G12	G2	=IF($J2="B",ROW(),999)
L2:L11	L2	=COUNTIF($N2:$AX2,"Win")
M2:M11	M2	=COUNTIF($N2:$AX2,"Lost")
C4:D12	C4	=IFERROR(INDEX($K$1:$K$34,SMALL(F$1:F$36,ROWS(C$4:C4))),"")

Cells with Data Validation
Cell	Allow	Criteria
J2:J12	List	A,B

Click the copy icon in the top l/h corner of the above XL2B Mini Sheet and you can then paste it into a worksheet to try it.
Hope that helps.

 

[Snakehips]

A slight tweak that may make it easier to see players that you may wish to swap?

Book1
	A	B	C	D	E
1			TEAM A	TEAM B
2		Win Count	7	9
3			PLAYERS	PLAYERS
4			Frank (2)	Jacob (3)
5			Anthony (2)	Nir (3)
6			Thomas (3)	Kim (3)
7
Sheet3

Cell Formulas
Range	Formula
C2	C2	=SUMIF($J$1:$J$35,"A",$L$1:$L$35)
D2	D2	=SUMIF($J$1:$J$35,"B",$L$1:$L$35)
C4:D7	C4	=IFERROR(INDEX($K$1:$K$34,SMALL(F$1:F$36,ROWS(C$4:C4)))&" ("&INDEX($L$1:$L$34,SMALL(F$1:F$36,ROWS(C$4:C4)))&")","")

 

[sam_owen]

@Snakehips Thank you very much for helping me out and I successfully managed to cope the file you presented into excel with your instructions. The second part greatly helps to look at combinations easily which is very helpful.May I please request/clarify one more thing?

As my limited knowledge goes the formula that you developed counts the win of players into one table and then adds them up to see who makes the most wins, which is wonderful. What I am trying to see is the combinative effect. As an example, If we look at the excel file, we can see that when Frank and Shib were in same team, they lost 3 games( Game 3,4,5) and Jim & Kim won 3 games when they were in same team (Game 3,4,5). Is it possible to calculate this compatibility? What we have in reality are that some strikers score more when paired with some mid-fielders due to their chemistry. Is there a way to calculate the probability/count or so, that would be brilliant.

Thanks once again!

 

[Snakehips]

Maybe this? Again using 'Helpers'. It will show basic win info to compare two partnerships, of up to five players, as selected by entering X or Y in columns K & L respectively.
You can always tweak the results it if you have other definable criteria that would better weight the result.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1			TEAM A	TEAM B		Helper A	Helper B	Helper X	Helper Y		X Partnership	Y Partnership	Select Team	NAME	Wins	Loss	Game 1	Game 2	Game 3	Game 4	Game 5
2		Win Count	7	9		2	999	2	999		X		A	Frank	2	3	WIN	WIN	LOST	Lost	LOST
3			PLAYERS	PLAYERS		3	999	999	999				A	Anthony	2	3	Lost	WIN	LOST	Win	LOST
4			Frank (2)	Jacob (3)		4	999	999	999				A	Thomas	3	2	Win	WIN	WIN	Lost	LOST
5			Anthony (2)	Nir (3)		999	5	999	999				B	Jacob	3	2	Lost	WIN	WIN	Win	LOST
6			Thomas (3)	Kim (3)		999	999	6	999		X			Shib	2	3	Win	WIN	LOST	Lost	LOST
7						999	999	999	999					Marod	2	3	Lost	LOST	LOST	Win	WIN
8						999	999	999	999					Ire	2	3	Win	LOST	LOST	Lost	WIN
9						999	999	999	9			Y		Jim	3	2	Lost	LOST	WIN	Win	WIN
10						999	10	999	999				B	Nir	3	2	Win	LOST	WIN	Lost	WIN
11						999	11	999	11			Y	B	Kim	3	2	Lost	LOST	WIN	Win	WIN
12						999	999	999	999
13						999	999	999	999
14						999	999	999	999
15						999	999	999	999
16						999	999	999	999
17						999	999	999	999
18						999	999	999	999
19						999	999	999	999
20			COMPARE PARTNERSHIPS			999	999	999	999
21		PARTNERSHIP	X	Y		999	999	999	999
22		Games Played	5	5		999	999	999	999
23		Games Won	2	3		999	999	999	999
24		Win %	40%	60%		999	999	999	999
25			PLAYERS	PLAYERS		999	999	999	999
26			Frank	Jim		999	999	999	999
27			Shib	Kim		999	999	999	999
28						999	999	999	999
29						999	999	999	999
30						999	999	999	999
31						999	999	999	999
32						999	999	999	999
33						999	999	999	999
34						999	999	999	999
35
36															Helper X	Win	1	1	0	0	0	0	0	0
37															Helper X	Lost	0	0	1	1	1	0	0	0
38															Helper Y	Win	0	0	1	1	1	0	0	0
39															Helper Y	Lost	1	1	0	0	0	0	0	0
40
Sheet3

Cell Formulas
Range	Formula
C2	C2	=SUMIF($M$1:$M$35,"A",$O$1:$O$35)
D2	D2	=SUMIF($M$1:$M$35,"B",$O$1:$O$35)
F2:F34	F2	=IF($M2="A",ROW(),999)
G2:G34	G2	=IF($M2="B",ROW(),999)
H2:H34	H2	=IF($K2="X",ROW(),999)
I2:I34	I2	=IF($L2="Y",ROW(),999)
O2:O11	O2	=COUNTIF($Q2:$BA2,"Win")
P2:P11	P2	=COUNTIF($Q2:$BA2,"Lost")
C4:D18	C4	=IFERROR(INDEX($N$1:$N$34,SMALL(F$1:F$36,ROWS(C$4:C4)))&" ("&INDEX($O$1:$O$34,SMALL(F$1:F$36,ROWS(C$4:C4)))&")","")
C22	C22	=SUM(Q36:BA37)
D22	D22	=SUM(Q38:BA39)
C23	C23	=SUM(Q36:BA36)
D23	D23	=SUM(Q38:BA38)
C24	C24	=IF(C22=0,"N/A",C23/C22)
D24	D24	=D23/D22
C26:D30	C26	=IFERROR(INDEX($N$1:$N$34,SMALL(H$1:H$36,ROWS(C$4:C4))),"")
Q36:X36	Q36	=--(COUNTIFS($K$2:$K$34,"X",Q$2:Q$34,"Win")=COUNTIF($K$2:$K34,"X"))
Q37:X37	Q37	=--(COUNTIFS($K$2:$K$34,"X",Q$2:Q$34,"Lost")=COUNTIF($K$2:$K$34,"X"))
Q38:X38	Q38	=--(COUNTIFS($L$2:$L$34,"Y",Q$2:Q$34,"Win")=COUNTIF($L$2:$L$34,"Y"))
Q39:X39	Q39	=--(COUNTIFS($L$2:$L$34,"Y",Q$2:Q$34,"Lost")=COUNTIF($L$2:$L$34,"Y"))

Cells with Data Validation
Cell	Allow	Criteria
M2:M35	List	A,B
K2:K34	List	X,
L2:L34	List	Y

Hope that helps.

 

[sam_owen]

@Snakehips That's wonderful!

I couldn't even explain the problem clearly and you have solved it in the most amazing way! I tried this with limited data we had of team and it's exactly what I was looking for!!!!

Two queries from my side for clarification!

1. The comparison limited to 5 players, can it be expanded to 10 or so by changing formula? Or is it an excel limitation?
2. Is there a better way to do what I am currently doing by: Getting the data of wins from last year-Use conditional formatting to colour 'Win" and 'Lost'. Then filter and choose the main players manually with most wins and finally subject them to analysis of this sheet to find better combinations. I can imagine something in VBA, which goes cell after cell to do something. But is there anything easier than to look at around 20 players with 50 games every year ~1000 cells?

Above are just queries only and you have helped solve an ambiguous issue I had in mind for many months now!

 

[Snakehips]

@Snakehips That's wonderful!

I couldn't even explain the problem clearly and you have solved it in the most amazing way! I tried this with limited data we had of team and it's exactly what I was looking for!!!!

Two queries from my side for clarification!

1. The comparison limited to 5 players, can it be expanded to 10 or so by changing formula? Or is it an excel limitation?
2. Is there a better way to do what I am currently doing by: Getting the data of wins from last year-Use conditional formatting to colour 'Win" and 'Lost'. Then filter and choose the main players manually with most wins and finally subject them to analysis of this sheet to find better combinations. I can imagine something in VBA, which goes cell after cell to do something. But is there anything easier than to look at around 20 players with 50 games every year ~1000 cells?

Above are just queries only and you have helped solve an ambiguous issue I had in mind for many months now!

@sam_owen You are welcome. Glad to have helped.

1. If you want to extend it to 10 players then just drag the formulas inC30:D30 down by another 5 rows. The reason I limited it to 5 was thinking that the more players you include, the less likely for it to be a combination that has played together before? If you choose a combo that has no history then the %win stat shows as N/A rather than 0% for a combo that has lost all matches when together.
2. Pass!
The key is you being able to identify/define how an interpretation of your past results will aid your team selection. If you can define it then one way or another, Excel will likely have the capability of achieving it.

Good luck with the Excel and the footy.

 

[sam_owen]

@Snakehips Thanks you great sir! You are a genius who solved our team issue!