How to tally tennis court pairings?

[JenniferMurphy]

Once a month, each member of our tennis group submits the days that they are available to play doubles. The person in charge of court scheduling then assigns the available players to a court. We have been doing this for years. The scheduler tries to mix up the players so that we each one gets a chance to play with as many dfferent players as possible.

I thought it might be interesting to tally the number of times each player has been paired with each other player. With N players, there would be N * (N-1) / 2 2-player pairings. We have about 20 players. That would be 19 pairings.

Here is some sample data with just 6 players (= 15 pairings). There are 3 tables. TblPlayers is a list of the players. TblCourts is a list of the court assignments. TblTallies is a list of the pairings with tallies for each. The first 2 tables are the input data. TblTallies is the one that needs to be generated. I don't see any way to do that in Excel, so my plan is to write a macro. I would appreciate any suggestions.

Tennis Pairings.xlsx
	B	C	D	E	F	G
3	TblPlayers		TblCourts		TblTallies
4	Name		Court Assignment		Pairing	Tally
5	Debbie		Jill Nancy Debbie Emily		Emily Linda	0
6	Emily		Susan Jill Debbie Linda		Emily Jill	1
7	Jill		Nancy Jill Debbie Linda		Linda Susan	1
8	Linda		Nancy Susan Emily Debbie		Emily Susan	1
9	Nancy		Debbie Nancy Susan Jill		Linda Nancy	1
10	Susan		5		Debbie Emily	2
11	6				Debbie Linda	2
12					Emily Nancy	2
13					Jill Linda	2
14					Jill Susan	2
15					Nancy Susan	2
16					Debbie Susan	3
17					Jill Nancy	3
18					Debbie Jill	4
19					Debbie Nancy	4
20					15	30
Sheet1

Cell Formulas
Range	Formula
D10	D10	=SUBTOTAL(103,[Court Assignment])
B11	B11	=SUBTOTAL(103,[Name])
F20	F20	=SUBTOTAL(103,[Pairing])
G20	G20	=SUBTOTAL(109,[Tally])

I added the table names aboe each table because xl2bb does not report table names.

 

[Fluff]

How about

=COUNTIFS($D$5:$D$9,"*"&TEXTBEFORE(F5," ")&"*",$D$5:$D$9,"*"&TEXTAFTER(F5," ")&"*")

 

[JenniferMurphy]

How about

=COUNTIFS($D$5:$D$9,"*"&TEXTBEFORE(F5," ")&"*",$D$5:$D$9,"*"&TEXTAFTER(F5," ")&"*")

Wow!!! That seems to work. Very nice. I modified your code to use table notation. My manual tallies are Tally1, yours are Tally2.

Tennis Pairings.xlsx
	B	C	D	E	F	G	H
3	TblPlayers		TblCourts		TblTallies
4	Name		Court Assignment		Pairing	Tally1	Tally2
5	Debbie		Jill Nancy Debbie Emily		Emily Linda	0	0
6	Emily		Susan Jill Debbie Linda		Emily Jill	1	1
7	Jill		Nancy Jill Debbie Linda		Linda Susan	1	1
8	Linda		Nancy Susan Emily Debbie		Emily Susan	1	1
9	Nancy		Debbie Nancy Susan Jill		Linda Nancy	1	1
10	Susan		5		Debbie Emily	2	2
11	6				Debbie Linda	2	2
12					Emily Nancy	2	2
13					Jill Linda	2	2
14					Jill Susan	2	2
15					Nancy Susan	2	2
16					Debbie Susan	3	3
17					Jill Nancy	3	3
18					Debbie Jill	4	4
19					Debbie Nancy	4	4
20					15	30	30
Sheet1

Cell Formulas
Range	Formula
D10	D10	=SUBTOTAL(103,[Court Assignment])
B11	B11	=SUBTOTAL(103,[Name])
H5:H19	H5	=COUNTIFS(TblCourts[Court Assignment],"*"&TEXTBEFORE([@Pairing]," ")&"*",TblCourts[Court Assignment],"*"&TEXTAFTER([@Pairing]," ")&"*")
F20	F20	=SUBTOTAL(103,[Pairing])
G20	G20	=SUBTOTAL(109,[Tally1])
H20	H20	=SUBTOTAL(109,[Tally2])

And it continues to work if I add more Court Assignments.

Tennis Pairings.xlsx
	B	C	D	E	F	G
3	TblPlayers		TblCourts		TblTallies
4	Name		Court Assignment		Pairing	Tally2
5	Debbie		Jill Nancy Debbie Emily		Emily Linda	1
6	Emily		Susan Jill Debbie Linda		Linda Nancy	1
7	Jill		Nancy Jill Debbie Linda		Emily Jill	2
8	Linda		Nancy Susan Emily Debbie		Linda Susan	2
9	Nancy		Debbie Nancy Susan Jill		Jill Linda	2
10	Susan		Debbie Susan Emily Linda		Emily Susan	3
11	6		Emily Nancy Susan Jill		Debbie Emily	3
12			7		Debbie Linda	3
13					Emily Nancy	3
14					Jill Susan	3
15					Nancy Susan	3
16					Debbie Susan	4
17					Jill Nancy	4
18					Debbie Jill	4
19					Debbie Nancy	4
20					15	42
Sheet1

Cell Formulas
Range	Formula
B11	B11	=SUBTOTAL(103,[Name])
D12	D12	=SUBTOTAL(103,[Court Assignment])
G5:G19	G5	=COUNTIFS(TblCourts[Court Assignment],"*"&TEXTBEFORE([@Pairing]," ")&"*",TblCourts[Court Assignment],"*"&TEXTAFTER([@Pairing]," ")&"*")
F20	F20	=SUBTOTAL(103,[Pairing])
G20	G20	=SUBTOTAL(109,[Tally2])

The problem arises if I add any new players. Here I added Wendy to TblPlayers and a new row to TblCourts with Wendy playing. Then I have to manually add N-1 rows to TblTallies.

Tennis Pairings.xlsx
	B	C	D	E	F	G
3	TblPlayers		TblCourts		TblTallies
4	Name		Court Assignment		Pairing	Tally2
5	Debbie		Jill Nancy Debbie Emily		Emily Wendy	0
6	Emily		Susan Jill Debbie Linda		Jill Wendy	0
7	Jill		Nancy Jill Debbie Linda		Susan Wendy	0
8	Linda		Nancy Susan Emily Debbie		Emily Linda	1
9	Nancy		Debbie Nancy Susan Jill		Debbie Wendy	1
10	Susan		Debbie Susan Emily Linda		Linda Wendy	1
11	Wendy		Emily Nancy Susan Jill		Nancy Wendy	1
12	7		Debbie Wendy Linda Nancy		Linda Nancy	2
13			8		Emily Jill	2
14					Linda Susan	2
15					Jill Linda	2
16					Emily Susan	3
17					Debbie Emily	3
18					Emily Nancy	3
19					Jill Susan	3
20					Nancy Susan	3
21					Debbie Linda	4
22					Debbie Susan	4
23					Jill Nancy	4
24					Debbie Jill	4
25					Debbie Nancy	5
26					21	48
Sheet1

Cell Formulas
Range	Formula
B12	B12	=SUBTOTAL(103,[Name])
D13	D13	=SUBTOTAL(103,[Court Assignment])
G5:G25	G5	=COUNTIFS(TblCourts[Court Assignment],"*"&TEXTBEFORE([@Pairing]," ")&"*",TblCourts[Court Assignment],"*"&TEXTAFTER([@Pairing]," ")&"*")
F26	F26	=SUBTOTAL(103,[Pairing])
G26	G26	=SUBTOTAL(109,[Tally2])

Your solution is very cool, but with 20 players and new ones added from time to time, manually adding rows to TblTallies wuold be tedious and error prone. I am looking for a solution that generates the entire TblTallies table from scratch based on the contents of TblPlayers & TblCourts.

I also want to add another table that shows who was available to play each day and use that to calculate the percentage of the days that each player was selected when they were available.

I think I will need a macro for all that. Do you agree?

 

[shaowu459]

auto populate the whole result table:

=LET(s,DROP(REDUCE(0,B5:B9,LAMBDA(x,y,VSTACK(x,DROP(IFNA(HSTACK(y,y:B10),y),1)))),1),VSTACK({"Player1","Player2","Tally"},HSTACK(s,BYROW(s,LAMBDA(x,SUM(N(BYROW(FIND(x,D5:D9),LAMBDA(y,COUNT(y)=2)))))))))

 

[JenniferMurphy]

auto populate the whole result table:

=LET(s,DROP(REDUCE(0,B5:B9,LAMBDA(x,y,VSTACK(x,DROP(IFNA(HSTACK(y,y:B10),y),1)))),1),VSTACK({"Player1","Player2","Tally"},HSTACK(s,BYROW(s,LAMBDA(x,SUM(N(BYROW(FIND(x,D5:D9),LAMBDA(y,COUNT(y)=2)))))))))

Impressive, very impressive. I am afraid you guys are just a few levels over my head. It is amazing to see what Excel is capable of if you know what you are doing. Amazing.

Thanks

 

[shaowu459]

B5:B9=drop the last row of TblPlayers range
B10=the last cell in Tblayers range

If you add rows, change the references accordingly.

 

[shaowu459]

make it shorter:

=REDUCE({"Player1","Player2","Tally"},B5:B9,LAMBDA(x,y,VSTACK(x,DROP(REDUCE(0,y:B10,LAMBDA(m,n,VSTACK(m,HSTACK(y,n,COUNT(FIND(y,D5:D9)*FIND(n,D5:D9)))))),2))))

 

[shaowu459]

Impressive, very impressive. I am afraid you guys are just a few levels over my head. It is amazing to see what Excel is capable of if you know what you are doing. Amazing.

Thanks

thanks for the feedback and enjoy the day

 

[shaowu459]

I can't edit my post #7, so post here the final formula:

=REDUCE({"Player1","Player2","Tally"},B5:B9,LAMBDA(x,y,VSTACK(x,DROP(REDUCE(,y:B10,LAMBDA(m,n,VSTACK(m,HSTACK(y,n,COUNT(FIND(y,D5:D9)*FIND(n,D5:D9)))))),1))))

 

[JenniferMurphy]

I can't edit my post #7, so post here the final formula:

=REDUCE({"Player1","Player2","Tally"},B5:B9,LAMBDA(x,y,VSTACK(x,DROP(REDUCE(,y:B10,LAMBDA(m,n,VSTACK(m,HSTACK(y,n,COUNT(FIND(y,D5:D9)*FIND(n,D5:D9)))))),1))))

I am totally blown away by the creative and mind-boggling (at least for me) solutions to my question. If I ever get my head around several of these new functions, I will have learned a lot. Thank you.

However, I feel a little guilty because I fear I may have been a little misleading or at least unclear about my objective. Generating the tallies is just part of the objective. I then want to calculate the percentages of the pair-wise tallies and the total tallies as compared their availability.

Also, I am more than a little wary of complicated expressions in cells -- especially if it has to be replicated on multiple cells. I am typo-prone and a little dyslexic, so I tend to get even simple expressions wrong. Expressions like the one above scare the *%$# out of me.

Here's an example of what the final product might look like. Bear in mind that this is with only 7 players and for just 8 dates. We have around 20 that play regularly and a few others that play occasionally. If 8 or more are available on a given date, I will assign two courts. If 12 or more are available, I will assign 3 courts. And I think I will want to be able to generate tallies for the last 6 months, the last 12 months, and so on.

So the tables will be much larger and somewhat dynamic. I used your expression for the pairing tally. I am sure your expression could be tweaked to generate the player tally and both availability counts, too, but I am almost certain to resort to a macro, with which I am more comfortable, so I did them by hand.

Tennis Pairings.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O
3	TblPlayers2					TblCourts2				TblTallies2
4	Name	Tally	Avail	%		Date	Available	Court1		Pairing	Tally	Avail	%
5	Debbie	7	8	88%		11/07/23	Jill Nancy Debbie Emily	Jill Nancy Debbie Emily		Debbie Emily	3	5	60%
6	Emily	4	5	80%		11/09/23	Susan Jill Debbie Linda Nancy	Susan Jill Debbie Linda		Debbie Jill	4	5	80%
7	Jill	5	5	100%		11/14/23	Nancy Jill Debbie Linda Emily Susan	Nancy Jill Debbie Linda		Debbie Linda	4	6	67%
8	Linda	4	6	67%		11/16/23	Nancy Susan Emily Debbie Linda	Nancy Susan Emily Debbie		Debbie Nancy	5	7	71%
9	Nancy	6	7	86%		11/21/23	Debbie Nancy Susan Jill	Debbie Nancy Susan Jill		Debbie Susan	4	7	57%
10	Susan	5	7	71%		11/23/23	Debbie Susan Emily Linda	Debbie Susan Emily Linda		Debbie Wendy	1	1	100%
11	Wendy	1	1	100%		11/28/23	Emily Nancy Susan Jill Debbie Linda	Emily Nancy Susan Jill		Emily Jill	2	3	67%
12	7	32	39			11/30/23	Debbie Wendy Linda Nancy Susan	Debbie Wendy Linda Nancy		Emily Linda	1	2	50%
13								8		Emily Nancy	3	4	75%
14										Emily Susan	3	4	75%
15										Emily Wendy	0	0	0%
16										Jill Linda	2	3	67%
17										Jill Nancy	4	5	80%
18										Jill Susan	3	4	75%
19										Jill Wendy	0	0	0%
20										Linda Nancy	2	5	40%
21										Linda Susan	2	6	33%
22										Linda Wendy	1	1	100%
23										Nancy Susan	3	6	50%
24										Nancy Wendy	1	1	100%
25										Susan Wendy	0	1	0%
26										21	48	76
Sheet2

Cell Formulas
Range	Formula
F5:F11,O5:O25	F5	=IF([@Avail]=0,0,[@Tally]/[@Avail])
C12	C12	=SUBTOTAL(103,[Name])
D12,M26	D12	=SUBTOTAL(109,[Tally])
E12,N26	E12	=SUBTOTAL(109,[Avail])
J13	J13	=SUBTOTAL(103,[Court1])
M5:M25	M5	=COUNTIFS(TblCourts2[Court1],"*"&TEXTBEFORE([@Pairing]," ")&"*",TblCourts2[Court1],"*"&TEXTAFTER([@Pairing]," ")&"*")
L26	L26	=SUBTOTAL(103,[Pairing])

I hope that is a little clearer.