Previous 1 2 Next 23 Replies Latest reply: Dec 13, 2012 7:02 PM by Wayne Contello
Rob_NL Level 1 (0 points)

I need your help how to automatically create a player schedule from a given list of players who will play doubles in a given period.


Let's say, I have a list of 12 players who will each play doubles with random partners for 4 times.

So play one could be: 1&2 vs 3&4, 5&6 vs 7&8, 9&10 vs 11&12.
But now I want inumbers to plan the second, third and fourth play automatically but with unique combinations.

In this example it's quite easy to do it manually, but I need to plan something similar for the tennisclub with many more players.

It should be able to set up a worksheet where you will fill out the following parameters:

List of players, number of available courts, whether you want ot let them play single or double games, number of plays.
The last one could also be left empty so discover how many unique plays you have given the other parameters.


I hope someone is able to help me, because this would save me a lot of time to make the planning for the tennisclub.

Thanks in advance for any support!

MacBook Pro (15-inch Late 2008), Mac OS X (10.7), Ipad 2, Iphone 4s, Apple TV
  • Wayne Contello Level 6 (16,960 points)



    This may be possible but it would certainly help if you have some guidance regarding the presentation of the results (the output).



  • Rob_NL Level 1 (0 points)

    Hi Wayne,


    In this example the output could be as follow:IMG_0974.jpg

    Does this help?


    Thanks so far!



  • Wayne Contello Level 6 (16,960 points)

    I don't play tennis 

  • Barry Level 7 (29,610 points)

    Hi Rob,


    It's an interesting problem. So far, I've only tackled the issue of the first round, which is pretty easy. After that, keeping the random partner assignments from  repeating a team throws more than one spanner into the works!


    From one point of view, the simplest approach would be to set up (manually) the full schedules of se to assign the players to those specific team numbers and LOOKUP to match the names with the previously calculated numbers.


    May get another chance to look at this tomorrow.




  • Wayne Contello Level 6 (16,960 points)

    I am with Barry.  I made a bingo document for my wifee using Numbers.  To make many cards that were randomized I had to write a program to generate many lists of the same set of numbers that were randomized.  I think this is same problem.  The only "tricky" parts are that if you have:

    - singles then there MUST be an even number of players

    - doubles then there must be a multiple of 4 players


    Can you promise these conditions are true?


    I can make a program to generate the randomized list.  The result of this program will be a copy, then paste into a special table in Numbers.

  • Wayne Contello Level 6 (16,960 points)

    OK.  I think this may work for you:


    There are two parts:

    1) a small program you have to run (this is the part you may find difficult at first)

    2) a Numbers spreadsheet



    1) the program is invoked on the command line in the terminal.  You can open the Terminal by navigating to the folder "/Applications/Utilities"


    you need to download the zip file here, then copy and unzip  in the same folder where you plan to store the Numbers document for making the tennis schedule.


    Let's agree for this procedure that you are storing the program and Numbers file in the folder "/Users/<your user name>/Documents/TennisSchedule".



    Once you launch the terminal you can navigate to the folder where you stored the zip file by typing:


    cd ~/Documents/TennisSchedule


    now you can invoke the program by typing:



    Then hit the return key.  Without any arguments the program simply emits some program information with a little help on how to use it-- described in the text starting with "USAGE" (see below)


    Proton:TennisRandomList wayne$ ./TennisRandomList



    Tennis Randomw List maker

    Version 0.1

    compiled on Nov 18 2012 @ 09:14:41

    USAGE: TennisRandomList numPlayers numCourts numWeeks type verbosity

      numPlayers: 1 - 255

      numCourts: 1 - 255

      numWeeks: 1 - 255

      type: [s]ingle or [d]ouble

      verbosity: 0 - 5

      0 -> player ids only

      1 -> player ids + inputs

      2 -> player ids + inputs + raw random values

      3 -> player ids + inputs + raw random values + pairings

      4 -> player ids + inputs + raw random values + pairings

      5 -> player ids + inputs + raw random values + pairings



    Proton:TennisRandomList wayne$



    so to make a schedule for:

    -12 players

      - 3 courts

    - 6 weeks

    - singles


    you would invoke as:


    ./TennisRandomList 12 3 6 s


    Then type return.


    you should get something like:



    Proton:TennisRandomList wayne$ ./TennisRandomList 12 3 6 s















    Weeks: 6

    Players: 12

    Courts: 3

    Type: singles


    Week          Slot          Court 1          Court 2          Court 3

    1          1          3          2          4          6          5          8

    1          2          9          11          10          12          1          7

    2          1          12          2          7          10          11          1

    2          2          3          5          8          4          9          6

    3          1          6          11          2          1          7          3

    3          2          4          8          5          10          9          12

    4          1          4          8          11          3          5          7

    4          2          10          12          1          6          9          2

    5          1          8          4          3          12          9          5

    5          2          6          10          7          11          1          2

    6          1          5          7          1          12          3          10

    6          2          6          8          2          4          9          11

    Proton:TennisRandomList wayne$

    You can select, then copy the text following the line "Week Slot Court 1 Court 2 Court 3" upto, but not including, "Proton:TennisRandomList wayne$ "-- bolded in the above quotation


    You can paste that into the Numbers document we will now make.


    2) a Numbers spreadsheet


    The Numbers document includes several tables as shown below:

  • Wayne Contello Level 6 (16,960 points)

    The forum cut me off so I will try to post how to assemble the Numbers document now:


    There are several tables as shown below:

    Screen Shot 2012-11-18 at 1.18.39 PM.png



    The first table is titles "Players" and is where you enter the names of each player.  The first row is a header.  For convenience put the following formula




    select A2 and fill down as needed.  This will make the first column update automatically if you expand the table.


    Enter names as needed.

    Screen Shot 2012-11-18 at 1.21.42 PM.png



    The second table is the table where you will paste results from running the command line application in the terminal.


    You just need a table to exist so make a table with a header row as shown.  Be certain to delete the contents of NON-HEADER rows before pasting in new data.  You DO NOT need to type all those number into the cells in rows 2 and on


    Screen Shot 2012-11-18 at 1.23.05 PM.png




    The third table is "Facility set up" where you enter information regarding the season, number of player, and Number of courts.

    Screen Shot 2012-11-18 at 1.26.19 PM.png

    B4=MAX(Paste Set Up Here :: B)


    cell B2 is set up as a pop-up menu that contains two entries: "Single", and "Double":


    Screen Shot 2012-11-18 at 1.27.34 PM.png





    An finally...

    The table "Schedule" is made as follows:


    Screen Shot 2012-11-18 at 1.28.38 PM.png


    row 1 is a header.


    C1=IF(COLUMN()-3<Facility set up :: $B$1, "Court "&COLUMN()-2, "")


    select C1 and fill to the right as needed (I went all the way out to column L)


    A2=INT((ROW()-2)/Facility set up :: $B$4)+1

    B2=IF(A2<=Facility set up :: $B$3, MOD((ROW()-2), Facility set up :: $B$4)+1, "")

    C2=IFERROR(IF(C$1<>"", IF(Facility set up :: $B$2="Single", VLOOKUP(INDIRECT("Paste Set Up Here :: "&CHAR(CODE("C")+2*(COLUMN()-3))&ROW() ), Players :: $A:$B, 2, 0)&" vs "&VLOOKUP(INDIRECT("Paste Set Up Here :: "&CHAR(CODE("C")+1+2*(COLUMN()-3))&ROW() ), Players :: $A:$B, 2, 0), "("&VLOOKUP(INDIRECT("Paste Set Up Here :: "&CHAR(CODE("C")+0+4*(COLUMN()-3))&ROW() ), Players :: $A:$B, 2, 0)&" & "&VLOOKUP(INDIRECT("Paste Set Up Here :: "&CHAR(CODE("C")+1+4*(COLUMN()-3))&ROW() ), Players :: $A:$B, 2, 0)&") vs ("&VLOOKUP(INDIRECT("Paste Set Up Here :: "&CHAR(CODE("C")+2+4*(COLUMN()-3))&ROW() ), Players :: $A:$B, 2, 0)&" & "&VLOOKUP(INDIRECT("Paste Set Up Here :: "&CHAR(CODE("C")+3+4*(COLUMN()-3))&ROW() ), Players :: $A:$B, 2, 0)&")"), ""), "")


    select C2 and fill to the right as needed (I went out to column L)


    now select A2 thru L2 and fill down.


    This is a lot, so please post back if you have comments, or questions.


    Best regards,


  • Wayne Contello Level 6 (16,960 points)



    Have you had a chance to review this post?



  • Rob_NL Level 1 (0 points)

    Hi Wayne,

    First my apologies for not replying earlier, because I was caught up in work and some private stuff.


    Thanks for your help so far.

    It's a bit complicated for a none-tech user like me, so I first need to find the time to understand it step by step.

    I will try to take a look at it during this week.

    If I have any questions, I will come back to you.

    Thanks again!






  • Wayne Contello Level 6 (16,960 points)

    No worries.  Let me know if you have any questions

  • Wayne Contello Level 6 (16,960 points)

    I  made a web interface for the command line application I provided.  It is here:


    you get the same output and can copy and paste the table from the web page into the Numbers table (so long as you use Safari).  I may try an applescript which could be directly integrated but I am less familiar and always get frustrated becuase I know what I want to do and end up fighting with syntax. 

  • Rob_NL Level 1 (0 points)

    Hi Wayne,


    Wow this really helps and makes it very user friendly.


    Only one small remark from me.

    If you would select 4 players the players are numbered 0, 1, 2 and 3.

    Maybe it would make it more clear if that would actually be 1, 2, 3 and 4.

    No idea if that is difficult to correct, don't worry if that's the case ... I am already happy with this result.


    Thanks again!


    Kind regards,




  • Wayne Contello Level 6 (16,960 points)



    1) I corrected the 0 - n-1 to be 1 - n

    2) this is a demo only and since it is located on my personal domain it is not guaranteed to always be there.  I provided the command line application so you would be self supporting.  Not saying there is a reason my domain will not be available just pointing out there is no guarantee.


    I think you should get familiar with the web tool and how that interacts with the Numbers tables, then try the command line application.

  • Rob_NL Level 1 (0 points)

    Hi Wayne,


    Thanks for making the update 1-n!


    I come across another challenge. Our current list of participants is 17 persons long and I would like to make a schedule for doubles, with 5 courts and 6 weeks in a row.

    Would it be possible to show the result with 4 players on the first 4 courts and one player on court 5. Of course (s)he can't play then, but at least (s)he knows. In the case we have 18 players then 2 players will be listed on court 5. They can play a single match then. In the case we have 19 players, there will be 3 players on court 5. They could play 2 against 1 (we call it "an American game") or 2 play a single match and will be substituted by the third person after one set is finished.


    I hope this is only a minor change!


    Thanks again!

Previous 1 2 Next