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.
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.
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:
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
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
so to make a schedule for:
- 3 courts
- 6 weeks
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
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
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:
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:
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.
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
The third table is "Facility set up" where you enter information regarding the season, number of player, and Number of courts.
B4=MAX(Paste Set Up Here :: B)
cell B2 is set up as a pop-up menu that contains two entries: "Single", and "Double":
The table "Schedule" is made as follows:
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.
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.
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.
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.
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.
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!