Posting my Schedule

[SSG Troyer]

#
# 36 team schedule, 162 games
# Designed for the following configuration:
# 2 subleagues each with 3 divisions, each with 6 teams
#
#
# 12 games against 5 other teams in division (6H/6A)
# 8 games against 12 other teams in league (4H/4A)
# 6 interleague games (3H against one team in equivelent division, 3A against another)
#
#
# Intradivision and interleague series' are 3 game, interdivision series' are 2 or 4 games
# Offdays are staggered, no team has more than 15 days without a day off (I think...).
# Every team has the same day off in late August for league holiday (Little League Day).
#
# No homestands or roadtrips longer than 15 games
#
# All games have random start times of (20%) 1315, (20%) 1515, or (60%) 1905
# (except 1st game of the season (all at 1315) and last game of the season (all at 1905))
# OOTP will adjust for time zone variance
#
# Season begins first Monday on or after April 1st
# Season thus ends late September/early October
#
# All-Star Game set for early July
#
# Posted 2012/04/19
#

[GiantYankee]

Any tips for making a schedule that doesn't take weeks? Still struggling to create them efficiently.

[SSG Troyer]

I worked in Excel 2003...gmo's blog is a good read; he helped me with the ML-style. We share a common root concept; that the series is the base/root "unit" with which to work.

I started by making a big triangle of all possible combinations of team numbers. A small one would be:

01-02  02-03  03-04  04-05  05-06
01-03  02-04  03-05  04-06
01-04  02-05  03-06
01-05  02-06
01-06

Each set of numbers is a series. I then cut-and-pasted (the cut here is important to avoid accidental duplication) them into columns where no two numbers appeared twice. A pattern develops as it progresses, and it becomes easy.

01-02	01-03	01-04	01-05	01-06
03-04	02-05	02-06	02-04	02-03
05-06	04-06	03-05	03-06	04-05

I then copied-and-pasted these columns next to each other and switched the order of each pair in the second set. Them I started making the days...I cut-and-pasted the first column into a new set of columns, and then copied-and-pasted that column next the one I'd just pasted as many times as the series was long. Almost halfway through, my spreadsheet looks like this:

	01-03	01-04		01-06	02-01			05-01	06-01			
	02-05	02-06		02-03	04-03			04-02	03-02			
	04-06	03-05		04-05	06-05			06-03	05-04			

01-02	01-02	01-02	04-01	04-01	04-01	04-01	01-05	01-05	01-05	03-01	03-01	03-01
03-04	03-04	03-04	06-02	06-02	06-02	06-02	02-04	02-04	02-04	05-02	05-02	05-02
05-06	05-06	05-06	05-03	05-03	05-03	05-03	03-06	03-06	03-06	06-04	06-04	06-04

Once the top part is cleared, I cut-and-copied the bottom half up top. Days off were inserted as blank columns. Cells were moved into blank cells next to equivelently-numbered cells as needed to stagger the days off. A macro then "transposed" the columns into schedule-file-format order. Mind, this macro uses numbers for it's loops that are specific to the one I posted, not my example, and would be different for any schedule it needed to process:

Public Sub Transcribe()
    Dim rw As Integer, cl As Integer, home As Integer, away As Integer, cntr As Integer
    cntr = 21
    For cl = 1 To 180
        For rw = 2 To 19
            If Worksheets("Schedule").Cells(rw, cl).Value <> "" Then
                cntr = cntr + 1
                home = Val(Left(Worksheets("Schedule").Cells(rw, cl).Value, 2))
                away = Val(Right(Worksheets("Schedule").Cells(rw, cl).Value, 2))
                Worksheets("Schedule").Cells(cntr, 1).Value = Str(cl)
                Worksheets("Schedule").Cells(cntr, 2).Value = Str(Settime(home))
                Worksheets("Schedule").Cells(cntr, 3).Value = Str(away)
                Worksheets("Schedule").Cells(cntr, 4).Value = Str(home)
            End If
        Next rw
    Next cl
End Sub

Settime() is the function I wrote to generate the random game times. A second macro then took the output of the first and wrote the file (without the header stuff, which was copied from a working file and edited to my needs).

Public Sub WriteTheFile()
Dim x As Integer, lineout As String
Open "C:\Users\Mike\Documents\Out of the Park Developments\Schedule.lsdl" For Output As 1
For x = 22 To 2937
    lineout = "<GAME day=" & Chr(34) & Worksheets("Schedule").Cells(x, 1).Value & Chr(34) & " time=" & Chr(34) & Worksheets("Schedule").Cells(x, 2).Value & Chr(34) & " away=" & Chr(34) & Worksheets("Schedule").Cells(x, 3).Value & Chr(34) & " home=" & Chr(34) & Worksheets("Schedule").Cells(x, 4).Value & Chr(34) & " />"
    Print #1, lineout
Next x
Close #1
End Sub

Once again, the loop values are specific to the posted schedule.

All told, this schedule took about twelve/fourteen hours to put together. I've atteched the finished .xls which generated the schedule I posted.

[GiantYankee]

Thanks for the pointers. I'm gonna try it again with your info. Much appreciated.