OOTP Developments Forums - Posting my Schedule

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:

Code:

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.

Code:

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:

Code:

        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:

Code:

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).

Code:

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.