+ Reply to Thread
Results 1 to 7 of 7

Non-repeating sports league schedule generator

  1. #1
    Registered User
    Join Date
    06-14-2011
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 2011
    Posts
    16

    Non-repeating sports league schedule generator

    Right now I have a sports league with 8 teams, I'd like to create a random 7-game schedule where each team plays the other team only once. Essentially this will be a 7-game round robin. However, I'd like to be able to use this for any number of teams and games.

    I'd like to do this in Excel, but I can't figure out how to have a randomly generating non-repeating macro with text values in cells.

    So right now I have a column of 8 values and need matrix of 7 columns by 8 rows next to it.

    I've found this thread that has one for numbers, but I can't figure out how to do it for text values...

    http://www.excelforum.com/excel-prog...m-numbers.html

    Thanks all!

  2. #2
    Forum Contributor sauerj's Avatar
    Join Date
    11-11-2009
    Location
    Lafayette, IN
    MS-Off Ver
    Excel 2010
    Posts
    159

    Re: Non-repeating sports league schedule generator

    Attached is a 2-dimensional matrix random generator macro which sponges off of the link you provided. But the trouble is that it doesn't work like you would like. As the numbers get generated & placed in the table, the macro gets stuck where, for the latter cells, all the numbers are "taken" (if you look in both the column & row directions) and no valid number can be used. Run this macro and study the results and you will see what I mean. I would scrap this approach.

    On the next tab (Option2), I setup a table that uses formulas to generate a tourney line-up. The formulas can be used for any size matrix. I have a 10x9 (rxc) matrix populated, but you only see the 8x7 matrix for ths 8 team option. The outcome of these formulas follow a basic ordered pattern: Team1 plays Team2 - Team8 in games 1-7, and Team2 would play Team3 - Team8, then play Team1 at the end, and Team3 would play Team4 - Team8, then play Team1 - Team2 at the end, and so forth. I would use this same pattern for every tournament. But, then just scramble the order of the teams in the 'A' column and you will have a random generated line-up.

    Note, that the formulas in row1 of the matrix are different then the other rows. This top row formulas only look at the cell column. The rows below then reference the values in their preceeding row above.

    The TeamName() and TeamNum() functions only serve the purpose of converting the Team Number (sequential number as listed in column 'A') to its corresponding Team Name, or visa-versa.

    Note: The Opt2 macro requires setting up a Range Name ("Team.Rng") . This Range Name can be defined from A2 down to as far as you want to go (for whatever max size roster you'd expect). If the Team roster will always be in column 'A', then you can replace this "Team.Rng" with "A:A". ... sauerj
    Attached Files Attached Files

  3. #3
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    39,482

    Re: Non-repeating sports league schedule generator

    In the attachment, you can change B3 between 3 and 32 to see round-robin pairings.

    Then you just need to assign team numbers randomly to teams.
    Attached Files Attached Files
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Forum Contributor sauerj's Avatar
    Join Date
    11-11-2009
    Location
    Lafayette, IN
    MS-Off Ver
    Excel 2010
    Posts
    159

    Re: Non-repeating sports league schedule generator

    shg's worksheet points out some glaring errors in my thinking. My structure set up a team to play two games in the same round (boo!). Thanks shg for your clear thinking. kabnt2005, neglect my entry. shg's wkb should work well. It even accommodates an ODD number of teams. Play Ball! ... kabnt2005, If you need further help, holler!

  5. #5
    Registered User
    Join Date
    06-14-2011
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 2011
    Posts
    16

    Re: Non-repeating sports league schedule generator

    Thanks everyone for the help.

    While I would have preferred a truly random schedule generator instead of just a rotating one, this will work.

  6. #6
    Registered User
    Join Date
    08-26-2013
    Location
    canada
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Non-repeating sports league schedule generator

    I Noticed how well these programs work for only 2 teams per match... Is there anyhting that can make a schedule that has 3 teams per match and nobody ever plays each other more than once.

  7. #7
    Forum Contributor sauerj's Avatar
    Join Date
    11-11-2009
    Location
    Lafayette, IN
    MS-Off Ver
    Excel 2010
    Posts
    159

    Re: Non-repeating sports league schedule generator

    cms541, I see you are new to the forum. Take a moment and glance at the forum rules (click the Forum Rules at very top). See rule #2; you'll get a better response if you follow that rule. To start a new thread. Go to "Forum / Excel General" and click the "Post New Thread" button. Ask your question this way and in that post, include a LINK to this thread.
    Addressing your question: I don't understand it. Can you not use SHG's worksheet and type '3' in cell B2 (for a 3 team tourney)? Or, do you mean something else?
    In your new post, attach a worksheet with an example tourney scheduled approximately with the structure that you mean. That will help others understand and get you an answer faster. ... good luck, sauerj

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1