# Fixture table help required

#### bowlerden300

##### New Member
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

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### bowlerden300

##### New Member
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

##### New Member
 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

##### New Member

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

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

#### wideboydixon

##### Well-known Member

See here:

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

A simple implementation of this:

Book1
ABCDEFGHIJK
1Week 1P 1P 2P 3P 4P 5P 6P 7P 8P 9P 10
2P 20P 19P 18P 17P 16P 15P 14P 13P 12P 11
3Week 2P 1P 20P 2P 3P 4P 5P 6P 7P 8P 9
4P 19P 18P 17P 16P 15P 14P 13P 12P 11P 10
5Week 3P 1P 19P 20P 2P 3P 4P 5P 6P 7P 8
6P 18P 17P 16P 15P 14P 13P 12P 11P 10P 9
7Week 4P 1P 18P 19P 20P 2P 3P 4P 5P 6P 7
8P 17P 16P 15P 14P 13P 12P 11P 10P 9P 8
9Week 5P 1P 17P 18P 19P 20P 2P 3P 4P 5P 6
10P 16P 15P 14P 13P 12P 11P 10P 9P 8P 7
11Week 6P 1P 16P 17P 18P 19P 20P 2P 3P 4P 5
12P 15P 14P 13P 12P 11P 10P 9P 8P 7P 6
13Week 7P 1P 15P 16P 17P 18P 19P 20P 2P 3P 4
14P 14P 13P 12P 11P 10P 9P 8P 7P 6P 5
15Week 8P 1P 14P 15P 16P 17P 18P 19P 20P 2P 3
16P 13P 12P 11P 10P 9P 8P 7P 6P 5P 4
17Week 9P 1P 13P 14P 15P 16P 17P 18P 19P 20P 2
18P 12P 11P 10P 9P 8P 7P 6P 5P 4P 3
19Week 10P 1P 12P 13P 14P 15P 16P 17P 18P 19P 20
20P 11P 10P 9P 8P 7P 6P 5P 4P 3P 2
21Week 11P 1P 11P 12P 13P 14P 15P 16P 17P 18P 19
22P 10P 9P 8P 7P 6P 5P 4P 3P 2P 20
23Week 12P 1P 10P 11P 12P 13P 14P 15P 16P 17P 18
24P 9P 8P 7P 6P 5P 4P 3P 2P 20P 19
25Week 13P 1P 9P 10P 11P 12P 13P 14P 15P 16P 17
26P 8P 7P 6P 5P 4P 3P 2P 20P 19P 18
27Week 14P 1P 8P 9P 10P 11P 12P 13P 14P 15P 16
28P 7P 6P 5P 4P 3P 2P 20P 19P 18P 17
29Week 15P 1P 7P 8P 9P 10P 11P 12P 13P 14P 15
30P 6P 5P 4P 3P 2P 20P 19P 18P 17P 16
31Week 16P 1P 6P 7P 8P 9P 10P 11P 12P 13P 14
32P 5P 4P 3P 2P 20P 19P 18P 17P 16P 15
33Week 17P 1P 5P 6P 7P 8P 9P 10P 11P 12P 13
34P 4P 3P 2P 20P 19P 18P 17P 16P 15P 14
35Week 18P 1P 4P 5P 6P 7P 8P 9P 10P 11P 12
36P 3P 2P 20P 19P 18P 17P 16P 15P 14P 13
37Week 19P 1P 3P 4P 5P 6P 7P 8P 9P 10P 11
38P 2P 20P 19P 18P 17P 16P 15P 14P 13P 12
Sheet1
Cell Formulas
RangeFormula
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

##### New Member
See here:

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

A simple implementation of this:

ABCDEFGHIJK
1Week 1P 1P 2P 3P 4P 5P 6P 7P 8P 9P 10
2P 20P 19P 18P 17P 16P 15P 14P 13P 12P 11
3Week 2P 1P 20P 2P 3P 4P 5P 6P 7P 8P 9
4P 19P 18P 17P 16P 15P 14P 13P 12P 11P 10
5Week 3P 1P 19P 20P 2P 3P 4P 5P 6P 7P 8
6P 18P 17P 16P 15P 14P 13P 12P 11P 10P 9
7Week 4P 1P 18P 19P 20P 2P 3P 4P 5P 6P 7
8P 17P 16P 15P 14P 13P 12P 11P 10P 9P 8
9Week 5P 1P 17P 18P 19P 20P 2P 3P 4P 5P 6
10P 16P 15P 14P 13P 12P 11P 10P 9P 8P 7
11Week 6P 1P 16P 17P 18P 19P 20P 2P 3P 4P 5
12P 15P 14P 13P 12P 11P 10P 9P 8P 7P 6
13Week 7P 1P 15P 16P 17P 18P 19P 20P 2P 3P 4
14P 14P 13P 12P 11P 10P 9P 8P 7P 6P 5
15Week 8P 1P 14P 15P 16P 17P 18P 19P 20P 2P 3
16P 13P 12P 11P 10P 9P 8P 7P 6P 5P 4
17Week 9P 1P 13P 14P 15P 16P 17P 18P 19P 20P 2
18P 12P 11P 10P 9P 8P 7P 6P 5P 4P 3
19Week 10P 1P 12P 13P 14P 15P 16P 17P 18P 19P 20
20P 11P 10P 9P 8P 7P 6P 5P 4P 3P 2
21Week 11P 1P 11P 12P 13P 14P 15P 16P 17P 18P 19
22P 10P 9P 8P 7P 6P 5P 4P 3P 2P 20
23Week 12P 1P 10P 11P 12P 13P 14P 15P 16P 17P 18
24P 9P 8P 7P 6P 5P 4P 3P 2P 20P 19
25Week 13P 1P 9P 10P 11P 12P 13P 14P 15P 16P 17
26P 8P 7P 6P 5P 4P 3P 2P 20P 19P 18
27Week 14P 1P 8P 9P 10P 11P 12P 13P 14P 15P 16
28P 7P 6P 5P 4P 3P 2P 20P 19P 18P 17
29Week 15P 1P 7P 8P 9P 10P 11P 12P 13P 14P 15
30P 6P 5P 4P 3P 2P 20P 19P 18P 17P 16
31Week 16P 1P 6P 7P 8P 9P 10P 11P 12P 13P 14
32P 5P 4P 3P 2P 20P 19P 18P 17P 16P 15
33Week 17P 1P 5P 6P 7P 8P 9P 10P 11P 12P 13
34P 4P 3P 2P 20P 19P 18P 17P 16P 15P 14
35Week 18P 1P 4P 5P 6P 7P 8P 9P 10P 11P 12
36P 3P 2P 20P 19P 18P 17P 16P 15P 14P 13
37Week 19P 1P 3P 4P 5P 6P 7P 8P 9P 10P 11
38P 2P 20P 19P 18P 17P 16P 15P 14P 13P 12

</tbody>
Sheet1

Worksheet Formulas
CellFormula
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

</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

##### Well-known Member
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

##### New Member
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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,010
Messages
5,834,880
Members
430,325
Latest member
Thony

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back