League Scheduler

[Hoosier605]

I know this has been asked many times, but I couldn't find what I was looking for. Most of the league scheduler questions are something like 21 teams for 20 weeks and looking for a random scheduler where all teams will play each other once and only once. I however do not want total randomness as I have 28 teams, only 20 weeks, and I want certain teams to play each other. I just need a semi-random schedule to be generated from a pairings table and teams that do play each other only play each other once. I'm sure this is simple, but unfortunately when it comes to excel, I'm fairly simple...thanks in advance.

See below: Team 1 would play teams 3,4,5,7,9,11,12, etc Team 2 3,6,7,8,10, etc 14 matches each week for 20 weeks

The first two weeks "could" look like this:

	Tee1	Tee2	Tee3	Tee4	Tee5	Tee6	Tee7	Tee8	Tee9	Tee10	Tee11	Tee12	Tee13	Tee14
Week 1	1-3	2-4	5-6	7-9	8-10	11-13	12-14	15-17	16-18	19-21	20-22	23-25	24-26	27-28
Week 2	25-26	4-28	1-27	2-3	5-7	6-8	9-10	11-14	12-15	13-16	17-19	18-20	21-23	22-24

Preferred Pairings 2024
TEAM	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28
1			1	1	1		1		1		1	1	1	1	1	1	1	1	1	1	1	1	1		1		1
2			1	1		1	1	1		1	1	1		1	1	1		1	1	1		1	1	1	1	1		1
3	1	1		1	1	1	1		1		1	1	1		1	1	1		1		1		1	1	1		1	1
4	1	1	1		1	1		1	1	1	1	1		1		1		1	1	1		1		1	1	1		1
5	1		1	1		1	1	1	1		1		1	1	1		1	1	1		1		1	1	1		1	1
6		1	1	1	1			1		1		1	1	1		1	1	1		1		1	1	1	1	1	1	1
7	1	1	1		1				1		1	1	1	1	1		1		1	1	1	1	1	1	1	1	1
8		1		1	1	1			1	1		1	1	1	1	1	1	1		1	1	1		1		1	1	1
9	1		1	1	1		1	1		1	1		1	1	1	1	1	1	1		1		1		1	1	1
10		1		1		1		1	1			1	1	1		1	1	1		1	1	1	1	1	1	1	1	1
11	1	1	1	1	1		1		1				1	1	1	1	1		1	1	1	1	1		1		1	1
12	1	1	1	1		1	1	1		1				1	1	1		1	1	1	1	1	1	1		1		1
13	1		1		1	1	1	1	1	1	1				1	1	1	1	1	1	1	1	1		1		1
14	1	1		1	1	1	1	1	1	1	1	1			1	1		1	1	1		1		1		1		1
15	1	1	1		1		1	1	1		1	1	1	1			1	1	1		1	1	1		1		1	1
16	1	1	1	1		1		1	1	1	1	1	1	1				1	1	1	1	1		1		1		1
17	1		1		1	1	1	1	1	1	1		1		1				1	1	1	1	1	1	1	1	1
18	1	1		1	1	1		1	1	1		1	1	1	1	1			1	1		1		1		1	1	1
19	1	1	1	1	1		1		1		1	1	1	1	1	1	1	1			1		1		1		1	1
20	1	1		1		1	1	1		1	1	1	1	1		1	1	1			1	1	1	1		1		1
21	1		1		1		1	1	1	1	1	1	1		1	1	1		1	1			1	1	1	1	1
22	1	1		1		1	1	1		1	1	1	1	1	1	1	1	1		1				1	1	1		1
23	1	1	1		1	1	1		1	1	1	1	1		1		1		1	1	1				1	1	1	1
24		1	1	1	1	1	1	1		1		1		1		1	1	1		1	1	1			1	1	1	1
25	1	1	1	1	1	1	1		1	1	1		1		1		1		1		1	1	1	1		1	1
26		1		1		1	1	1	1	1		1		1		1	1	1		1	1	1	1	1	1		1	1
27	1		1		1	1	1	1	1	1	1		1		1		1	1	1		1		1	1	1	1		1
28		1	1	1	1	1		1		1	1	1		1	1	1		1	1	1		1	1	1		1	1

 

[Bedlam]

I would look up Free Swiss Tournament software as that sounds similar to what you want

 

[Graymalkin]

This is not a simple problem as it requires an algorithmic solution, possibly it could be programmed using VBA macros, but personally I would not try to solve it in Excel. I believe the following schedule will work for you:

18 27 22 26 5 23 2 24 3 17 6 10 1 21 8 13 4 9 14 28 7 15 19 25 11 20 12 16
13 16 20 21 17 22 12 24 10 26 3 7 5 25 2 15 8 28 1 27 11 19 9 18 4 14 6 23
20 28 11 14 16 26 10 17 23 25 3 12 7 27 15 22 2 4 5 8 1 9 13 18 6 24 19 21
1 14 8 22 3 15 24 28 5 21 11 17 18 26 25 27 4 19 20 23 7 12 9 16 6 13 2 10
10 28 19 27 1 23 3 21 2 8 4 26 24 25 9 11 12 14 5 6 16 20 7 17 13 15 18 22
13 20 1 11 12 15 8 9 6 16 2 25 17 21 5 27 24 26 7 19 3 28 10 23 4 22 14 18
10 24 9 23 1 16 6 25 3 4 11 27 5 14 15 18 19 28 21 26 13 17 2 7 20 22 8 12
18 28 8 15 10 14 11 16 5 9 12 19 7 20 17 27 2 22 4 25 1 13 3 24 21 23 6 26
12 23 22 25 20 26 5 17 11 21 1 7 15 28 4 10 18 24 13 27 9 14 3 16 6 8 2 19
10 20 3 11 17 19 16 24 5 13 23 27 6 28 1 22 4 18 8 14 15 25 2 12 9 21 7 26
10 16 2 14 11 15 22 24 3 25 7 13 9 26 4 12 8 18 21 27 6 20 17 23 1 19 5 28
7 25 3 27 9 13 2 28 1 4 8 21 6 22 5 11 16 18 15 19 14 20 23 26 10 12 17 24
8 16 2 18 4 11 6 14 12 21 10 27 9 15 25 26 23 28 17 20 13 19 5 24 1 3 7 22
22 28 13 21 14 26 10 18 9 27 11 23 2 16 1 15 8 20 3 19 5 7 6 12 17 25 4 24
20 24 6 27 7 11 14 19 26 28 3 9 15 17 12 22 1 25 10 13 16 21 4 8 5 18 2 23
1 20 13 22 15 23 11 25 6 18 14 24 2 3 5 19 8 17 10 21 7 9 26 27 4 16 12 28
14 16 15 21 9 19 2 11 10 22 12 26 1 17 4 6 13 25 3 5 18 20 8 24 7 23 27 28
1 12 24 27 7 21 16 28 3 23 4 20 8 26 2 6 9 17 14 22 10 25 5 15 18 19 11 13
19 23 21 25 3 13 1 18 7 24 12 20 2 26 16 22 14 15 4 5 6 17 8 27 11 28 9 10
8 10 3 6 13 23 21 24 4 28 11 22 7 14 16 19 17 26 15 27 1 5 9 25 12 18 2 20

where the 20 rows are rounds. (18 27) is tee 1 week 1, (22 26) is tee 2 week 1, etc..

 

[Hoosier605]

This is not a simple problem as it requires an algorithmic solution, possibly it could be programmed using VBA macros, but personally I would not try to solve it in Excel. I believe the following schedule will work for you:

18 27 22 26 5 23 2 24 3 17 6 10 1 21 8 13 4 9 14 28 7 15 19 25 11 20 12 16
13 16 20 21 17 22 12 24 10 26 3 7 5 25 2 15 8 28 1 27 11 19 9 18 4 14 6 23
20 28 11 14 16 26 10 17 23 25 3 12 7 27 15 22 2 4 5 8 1 9 13 18 6 24 19 21
1 14 8 22 3 15 24 28 5 21 11 17 18 26 25 27 4 19 20 23 7 12 9 16 6 13 2 10
10 28 19 27 1 23 3 21 2 8 4 26 24 25 9 11 12 14 5 6 16 20 7 17 13 15 18 22
13 20 1 11 12 15 8 9 6 16 2 25 17 21 5 27 24 26 7 19 3 28 10 23 4 22 14 18
10 24 9 23 1 16 6 25 3 4 11 27 5 14 15 18 19 28 21 26 13 17 2 7 20 22 8 12
18 28 8 15 10 14 11 16 5 9 12 19 7 20 17 27 2 22 4 25 1 13 3 24 21 23 6 26
12 23 22 25 20 26 5 17 11 21 1 7 15 28 4 10 18 24 13 27 9 14 3 16 6 8 2 19
10 20 3 11 17 19 16 24 5 13 23 27 6 28 1 22 4 18 8 14 15 25 2 12 9 21 7 26
10 16 2 14 11 15 22 24 3 25 7 13 9 26 4 12 8 18 21 27 6 20 17 23 1 19 5 28
7 25 3 27 9 13 2 28 1 4 8 21 6 22 5 11 16 18 15 19 14 20 23 26 10 12 17 24
8 16 2 18 4 11 6 14 12 21 10 27 9 15 25 26 23 28 17 20 13 19 5 24 1 3 7 22
22 28 13 21 14 26 10 18 9 27 11 23 2 16 1 15 8 20 3 19 5 7 6 12 17 25 4 24
20 24 6 27 7 11 14 19 26 28 3 9 15 17 12 22 1 25 10 13 16 21 4 8 5 18 2 23
1 20 13 22 15 23 11 25 6 18 14 24 2 3 5 19 8 17 10 21 7 9 26 27 4 16 12 28
14 16 15 21 9 19 2 11 10 22 12 26 1 17 4 6 13 25 3 5 18 20 8 24 7 23 27 28
1 12 24 27 7 21 16 28 3 23 4 20 8 26 2 6 9 17 14 22 10 25 5 15 18 19 11 13
19 23 21 25 3 13 1 18 7 24 12 20 2 26 16 22 14 15 4 5 6 17 8 27 11 28 9 10
8 10 3 6 13 23 21 24 4 28 11 22 7 14 16 19 17 26 15 27 1 5 9 25 12 18 2 20

where the 20 rows are rounds. (18 27) is tee 1 week 1, (22 26) is tee 2 week 1, etc..

Thank You!!! Can you point me in the right direction of how you accomplished this? I will have to do this annually and may even need to recreate this schedule with only 26 teams instead of 28...

 

[Fluff]

@Hoosier605 In future please do not mark a post as the solution when it does not contain one. Thanks
I have unmarked post#3 as it does not have a solution.

 

[Graymalkin]

@Fluff I don't understand, the OP specified a problem very precisely, I offered a perfect solution and they accepted. I am not feeling very welcome here.

 

[Fluff]

What solution, you just posted a series of numbers with no explanation of where they came from, or how to do it.

 

[Graymalkin]

It is most definitely 'a solution' to the problem as stated and @Hoosier605 recognized it as such. An explanation is something entirely different. As I suggested above I have created an algorithm outside of Excel to arrange the 280 pairs in a 20x14 grid within the constraints specified. I have used SAS/IML which I am sure very few of your members will know about or have access to, and so it never occurred to me to post the code.

I would have been perfectly happy to discuss more details of my algorithm here, however, I find the heavy handed moderation is just too much, so this will be my last post to this message board.

@Fluff I would ask you to share my email address with OP if they wish to contact me offline.

 

[Fluff]

I find the heavy handed moderation is just too much

All I did was ask the OP not to mark a post as the solution. In what way is that "heavy handed"?

It is most definitely 'a solution' to the problem as stated and @Hoosier605 recognized it as such

Maybe you missed what the OP said here

Can you point me in the right direction of how you accomplished this? I will have to do this annually and may even need to recreate this schedule with only 26 teams instead of 28...

Therefore it is not a solution.

I would ask you to share my email address with OP if they wish to contact me offline.

Absolutely not as that is against the rules.