Fixture table help required

[bowlerden300]

Hi i am relatively new to excel so hope that i am not making a huge mess of this.

i have created a table of 20 rows and 19 columns the rows are of bowlers names and a number suffix ie: rob1 and the columns are headed with weeks 1 -19. I wish to fill the table with fixtures where each player plays each other once. so i wondered if i could use some sort of formula to add the player numbers to the table randomly so they appear only once on each row and column remembering a player cant play himself so player 1 row would not feature a number 1 etc. I have tried to do it manually but it takes so long and i always end up with a number duplicated at some point so an automatic way would be a great benefit any help will be gratefully accepted.
Thank you
Den

 

[MaxSmart]

Hi,

Have you tried going to this free site;

Tournament Brackets - Printable Single & Double Elimination

You may find that it has exactly what you need.

Regards.......Max

 

[bowlerden300]

Thanks Max i've been and looked but its not what i need i guess i will have to keep struggling to work it out for now.

 

[bowlerden300]

	Match Play League
	Week 1	Week 2	Week3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week 11	Week 12	Week 13	Week 14	Week 15	Week 16	Week 17	Week 18	Week 19
Player 1	10	4	16	12	17	9	8	19	20	15	3	11	5	18	7	14	6	13	2
Player 2	16	12	17	9	8	19	20	15	3	11	5	18	7	14	6	13	4	10	1
Player 3	4	11	5	18	7	14	6	13	2	10	1	16	12	17	9	8	19	20	15
Player 4	3	1	11	16	5	12	18	17	7	9	14	8	6	19	13	20	2	15	10
Player 5	20	15	3	11	4	18	7	14	6	13	2	10	1	16	12	17	9	8	19
Player 6	17	9	8	19	20	15	3	11	5	18	7	14	4	13	2	10	1	16	12
Player 7	8	19	20	15	3	11	5	18	4	14	6	13	2	10	1	16	12	17	9
Player 8	7	14	6	13	2	10	1	16	12	17	9	4	19	20	15	3	11	5	18
Player 9	14	6	13	2	10	1	16	12	17	4	8	19	20	15	3	11	5	18	7
Player 10	1	16	12	17	9	8	19	20	15	3	11	5	18	7	14	6	13	2	4
Player 11	15	3	4	5	18	7	14	6	13	2	10	1	16	12	17	9	8	19	20
Player 12	13	2	10	1	16	4	17	9	8	19	20	15	3	11	5	18	7	14	6
Player 13	12	17	9	8	19	20	15	3	11	5	18	7	14	6	4	2	10	1	16
Player 14	9	8	19	20	15	3	11	5	18	7	4	6	13	2	10	1	16	12	17
Player 15	11	5	18	7	14	6	13	2	10	1	16	12	17	9	8	19	20	4	3
Player 16	2	10	1	4	12	17	9	8	19	20	15	3	11	5	18	7	14	6	13
Player 17	6	13	2	10	1	16	12	4	9	8	19	20	15	3	11	5	18	7	14
Player 18	19	20	15	3	11	5	4	7	14	6	13	2	10	1	16	12	17	9	8
Player 19	18	7	14	6	13	2	10	1	16	12	17	9	8	4	20	15	3	11	5
Player 20	5	18	7	14	6	13	2	10	1	16	12	17	9	8	19	4	15	3	11

<colgroup><col><col span="19"></colgroup><tbody>
</tbody>

 

[bowlerden300]

Above is the table i did manually it took hours of entering all the detail and amending the errors so i wish to be able to replicate this table using some form of automation to save time and eliminate the errors.
Thanks
Den

 

[bowlerden300]

I still need help i am using version:14.0.7166.5000 (32-bit) of excel

 

[wideboydixon]

See here:

https://en.wikipedia.org/wiki/Round-robin_tournament#Scheduling_algorithm

A simple implementation of this:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Week 1	P 1	P 2	P 3	P 4	P 5	P 6	P 7	P 8	P 9	P 10
2		P 20	P 19	P 18	P 17	P 16	P 15	P 14	P 13	P 12	P 11
3	Week 2	P 1	P 20	P 2	P 3	P 4	P 5	P 6	P 7	P 8	P 9
4		P 19	P 18	P 17	P 16	P 15	P 14	P 13	P 12	P 11	P 10
5	Week 3	P 1	P 19	P 20	P 2	P 3	P 4	P 5	P 6	P 7	P 8
6		P 18	P 17	P 16	P 15	P 14	P 13	P 12	P 11	P 10	P 9
7	Week 4	P 1	P 18	P 19	P 20	P 2	P 3	P 4	P 5	P 6	P 7
8		P 17	P 16	P 15	P 14	P 13	P 12	P 11	P 10	P 9	P 8
9	Week 5	P 1	P 17	P 18	P 19	P 20	P 2	P 3	P 4	P 5	P 6
10		P 16	P 15	P 14	P 13	P 12	P 11	P 10	P 9	P 8	P 7
11	Week 6	P 1	P 16	P 17	P 18	P 19	P 20	P 2	P 3	P 4	P 5
12		P 15	P 14	P 13	P 12	P 11	P 10	P 9	P 8	P 7	P 6
13	Week 7	P 1	P 15	P 16	P 17	P 18	P 19	P 20	P 2	P 3	P 4
14		P 14	P 13	P 12	P 11	P 10	P 9	P 8	P 7	P 6	P 5
15	Week 8	P 1	P 14	P 15	P 16	P 17	P 18	P 19	P 20	P 2	P 3
16		P 13	P 12	P 11	P 10	P 9	P 8	P 7	P 6	P 5	P 4
17	Week 9	P 1	P 13	P 14	P 15	P 16	P 17	P 18	P 19	P 20	P 2
18		P 12	P 11	P 10	P 9	P 8	P 7	P 6	P 5	P 4	P 3
19	Week 10	P 1	P 12	P 13	P 14	P 15	P 16	P 17	P 18	P 19	P 20
20		P 11	P 10	P 9	P 8	P 7	P 6	P 5	P 4	P 3	P 2
21	Week 11	P 1	P 11	P 12	P 13	P 14	P 15	P 16	P 17	P 18	P 19
22		P 10	P 9	P 8	P 7	P 6	P 5	P 4	P 3	P 2	P 20
23	Week 12	P 1	P 10	P 11	P 12	P 13	P 14	P 15	P 16	P 17	P 18
24		P 9	P 8	P 7	P 6	P 5	P 4	P 3	P 2	P 20	P 19
25	Week 13	P 1	P 9	P 10	P 11	P 12	P 13	P 14	P 15	P 16	P 17
26		P 8	P 7	P 6	P 5	P 4	P 3	P 2	P 20	P 19	P 18
27	Week 14	P 1	P 8	P 9	P 10	P 11	P 12	P 13	P 14	P 15	P 16
28		P 7	P 6	P 5	P 4	P 3	P 2	P 20	P 19	P 18	P 17
29	Week 15	P 1	P 7	P 8	P 9	P 10	P 11	P 12	P 13	P 14	P 15
30		P 6	P 5	P 4	P 3	P 2	P 20	P 19	P 18	P 17	P 16
31	Week 16	P 1	P 6	P 7	P 8	P 9	P 10	P 11	P 12	P 13	P 14
32		P 5	P 4	P 3	P 2	P 20	P 19	P 18	P 17	P 16	P 15
33	Week 17	P 1	P 5	P 6	P 7	P 8	P 9	P 10	P 11	P 12	P 13
34		P 4	P 3	P 2	P 20	P 19	P 18	P 17	P 16	P 15	P 14
35	Week 18	P 1	P 4	P 5	P 6	P 7	P 8	P 9	P 10	P 11	P 12
36		P 3	P 2	P 20	P 19	P 18	P 17	P 16	P 15	P 14	P 13
37	Week 19	P 1	P 3	P 4	P 5	P 6	P 7	P 8	P 9	P 10	P 11
38		P 2	P 20	P 19	P 18	P 17	P 16	P 15	P 14	P 13	P 12
Sheet1

Cell Formulas
Range	Formula
A1	=IF(MOD(ROW(),2)=1,"Week " & (ROW()+1)/2,"")
B3	=$B$1
B4	=C2
C3	=B2
C4	=D2
D3	=C1
D4	=E2
E3	=D1
E4	=F2
F3	=E1
F4	=G2
G3	=F1
G4	=H2
H3	=G1
H4	=I2
I3	=H1
I4	=J2
J3	=I1
J4	=K2
K3	=J1
K4	=K1

Copy A1 down. Copy B3:K4 down as well. B1:K2 would contain your player names.

WBD

 

[bowlerden300]

See here:

https://en.wikipedia.org/wiki/Round-robin_tournament#Scheduling_algorithm

A simple implementation of this:

	A	B	C	D	E	F	G	H	I	J	K
1	Week 1	P 1	P 2	P 3	P 4	P 5	P 6	P 7	P 8	P 9	P 10
2		P 20	P 19	P 18	P 17	P 16	P 15	P 14	P 13	P 12	P 11
3	Week 2	P 1	P 20	P 2	P 3	P 4	P 5	P 6	P 7	P 8	P 9
4		P 19	P 18	P 17	P 16	P 15	P 14	P 13	P 12	P 11	P 10
5	Week 3	P 1	P 19	P 20	P 2	P 3	P 4	P 5	P 6	P 7	P 8
6		P 18	P 17	P 16	P 15	P 14	P 13	P 12	P 11	P 10	P 9
7	Week 4	P 1	P 18	P 19	P 20	P 2	P 3	P 4	P 5	P 6	P 7
8		P 17	P 16	P 15	P 14	P 13	P 12	P 11	P 10	P 9	P 8
9	Week 5	P 1	P 17	P 18	P 19	P 20	P 2	P 3	P 4	P 5	P 6
10		P 16	P 15	P 14	P 13	P 12	P 11	P 10	P 9	P 8	P 7
11	Week 6	P 1	P 16	P 17	P 18	P 19	P 20	P 2	P 3	P 4	P 5
12		P 15	P 14	P 13	P 12	P 11	P 10	P 9	P 8	P 7	P 6
13	Week 7	P 1	P 15	P 16	P 17	P 18	P 19	P 20	P 2	P 3	P 4
14		P 14	P 13	P 12	P 11	P 10	P 9	P 8	P 7	P 6	P 5
15	Week 8	P 1	P 14	P 15	P 16	P 17	P 18	P 19	P 20	P 2	P 3
16		P 13	P 12	P 11	P 10	P 9	P 8	P 7	P 6	P 5	P 4
17	Week 9	P 1	P 13	P 14	P 15	P 16	P 17	P 18	P 19	P 20	P 2
18		P 12	P 11	P 10	P 9	P 8	P 7	P 6	P 5	P 4	P 3
19	Week 10	P 1	P 12	P 13	P 14	P 15	P 16	P 17	P 18	P 19	P 20
20		P 11	P 10	P 9	P 8	P 7	P 6	P 5	P 4	P 3	P 2
21	Week 11	P 1	P 11	P 12	P 13	P 14	P 15	P 16	P 17	P 18	P 19
22		P 10	P 9	P 8	P 7	P 6	P 5	P 4	P 3	P 2	P 20
23	Week 12	P 1	P 10	P 11	P 12	P 13	P 14	P 15	P 16	P 17	P 18
24		P 9	P 8	P 7	P 6	P 5	P 4	P 3	P 2	P 20	P 19
25	Week 13	P 1	P 9	P 10	P 11	P 12	P 13	P 14	P 15	P 16	P 17
26		P 8	P 7	P 6	P 5	P 4	P 3	P 2	P 20	P 19	P 18
27	Week 14	P 1	P 8	P 9	P 10	P 11	P 12	P 13	P 14	P 15	P 16
28		P 7	P 6	P 5	P 4	P 3	P 2	P 20	P 19	P 18	P 17
29	Week 15	P 1	P 7	P 8	P 9	P 10	P 11	P 12	P 13	P 14	P 15
30		P 6	P 5	P 4	P 3	P 2	P 20	P 19	P 18	P 17	P 16
31	Week 16	P 1	P 6	P 7	P 8	P 9	P 10	P 11	P 12	P 13	P 14
32		P 5	P 4	P 3	P 2	P 20	P 19	P 18	P 17	P 16	P 15
33	Week 17	P 1	P 5	P 6	P 7	P 8	P 9	P 10	P 11	P 12	P 13
34		P 4	P 3	P 2	P 20	P 19	P 18	P 17	P 16	P 15	P 14
35	Week 18	P 1	P 4	P 5	P 6	P 7	P 8	P 9	P 10	P 11	P 12
36		P 3	P 2	P 20	P 19	P 18	P 17	P 16	P 15	P 14	P 13
37	Week 19	P 1	P 3	P 4	P 5	P 6	P 7	P 8	P 9	P 10	P 11
38		P 2	P 20	P 19	P 18	P 17	P 16	P 15	P 14	P 13	P 12

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula A1 =IF(MOD(ROW(),2)=1,"Week " & (ROW()+1)/2,"") B3 =$B$1 C3 =B2 D3 =C1 E3 =D1 F3 =E1 G3 =F1 H3 =G1 I3 =H1 J3 =I1 K3 =J1 B4 =C2 C4 =D2 D4 =E2 E4 =F2 F4 =G2 G4 =H2 H4 =I2 I4 =J2 J4 =K2 K4 =K1 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Copy A1 down. Copy B3:K4 down as well. B1:K2 would contain your player names.

WBD

This looks great just what i am looking for will it be easy to modify for more players as the league is increasing rapidly.
Thank you

 

[wideboydixon]

This looks great just what i am looking for will it be easy to modify for more players as the league is increasing rapidly.
Thank you

Yes if you just follow the pattern of rotating all players except #1. If there's an odd number of players you'll need to add one called "Bye".

WBD

 

[bowlerden300]

Thank you Wideboy i have used your formulas to create a 22 man matchplay league over 21 weeks and using part of your formula to create lane assignments for each match, i can see this being extremely useful.
Den