Making a 162-game Schedule for 22 teams using Excel

[Kirby Paquette]

I am documenting how I did a 162 game schedule for 22 teams that you can find herehttps://forums.ootpdevelopments.com/...d.php?t=330760 using Excel. I am posting my tools that I will discuss below. The ZIP pack includes 4 sheets that I will explain and a useful tool that I called the "Swiss Knife".

[Kirby Paquette]

01 Planning: Games and Set Up

The first thing to do is planning. MLB-type schedules should be 52 series spread over 26 weeks (26 ½ weeks if considering the Allstar break). To help me visualized that, I lay up a matrix with all the series and games to be played. I plan the way I want my schedule to be. Here’s the lay up for a schedule of 162 games for 22 teams in two Sub-League (SL) of 11 teams, with Inter-League (IL). I have planned 52 series (6 X 4-game series, 46 X 3-game series) for each team.



The planning here was quite difficult because of the complexity of the request. The request seems to be easy on paper but when it is time to plan, it is another story.

The matrix works this way: The number on top is the team of interest. The numbers on the left side, are the opposing teams. For the first situation, Team 1 will visit team 2 for one 4-game series. Team 1 will play at home against Team 2 for two 3-game series. Team 1 will visit Team 3 for two 3-game series and play at home against Team 3 for one 4-game series…



I have translated all the matrix in series that I will use to build the schedule. Here are all the game series for the schedule.



I use colors a lot. Here, I use Green for Intra-division series, Blue for IL. I use a darker shade for 4-game series. (I would use different colors if I have Intra-Division, Extra-Division or Inter-League matchups. I sometime use a fourth color to monitor East and West matchups).



In black and underlined, the number of games of the series (that will be useful in the layout of the schedule later). In red, the first number is the “Away” team, the second number is the “Home” team. Be careful translating the Matrix. I sometime make mistakes here, badly translating “Away” and “Home” series. (The Schedule I am displaying here is complicated and I did a mistake that I will show later).

Next, align the series that will be played at the same time. Each team has to be shown once per lane. I have no specific way to aligned them. It is usually tries and errors. This could be intimidating at first. Sometime it is easy, sometime it is harder. With time, I developed tricks, but I am still working on how to align series. Here, what I can say is because of the odd number of team per SL, when Teams play Intra-League, one team in each SL have to play together (Inter-League).

Here’s the picture of how I aligned the series.

[Kirby Paquette]

Here’s the picture of how I aligned the series. should be this Image

[Kirby Paquette]

Note that I grouped together the 4-game series. It will be easier to spread them into the schedule and avoid to play 4-game series too close to each other.
Note the extra black number representing the team left out of the Intra-division matchups, and that will play IL for the occasion. The black number is just an indication. I don’t need it for scheduling.
Note also that I moved around lanes of intra division matchups on the left to avoid a 2nd IL confrontation between 2 teams (or even a 3rd IL confrontation since it can’t be).

[Kirby Paquette]

02 Schedule Assembly

I transfer the series lanes to the Schedule Assembly sheet. I distribute the lanes according to the column with the numbers from 1 to 52, representing the weeks (or the half-weeks) of the season. The Black square is the half week for the All-Star Break.



I first distribute the lanes with the 4-Game series, so they are well spread.



According to the number of Intra-League and Inter-League “lanes”, I decided on how I want to spread it.



I copy the final lane assembly in the Black Box that is on top right of the sheet. I call this the “Trading Room”.



I also copy it under the grid under the black box.

[Kirby Paquette]

I spread it, leaving a space between each series, and I double the whole thing.



I fill the grid with all the series, one column for each team.



When it is all filled, we can see that there are series that are played consecutively. I will shift lanes to separate these series. When I do the shifts, I do them in the Black Box first before doing them in the grid under. It is very important to do the shifts in the Black Box because that is what will be used for the Layout of the schedule.



Here’s the grid after shifting lanes. Note the number 1 to 52 in the middle, that are not in order anymore, representing how I shifted lanes.



Here’s the Black Box as well. Again, note the number 1 to 52 in the middle, that are not in order anymore, representing how I shifted lanes.

[Kirby Paquette]

Next, remove the Filling color in the Grid.
Note, another lane that need to be shifted...
I changed the color of the opposing team in each column (Blue). Changing the color allow me to see what series needed to be traded to break stretches of 4 or more series played “away” or at “home” consecutively.



Verify that all teams play 26 series at home and 26 series on the road. Here, I notice that teams 4 and 6 played a ratio of 25/27 or 27/25 meaning that I did a mistake translating the matrix.



I went back, tracing and correcting the mistake.



Start identifying the series to be traded.



There are a lot! Trading is very long. I spend hours doing this. Here is the first round of trades that I intend to do.

[Kirby Paquette]

Don’t forget… All trades have to be done in the black box first. The goal here is to have no more then 3 series (Home or Away) consecutive.
Because of the complexity of this set up, I could not get it. The best I could do, was a few stretches of 4 series played consecutively. Here’s the final result.
Note that I have highlighted the 4-game series, showing how well spread they are. I know, by the display, that teams won’t play more than 13 games without a day off.
Note also that the schedule for team 22 is almost perfect in what I am looking for, especially the second half of the season.

[Kirby Paquette]

03 Schedule Layout

Copy and paste the Black Box at the bottom of the Layout sheet. Copy and paste the lanes at around week 9 of the sheet.



Bring up the first lane to Day 2 of the grid (Which is Friday of Week 1).
Note the colors: brownish for Thursdays, Blue for Saturdays and Grey for Sundays.
All series start either Friday or Monday. Thursday is for the 4th game of a 4-game series.



This is the whole grid after all is spread out. The Allstar game day is in green at day 104, (Tuesday of week 16). I have two sets of dates on the left, just to figure out when (the sooner or the later) the schedule will be played (my Excel version is in French). I know the Allstar game will always be played between July 9 and July 15. The season will start between March 28 and April 3.

[Kirby Paquette]

04 The Schedule

The first thing to do is paste “The Swiss Knife” on the right in the schedule sheet. I call it “The Swiss Knife” because it is very useful. It allows me to transposed the rows of games of a whole week into columns that will be used for the final assembly of the schedule.



I have designed it for 40 teams (or 20 games) but it can be easily adapted for 22 teams (11 games) by just shorting up the ROWBOX. Then bringing up the SK-BOX and the GAMEBOX covering position 12 to 20. I do that for all the seven “days” of the week.




Here it is, all adapted for my needs.



I copy and paste a bunch of them, one under another.

[Kirby Paquette]

Copy and paste the lay-out of the schedule on the right.



Remove the “week number” column. Remove the blank spaces. Bring all the matchups to the right of the “game days”. Bring all the Thursday games to the left of the layout (Except for the first week and after the Allstar break that are on the far right (Note my mistake on day 57 that I have corrected on the way).



Copy the first week (Starting from Monday even if it is not represented in the layout).



Paste In the ROWBOX. This will be automatically generated in the SK-BOX.



Copy the whole content of the SK-BOX.

[Kirby Paquette]

And Paste Special (Values) the content in the GAMEBOX.



Copy the Games in the GAMEBOX.



And paste them in the 3 appropriate columns, on the right of the “Swiss Knife”.



Repeat the process for all the weeks. Do not paste the blank days (gamedays where there are 0 0).

[Kirby Paquette]

Copy the column of the “Game days”.



And paste the column in column “A” of the schedule.



Copy and paste the 2 columns of “Teams” in columns “D” and “F” of the schedule.



Manually set the times for each game in column “B” of the schedule. I did set the times at 7:05 pm for weekdays, a mix of 1:05, 4:05 and 7:05 for Saturday games and 1:05 for Sunday games. There are a few day games for the first day of the season as well as the first day after the Allstar break.



I use the function =CONCATENATE in column “H” of the schedule. That will combine the content of columns “A” to “G” of the schedule. I copy the whole column “H”.

[Kirby Paquette]

And Paste Special (Values) in column “I” of the schedule.



This is the schedule that can be finally transfer (copy and paste) into a “schedule.lsdl”.



I usually copy and paste in a pre-existing “schedule.lsdl”, modifying the header accordingly.
I copy paste the Allstar game (that is hidden on top, somewhere in the first 10 rows of the schedule sheet) between day 102 and 106 in the “schedule.lsdl”.



Test the schedule in OOTP.

[cheo25]

Thanks for sharing this and the tools you use. I find this fascinating, and I like to see the thought process different people have used to put together schedules. I like the attention to detail you have, especially around things like minimizing the lengths of road trips and streaks of games played without a day off.

My process is a lot less detailed, and the thing I struggle with is pairing stretches of games where teams that have been on the road and at home together now have to play each other. The scenarios that come to mind are the LA/SD/SF trio that play East Coast teams and then play each other when the games against the East Coast teams are done. In OOTP, it really doesn't matter because after the first season, the teams are really just numbers and OOTP is going to randomly assign teams to number or numbers to teams. But I always seem to end up with too many 2-week road trips or homestands.

No schedule is perfect, but I've always liked to put teams into mini-groups and have them play stretches of games against other mini-groups. That works well for the end of the season in trying to have as much divisional play as possible. The trade-off is that sometimes I end up with longer-than-desired road trips or homestands.

[pgjocki]

Thanks and I have to admit that this gives me anxiety just looking at it and thinking how I might screw it up.