+ Reply to Thread
Results 1 to 8 of 8

Random Sports Schedule Creator with 2 Divisions

  1. #1
    Registered User
    Join Date
    06-10-2013
    Location
    Columbus, OH
    MS-Off Ver
    Excel 2011 Mac
    Posts
    50

    Random Sports Schedule Creator with 2 Divisions

    I have 2 divisions of 7 teams each (TEAM 1A-7A & TEAM 1B-7B).
    TEAM 1A-7A must play each other once & 3 of 1B-7B (9 total games).
    TEAM 1B-7B must play each other once & 3 of 1A-7A (9 total games).

    So the Schedule Matrix should look similar to the following (simplied example, but would like this to be random)
    " " - Represents a blank since TEAM 1A cannot play TEAM 1A
    Also, all schedule must coordinate (i.e. when TEAM 1A plays TEAM 3A, TEAM 3A must being scheduled to play TEAM 1A (italisized below)

    1A-" "-2A-3A-4A-5A-6A-7A
    2A-7A-1A-" "-3A-4A-5A-6A
    3A-6A-7A-1A-2A-" "-4A-5A
    4A-5A-6A-7A-1A-2A-3A-" "
    5A-4A-" "-6A-7A-1A-2A-3A
    6A-3A-4A-5A-" "-7A-1A-2A
    7A-2A-3A-4A-5A-6A-" "-1A

    Would Like a matrix for the other 3 Games as well versus the other division (again, random).

    1A-1B-2B-3B
    2A-2B-3B-4B
    3A-3B-4B-5B
    4A-4B-5B-6B
    5A-5B-6B-7B
    6A-6B-7B-1B
    7A-7B-1B-2B

    Hope this makes sense...It's quite a doozy! Thanks

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Random Sports Schedule Creator with 2 Divisions

    Hi Phily915,

    I did a problem very similar to this a few months ago. Let's see if we can solve your problem with my old workbook.

    My problem was this:
    On Wednesday evening a bunch of guys would come to play golf in a match play event. The head guy didn't want any two guys to ever play against each other twice. There was a list of about 100 possible people who would show up but only about 50 would ever show up at a time. I wrote a workbook that did this problem for them. (they never used it because they really wanted to pick who played against whom). Anyhow (sour grapes) this workbook can be used to solve your problem.

    If you make the players Teams A1 -> A7 and B1 - B7 and double click in Col A to show they are going to play this Wednesday. Then Click on the Pairings button and let the group size be 2 it will randomly select who plays against who. You will need to click on ALL A team players and a random selection of 3 B teams for the first round. Then POST the results to the results sheet where I use it to see if they have played against each other or not previously. For the second round, leave all 7 A teams and "x" 3 different B teams and make a new round. Post this round again. Do this 7 times and you will have a random drawing of all 7 A teams and 3 B teams. Now do this for all 7 B teams and a random selection of 3 A teams.

    Note - there is no guarantee that your problem has a solution!! It might be impossible to give an answer down towards the last matches, but this workbook will minimize the number of duplicate pairs. Find the attached... Have fun with it...

    BTW - If you choose a group size of 3 or 4 it is looking for a group of 3 or 4 people who have never played with each other before. In golf we call his threesomes or foursomes. In your case you should use the pairing size of 2.
    Attached Files Attached Files
    Last edited by MarvinP; 06-15-2013 at 11:43 AM.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    06-10-2013
    Location
    Columbus, OH
    MS-Off Ver
    Excel 2011 Mac
    Posts
    50

    Re: Random Sports Schedule Creator with 2 Divisions

    thanks for the response marvinp. i will check out the workbook and see if i can figure it out

  4. #4
    Registered User
    Join Date
    06-10-2013
    Location
    Columbus, OH
    MS-Off Ver
    Excel 2011 Mac
    Posts
    50

    Re: Random Sports Schedule Creator with 2 Divisions

    So I followed your instructions, except I left out the B-division teams for now and just ran the 7A division teams. I renamed the "players" from A1-A7 as follows: Boston College, Clemson, Florida State, Louisville, NC State, Syracuse, Wake Forest.
    I double-clicked each of those teams and got the x. Switched to the "WeekRound" page and resized group to 2. Then made pairings.
    My question arises at the "AllResults" page. It seems to have randomized the 7 teams and I assume if Group 1 has "Louisville" followed by "Wake Forest" they are a pairing. But what does it mean if Group 4 only has "Clemson"?? If I could guess, since there's 7 is odd there will always be an odd man out (so to say) with each pairing calculation.
    This is a very impressive code. Id be interested if we could speak further to customize my needs. If you would be interested. Thanks,
    Michael

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Random Sports Schedule Creator with 2 Divisions

    Hi Phily,

    On the Results Sheet the Group Number a person has played in, means they have played against each other. If you only start with 7 teams then one team must NOT have had an opponent. This workbook uses Advanced Filters to see if any of the two random pair teams have the same group numbers from the results sheet. If they do, that means they have played against each other previously. If they don't then the next pair is checked against previous matches.

    Did you Clear Contents of the Results range before you started your experiment?

    I hope this explains how this works. You really need to include at least a single B team so the number of teams is an even number before doing pairings. Make sense?

  6. #6
    Registered User
    Join Date
    06-10-2013
    Location
    Columbus, OH
    MS-Off Ver
    Excel 2011 Mac
    Posts
    50

    Re: Random Sports Schedule Creator with 2 Divisions

    I get what your throwin' at me. I just gotta figure out how to incorporate this into my sheets for I am a noob. Thanks for all your help!

  7. #7
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Random Sports Schedule Creator with 2 Divisions

    This is not an easy problem.

    I'd start with the 7 A teams and add 3 of the B teams and do the first random draw and post them to the results sheet. Then I'd pick 3 other B teams a do it again. I'd do this 6 times and post the random draws to the results sheet.

    Then I'd do all the B teams and pick 3 A teams at random. Do this 6 times and you should have what you asked for.

    The code picks all the people with an X next to their names and randomly sorts them. Then it starts to pair them at random. With each pairing it checks the results sheet to see if they've played against each other before. If they have it tries another pair (from what isn't yet paired) to see if it can create a new and unique pairing. It will try 100 times to create a unique pairing (might be 1000 - I forget what I coded into the VBA). It will stop trying if it has found a good result or hit the max number of tries..

  8. #8
    Registered User
    Join Date
    06-10-2013
    Location
    Columbus, OH
    MS-Off Ver
    Excel 2011 Mac
    Posts
    50

    Re: Random Sports Schedule Creator with 2 Divisions

    yea i could just do that and type them over onto my pages. That would probably be best since I already have a lot of calcs going! Ill go ahead and say its solved. nice work!

+ 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