OK, so you had an idea for your first tournament; you looked at other tournaments; read up on the procedure and published your tournament thread. Shortly afterwards, eager medal hunters filled all the available spaces and you are up and running with tournament privileges. Now you begin to invite the players into the games and realise just how tedious this is – and sometimes difficult to keep track – and wish you had thought this bit through a bit better.
Then, as the games get under way and results start coming in, you keep the tournament thread up-to-date and you wished you had thought of a less complex tournament and wonder how best to manage the updates with a minimum of effort.
That was my experience anyway. So, this series is about different tournament types and techniques of managing them, and, since I am still learning, I’m sure that there are many others out there who have far better methods; if so let me know and I will publish them.
So, let’s start by recapping the steps you needed in beginning your first tournament:
Scroll down to the bottom for the “Organising Your Thread” and “Tournament Tools” for the tags and tools to make your tournament invitation thread attractive and organised. There is also a very good set of articles by rdsrds2120 called "The Forum Tips Thread" that gives additional information. http://www.conquerclub.com/forum/viewtopic.php?f=57&t=17807.
For instance: The above was created using the following tools and tags:
Note that the image was uploaded to a free hosting site; Photobucket or, in this case, ImageShack. You don't have to have a picture, but you could include your banner or, as greenoaks' series of Watch and Learn or KISS, he has a special banner to identify the particular series. If you don't have an appropriate banner, then a copy of the map used in the tournament can always be inserted if required.
OK, so now you have a tournament thread that can be posted to attract players. As you get postings from players you can edit your thread to incude their names and update your title to indicate the number of players already included: "My Fantastic First Tournament (5 of 16)". Then post a thread to say that you have included "up to here" - communication to your players is always good practice!
When you have your full complement of players and perhaps one or two additional reserves, you change the title to "My Fantastic First Tournament (Full)" and magically, it seems, within a day or two you have a pm giving you tournament rights and allowing you to create your games. Let's say you invite your players rather than send pm's (I'll write about a few short cuts for pm'ing in a future article) and you decide to keep track of your games using Challonge (challonge.com). This is a useful (free!) site that allows you to select the type of tournament; say a single elimination, then you copy in your list of players; you can even randomise them using Challonge. There is a link adress on the site that you can paste into your tournament thread that gives a picture of the tournament progress. The following is from one of my completed tournaments. Remember that you still need to occasionally post progress notes onto your tournament thread, as updating Challonge does not affect the thread directly.
So - there is your first tournament set up and under way - it is so easy there is no reason why you should not start right away!
#2, Issue 59 - Different methods of randomisiong your list of players.
Once your tournament is full; you have tournament rights and you have created the blank games, you need to assign players to each game.
In many cases you first need to randomise your player list and there are several ways of doing this. If you are showing the results using Challonge then you can shuffle your list and then assign the list to be paired either sequentially (1 v 2, 3 v 4 etc) or traditionally (1 v 16, 2 v 15 etc as though they were seeded in order).
If you are not using Challonge, you could shuffle your player list by copying them to Random.org (http://www.random.org/lists/) and pasting the results back into an Excel spreadsheet. However you then find that each randomised name is preceded by numbers. An easy way of getting rid of these is by use of the following Excel function:
Column B has the player list (as entered); Column C is the randomised list from Random.org and the function copied into Column D is used to strip out the numbers. The formula in D3 is:-
For those of you wanting to know how the formula works, the RIGHT(C3,N) function copies the rightmost "N" characters from the text in cell C3. The LEN(C3) function gives the number of characters in the text in cell C3. The FIND(" ",C3) function finds the position of the space in cell C3. Therefore the formula copies the rightmost characters from the position of the space. Remember that if you then copy from this formula column you have to Paste Special/Value otherwise you get the function copied!
Greenoaks, master tournament organiser of over 100 tournaments (including his KISS, Watch & Learn and Best at Beta series) reminded me that you can also use random.org for randomising the game settings - and that gave me the idea for the "Random, Random, Random" tournament I started recently (though I am sure that the idea is not at all original!).
Another method of randomising your player list is to have your player list in Excel and paste the Random function RAND()in the next column. This function puts a random number between 0 and 1 into the column. You then select the two rows and sort according to the random number;
You find that each time you sort or refresh, the random number changes.
Note that if you are planning to set up an Excel template for many tournaments (and you are rather lazy!) you can create a macro to do the pasting and sorting. Simply select Tools/Macro/Record New Macro... and then give your macro a name and perhaps assign a key. Then just go though the row select and sort process and end your macro - done! If you want to put a button on your spreadsheet that carries out this new sort macro then select Tools/Customise/Format then drag the button icon to your toolbar. Then by selecting this icon then drawing a rectangle on your spreadsheet a button appears and you go straight to a window that allows you to assign your macro to this button. Job done! However, remember not to run this macro after you have started your tournament!
(These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to!)
#3, Issue 60 - Excel coding of single player CC game invite pm's.
Once your tournament is full; you have tournament rights; you have created the blank games and randomised your player list. Now you need to invite players to join their games.
If there is one game per round, you can invite them easily enough, but it is less work for you to send them a personal message and let them join the games themselves. With several games per player, the pm saves a lot of additional work. For instance if you have 16 players, then you need 16 invitations or 8 pm's. If there are three games per pairing, then you either need to make 48 invitations or just 8 pm's.
To keep it simple, let's firstly compose a pm for 1 game that allows players to join their game. Column B is the randomised player list and column C the RAND(0) function which was used to randomise the player list (and has changed its values once again!). The password (don't forget the password - as I did recently!) is in D2. Into column D are copied the game numbers - a tip here is that when you create the games you end up with numbers that are mostly in sequence. You can therefore copy the largest game number into the first game - say that is cell D4. You can then put formulae into the other cells that refer to cell D4 minus 1 (or 2 etc if the numbers are not quite sequential) and so on.
You can aso add a greeting message in your spreadsheet. "Welcome to my Fantastic First Tournament! We can now get under way and your first round game is as follows. Good Luck! ". Let's say you write this into cell G3 and change the code to:
tag puts the game information on the next line (which looks a bit better!). This then creates the following message and game information:
Welcome to my Fantastic First Tournament! We can now get under way and your first round game is as follows. Good Luck! View Game 1234567 (Auto-Join Game)
OK - so now we can expand that for, say, three games:
Click image to enlarge.
The code is three concatenated formulae (i.e. 3 x single invites as above):
Note that you can cut and paste these as required, but remember that they only relate to the column and row numbers that I have specified in my examples, so you need to change them to suit your layout. OK, so this code translates into 3 game invites that can be copied into the player's pm;
This process is slightly different when you have team entries, but the basic process is the same. Now the invites are out, you just need to sit back - apart from checking that the players have all joined and, of course, keeping up with result reporting.....
(These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to!)
#4, Issue 61 - Excel coding of game progress reports for your tournament thread.
Your tournament is up and running. Now you need to occasionally check on progress and update the results when games have finished.
To keep track of the games, the "Tour Stats" button on the game finder gives a summary of the state of progress of your tournament/s. If one or two games are taking a bit longer to finish and you want to send out the next round games as soon as possible after these games have finished, you can also install and use the Turn Watch greasemonkey script which adds a tab listing all the games you have clicked to watch.
When games have finished and you want to update your tournament thread, a simple process I use is a spreadsheet with functions to simplify the copy for your thread. Consider a simple 1 vs 1 knock-out:
Click image to enlarge.
Columns B and C are the lists of competing players and column D the game number (yes, I know that in my last spreadsheet about inviting players by pms, the players were all in one column, but the following example is to keep the coding as simple as possible). The next column is blank until the game is finished, then the winning player's name is copied in. The code in column E is:
="[game]"&D3&"[/game] "&IF(E3="",B3&" vs "&C3,IF(E3=B3,B3&" vs [s]"&C3&"[/s]","[s]"&B3&"[/s] vs "&C3))
For those of you who want to understand the Excel functions, after the text to insert the game number there is a conditional statement; if the winner cell is blank, then put in the two players' names , else if the winner text is PlayerA, include the "s" tag for PlayerB, otherwise include the "s" tag for Player A. The "s" tag puts lines through the loser's name. When the text in column E is copied to your tournament thread in the appropriate place, you get the following: Game 1234567 Player4 vs Player2 Game 1234566 Player5 vs Player1 Game 1234565Player3 vs Player7
Note that the above coding does not take into account the winning player being mistyped into the column. However, you can amend the code to take care of this eventuality:
Of course, there are many variations of this format that you may want to use. If you have several players, then the coding to put lines through the losers can get quite tricky, so I have used the following for a 4-player Assassin tournament:
Click image to enlarge.
This simply adds the name of the winner after the game number and list of players. The coding for this is:
="[game]"&F3&"[/game] "&B3&", "&C3&", "&D3&", "&E3&(IF(G3="",".","; won by "&G3))
Again, you just copy the column and paste into your tournament thread. Game 1234567 Player1, Player2, Player3, Player4. Game 1234566 Player5, Player6, Player7, Player8; won by Player5
And there you are; "proper job" as they say here in Devon! Merry Christmas To You All!
(These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to!)
#5, Issue 62 - Excel macro to summarise your tournament thread's winner list.
I'm quite lazy. Once I have updated my tournament results in Excel I like to be able to copy a block of text into the tournament thread to update the winners without needing additional typing. Therefore I use functions and macros where possible. Macros are accessed in Excel from the Tools/Macro menu. The best way I find of creating a new one is to go to Tools/Macro/Record New Macro; then enter the macro name (and perhaps a key press) that you want to use and then click the "stop" logo on the small window that appears. Then go to Tools/Macro/Macros, highlight your macro and choose "Edit" to open your macro into a new window so that you can edit and test your macro before saving the final version.
Obviously there are many different types of tournaments and ways of scoring, so I'll give an example from one of my present tournaments. In this one, a 4-player assassin tournament, I am recording the winner's name for each game and therefore need a macro that adds up the number of wins by each player. The winners' names are in a list and I use the following macro to scan down the list and copy the winners names into a separate list.
Dim i, j, itotal As Integer 'integers used in this macro Dim Aname, Bname As String 'string fields used to hold the players' names itotal = 0 'itotal is the number of winners For i = 47 To 112 'start to last row number of the winners' list Aname = Cells(i, 25).Value '25 is the player list's column number If Aname <> "" Then 'not blank - so a player's name itotal = itotal + 1 ' increment the number of winners Cells(itotal + 46, 29).Value = Aname 'put the player's name in column 29 Cells(itotal + 46, 30).Value = 1 'put 1 (for 1 win) in the next column End If
We now have a list of all the winners with 1's in the next column. Obviously there may be players who have won more than one game, so we sort them alphabetically:
We now scan down our list and, if there are two names the same, we add together the number of wins and delete one of the results. We then have to repeat the sort and check for duplicates until we have no further duplicates:
j = 1 'j used as a "duplicate" flag While j > 0 'repeat this sequence while j>0 (duplicates found) j = 0 ' no duplicates yet For i = 1 To itotal - 1 Aname = Cells(i + 46, 29) 'first name in list Bname = Cells(i + 47, 29) 'second name in list If Aname = Bname Then 'the player has more than 1 win j = j + 1 'so j increased to flag that a duplicate has been found Cells(i + 46, 30) = Cells(i + 46, 30) + Cells(i + 47, 30) 'add the number of wins together Cells(i + 47, 29) = "" 'delete the duplicate name Cells(i + 47, 30) = "" 'delete the second number itotal = itotal - 1 ' 1 fewer names in winner's list End If Next i Range(Cells(47, 29), Cells(46 + itotal + j, 30)).Select 're-sort our list Selection.Sort Key1:=Range("AC47"), Order1:=xlAscending Wend
The final act is to sort by the total number of wins:
The resulting block of names and number of games won can then be copied and pasted to your tournament thread.
The macro can then be accessed by assigning a keypress or using a button on the spreadsheet.
(These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to!)
#6, Issue 63 - Different scoring possibilities for Round Robin Tournaments.
Sensfan is covering Round Robin type tournaments in this edition, where everyone plays each other, perhaps just one game on a neutral map or two games on a home and away map, with points allocated as required. This article adds a few hints at managing these types of tournaments.
The simplest way is to use Challonge or Leaguerepublic sites to do the hard work for you - just update the game results on the sites and the results table is updated automatically - with a handy link that you put in your tournament thread. One hint if you use these sites is that, when you update the results also update your thread in some other way, say round number. Otherwise after a number of rounds, it may be flagged up as an abandoned tournament!
Alternatively, you may want to publish the league table using your own spreadsheet. Refering to the league table that is in Sensfan's article: This has a list of players and their number of wins and draws. Of course, we can't draw a game in CC, so let's assume that only one game is played on a neutral map. The points column of your spreadsheet therefore has the code of "= pointsperwin x number of wins".
If two games are played, one on a home map and the other on the away map, then there is a point for a home or away win plus a bonus point for winning both. The points are therefore 0 for two losses; 1 point for each player for winning one game each and 3 points for winning on both maps. The code for your spreadsheet for the points is therefore:
which gives 1 point for a 1-1 draw and 3 points for a 2-0 result.
Of course, as Sensfan says, the possibilities are endless - limited only by the scope of your imagination! They can get as realistic as you want (depending on how much work you want to put into your tournament!); for instance flexmaster33's "Major League Soccer 2011" viewtopic.php?t=139081has teams of 3: (deleted image) The forward player plays 3 games against the opponents back player and the midfield players play 4 games. The team then plays 1 game to determine the goalie's point! Of course there are home and away maps apart from the team game. Fantastic!
Other Round Robin formats may have points awarded according to other factors. For instance, in terminator games such as trinicardinal's "Trini's Terminator Tournament (TTT)" tournament, the points are recorded according to the termination sequence: viewtopic.php?t=157017.
But there are so many variations on awarding points that you can be as creative as you like! I have tried awarding points equal to 15 minus the round number (so as to to award fast finishes!) and also the CC points that a player gained when winning. All in all, the points are whatever your imagination can come up with - and the game format can be as simple or as complex as you like - proving that you already have planned how you are going to update it - otherwise it can be become quite a burdon to update.
(These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are many different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to!)
#7, Issue 64 - An Excel template for managing single elimination tournaments.
In edition #62, Sensfan gave an overview of the easiest format of a tournament which is a 16 player single elimination tournament i.e.
Click image to enlarge.
Player A plays Player B in game 1 and the winner goes through to game 9, meeting the winner of game 2 between players C and D etc.
In this edition I will give you a few tips on making the management of such a tournament as easy as possible. Obviously you can use Challonge as previously mentioned, though you may want to have your own "tree" image which also gives the game numbers. However, whereas the graphical "tree" format as above gives a very clear illustration of players progress through the tournament, it is not the easiest format to use in managing the various invites etc. For instance the following format is better to use:
Let's consider that you want to link both spreadsheets so that, by updating the table, the graphical representation is automatically updated.
Let's consider the table first. The players' names are copied (or automatically looked-up as I have explained previously) from your randomised player list into the table with the game numbers. When the winning player's name is copied in, then that can automatically be copied into their next game match-up. For instance if A wins game 1, then cell F4 holds his name and cell C12 needs to have that player's name for his game 9 match. Cell C12 can therefore have the text "=F4" in it to automatically insert the name. However, this will insert a "0" if there is no name in F4, so a better formula is "=IF(F4="","",F4)" which inserts a blank instead of "0".
OK so you can complete the table so that the games are all automatically inserted. Of course, you can also do the same for the graphical "tree"! Let's assume you have created this on sheet2 of your spreadsheet. You can then go through this spreadsheet and change the player references to link to the table in sheet1. For instance cell B4 on sheet2 contains "=sheet1!C4" and so on for the players and then the winners. Now you can put in the games numbers - for instance cell B5 on sheet1 contains ="Game "&Sheet1!E4. And so on. Yes it's quite a bit of work, but if you are wanting to run a series of games using the same format, it will be well worth it!
If you want to know how to copy your graphical tree into your tournament thread then you simply take a picture of the tree by pressing your PrtSch (print screen) button; open "Paint" or another similar programme and paste the image into it. You can move the image so that the top left corner is positioned correctly and you need to save it as a ".jpg" image. To crop off the right and bottom irrelevant parts of the image, I use Microsoft Office Picture Manager and then upload it to ImageShack or other free image repository site. (I am sure there must be an easier way, though, if anyone wants to let me know it!)
I have created a template that can be used for single elimination tournaments that can be viewed at the following address: https://docs.google.com/spreadsheet/ccc?key=0AhVfEnhd4J0cdGJDZFZTa0tPTFIyS0pFU0Z1ejZJQmc&hl=en_GB#gid=0 It has 3 sheets; the first to insert the players' names and then sort into a random order; the second is the spreadsheet tracking progress, with invites and progress lists to make it easier to update the tournament thread; the third is the above "tree" that can be used if you want a graphical representation of the progress. Note that these documents do not display exactly as in Excel, so you need to look at each cell to see its entire contents, should they be m,asked by the next column.
(These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are many different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to!)
If you scan through the list of available tournaments, you will find all kinds of inventive game formats. There are the straight elimination tournaments, with 1, 3 or 5 games to find the winner. Obviously the more the number of games played per round, the less the opportunity for lucky deployments and dice to play a part in the result.
In the last editions Sensfan and I looked at single eliminations and round-robin tournaments. In this I look at double elimination tournaments and I'll be giving you a few hints on managing them.
The easiest way of creating a double elimination style tournament is to double the number of entrants; for instance, in a 16 player tournament you enter each opponent twice as though you had 32 players. Once you randomise your player list and assign them to games there is, of course, the possibility that a player will be playing himself, so you need to say that in this circumstance he will go through automatically to the next round - losing one of his "lives"!
You can also have a double elimination tournament which is made easier by using Challonge.com. If you lose one match you then have a second chance of winning. Dazza, though, in one of his tournament series, has made it even more interesting by entering each player twice, so there are 4 chances of winning - hence his "double-double" series title!
Click image to enlarge.
Of course you can always enter each player "n" times into a single elimination type format game (as in deantursx's 1v1 Random Multi-Entry tournament with 4 entries per player). However the advantage with the double or triple elimination format used in Challonge is that, once eliminated, you rejoin at an appropriate position in the tournament rather than at the start.
You don't have to use Challonge though. You can create your own double elimination tournament and manage it using Excel. For instance: The "tree" is the same as in a single elimination tree, where Player1 plays Player2 in Game A etc. However, there is a "second chance" option which is as follows: In this, the losers of games F and E battle it out to play against the loser of game I; and so on.
And, of course, there are various related options that you can try. Butters1919, for instance, has used his own results table in his series of triple knock-out tournaments:
Click image to enlarge.
Doesn't it look lovely! Note the way he used colour coding for the number of lives lost. (As the runner-up from this particular tournament, the downside to this format is that Leintz and I seemed to be playing each other an inordinate number of times!) Sometime, we'll have to find out how he produced the image and any hints on how he managed it.
(These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are many different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to!)
#9, Issue 65 - Design considerations for multiple elimination tournaments.
In the last newsletter, we covered double elimination tournaments and I mentioned the splendid triple elimination tournaments that Butters1919 runs. Well, he has kindly pointed me in the direction of information relating to the scientific design behind such tournaments. Whereas such tournament design may have originated for spectating tournaments, nevertheless many of the principles remain valid for CC.
* There are the obvious ones! In any multiple-elimination set-up, it must not be beneficial to lose a game in order to progress more easily via the loser stream! i.e. are there fewer games via the loser match-up stream?
* You want your tournament to flow so as to avoid any problem where the single player who comes out of the top (winner's) bracket does not play until a single opponent comes out of the bottom (loser's) bracket or brackets. i.e. to try to ensure that no player sits idle for more than one round.
* You want to try and avoid immediate replays and hope to limit how often two players meet up, although in the final stages this may be unavoidable.
* To meet the above, you may have players with different numbers of losses playing each other - and the possibility that a planned game includes a player who might have exceeded the allowed number of tournament losses! To get around this, there will be "if necessary" match-ups.
* You also may want to design multiple elimination tournaments to maintain ranked position match-ups so that the higher ranked players do not meet up until the final rounds.
Well, all these points are easier to consider looking at different types of tournaments that take these issues into consideration - and explain why they may look a bit strange at first! With thanks to Joe Czapski at tournamentdesign.org for his hard work on these and all the other designs available on his website and Butters1919 for his advice!
Let's start with a 4-player double elimination tournament in which the players are ranked from 1 to 4: Game 7 is only necessary if the winner from Game 4 loses in game 6, so has a second chance, as did his opponent. You might want to have this format of game for 4 groups of 4 individuals - the winners going through to a fifth "tree" and gaining a two more elimination "lives"! In this case you would divide the rankings between the 4 initial "trees" so that, in theory, the 4 highest ranked players have the opportunity to get through to the final. (I might even try this one in my next tournament!)
For an example of a triple elimination tournament, many of you will already be familiar with Butters1919 series of tournaments; this is one of his latest ones:
Click image to enlarge.
Note how colour coding is used to clarify the number of eliminations.
An example of a 4-player quad elimination game:
Click image to enlarge.
Get's complex, doesn't it! However, you will see how the layout is designed to try and meet the above set of criteria. Anyone want to design a 16 player quad-elimination tournament??
(These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are many different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to!)
#10, Issue 66 - An Excel macro that totals tournament points.
This issue I'll set up an Excel macro to assign individual points to a "league" table. For simplicity, I'll start off as though the list of individual game winners are in a column on an Excel spreadsheet with the resulting points gained in the next column. I have assumed that the tournament has 16 games of which 10 have been completed. There is a "Sort By Points" button on the spreadsheet which accesses the macro to summarise the points into the table in columns E and F. The first few lines in the macro are to specify a few variables:-
Sub SortNumbers() Dim i, j, itotal As Integer ' itotal will be the total number of players in the table Dim Aname, Bname As String 'Aname and Bname will contain the player's names itotal = 0
The first thing to do is to copy all the players names and their totals into the final table:-
For i = 3 To 18 'the row number range Aname = Cells(i, 2).Value 'Aname will have the value in cell(3,2) If Aname <> 0 Then ' If a player name Cells(3 + itotal, 5).Value = Aname 'put the player name into the table Cells(3 + itotal, 6).Value = Cells(i, 3).Value 'and the score itotal = itotal + 1 'increase the number of players Else i = 18 'if the cell is blank we are at the end of the list and can end End If Next i
The list is copied into the results table:- This can then be sorted by player name as there may be players with more than one entry/win:-
The last process is to scan down the table and, if a duplicate player's name exists, add on the second score to the first and delete the second entry. Then sort to get rid of the blanks and repeat this process until there are no duplicate names:-
j = 1 'assume duplicates exist While j > 0 j = 0 'no duplicates yet For i = 1 To itotal - 1 Aname = Cells(i + 2, 5) '1st player's name Bname = Cells(i + 3, 5) '2nd player's name If Aname = Bname Then 'if they are the same... j = j + 1 'increment duplicate counter Cells(i + 2, 6) = Cells(i + 2, 6) + Cells(i + 3, 6) 'add the scores Cells(i + 3, 5) = "" 'delete the second player's name Cells(i + 3, 6) = "" 'delete the second score itotal = itotal - 1 'reduce total in the table End If Next i Range(Cells(3, 5), Cells(3 + itotal + j, 6)).Select 'sort from the top left cell to the bottom right cell Selection.Sort Key1:=Range("E3"), Order1:=xlAscending 'sort by player's names Wend
When this routine is completed you have just one entry per winning player with their total points score. All you need to do now is to sort by totals and finish the subroutine:-
Range(Cells(3, 5), Cells(3 + itotal, 6)).Select 'specify the area for the sort Selection.Sort Key1:=Range("F3"), Order1:=xlDescending 'specify a descending sort based on the score End Sub
And there you have it; a table that can be copied and pasted straight into your tournament thread to give the latest running order!
OK, so to put this macro into your spreadsheet you need to do the following in your spreadsheet:-
select Tools/Macro/Record New Macro
enter a name for your macro and press OK
press the "stop" on the small macro window
select Tools/Macro/Macros
select your macro and press edit
copy in all the above sets of code into the new window that appears (from Sub to End Sub) and save
select Tools/Customize
scroll down to forms and select "button" and close
click the button that now appears on your menu bar
draw a button shape on your spreadsheet
Click on your macro name to assign it to the button
Yes, it's a tedious process at first when you don't know your way around Excel and macros, but, once done, you can then customise your macro to suit different types of games and scoring systems. Obviously, if your spreadsheet has the lists and summary table in a different part of your spreadsheet, you will need to change the macro appropriately.
(These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are many different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to!)
Last edited by DaveH on Mon Sep 17, 2012 12:41 am, edited 3 times in total.
Once you have your list of sorted players, there is an Excel function that you can use to put your players names into the various game lists. This is the VLOOKUP, or vertical lookup function. This function takes a value from a reference position in a vertical table and puts it into the cell. It is best explained by using an example and the example I will use is a 16 player game where everyone has to play everyone else:= So there will be 15 rounds of 8 games and sorting is not required in this round-robin style tournament. The number in column D is the round number and the numbers in columns E and F are the reference numbers for the matchups; for instance in round 1 player a plays player 9 and in round 2, player 1 plays player 16 etc. OK, it takes a while to type in all the two sets of numbers for the 15 rounds, but it's a lot easier than copying or typing in the names. Now we can use the VLOOKUP function to copy the names from the reference table into the matchup tables. First we will define our table, which will be the list of 16 player's names in column B and their reference number in column A:-
select the list of player's names by dragging down your mouse from A3 to B18
On the Excel menu select Insert/Name/Define
Type in a suitable name - say "Table" (notice that the range of the table is defined correctly as "=SheetName!$A$3:$B$18")
Click OK and you are done
Note that if you wanted to change your tournament for, say, a 20 player round-robin, you simply increase the table bottom value from B18 by 4 to B22. Now we have to copy the VLOOKUP function into the matchup table:-
Click on the first blank cell (G3)
Click the fx button next to the tick and cross
Type V and select VLOOKUP
Into the Vlookup_value box click on E3
Into the Table_array type "Table"
Into the column index number type "2" (so that you select the 2nd column of your table)
Into the Range-lookup type "FALSE" (to exclude near matches)
[/list][/list] You are virtually done! Simply copy down G3 to G10, then copy the whole block across to column H. Then copy the complete block from G3 to H10 into the remaining 14 rounds - done" You can now use the spreadsheet for a new tournament simply by replacing the 16 names in your table with your new set of names!
(These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are many different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to!)
#12, Issue 68 - Designing a 4-player tournament in which each player meets everyone else just once.
Both follow the format of having 16 players with 4 players playing Terminator and everyone playing their 15 opponents only once. I tried a similar tournament of 4 player Assassin, each player playing their 15 opponents only once, and found that it was not at all easy to form unique groupings.
Groups of 3 are easy: Round 1 ABC, DEF, GHI; Round 2 ADG, BEH, CFI; Round 3 AEI, BFG, CDH nd Round 4 CEG, BDI, AFH. This is easier to see if you have a matrix: The grouping are across; down, diagonally right and diagonally left.
With groups of 4 the same method works - but not quite in the same way. I worked out that with 5 sets of 4 you would meet everyone once, but the combinations of players would not work. I eventually resorted to the web (thanks to mathtalk-ga!) and found that this is also a scheduling method used for golf foursomes and dinner parties!
You start with the 4 x 4 matrix and can start with two rounds across and down the matrix i.e. That gives Round 1; ABCD, EFGH, IJKL and MNOP: Round 2; AEIM, BFJN, CGKO and DHLP.
But diagonals don't work, and you have to look at the following two symmetrical patterns:
This gives two of the remaining groups that A plays in: AFLO and AHKN. But what about the other three matches in these two groups?
This is the clever bit (that I would never have thought of!). You take the same pattern but start at the other three corners! i.e. MJHC and MLGB from the "M" corner; PKEB and PIFC from the "P" corner and DGIN and DEJO from the "D" corner.
The final four games (Round 5) are based around the following two patterns: This gives AGJP and BHIO, and their vertical reflections of MKFD and CELN (which are the only other remaining possibilities anyway!).
Which leaves us with the possibility that Chapcrap is really the mathmatical genius "mathtalk-ga" and perhaps he even knows how to uniquely schedule 5 player matches?
Summary of Player Combinations A B C D E F G H I J K L M N O P
A E I M B F J N C G K O D H L P
A F L O M J C H P K B E D G I N
A H K N M B G L P C F I D E J O
A G J P D F K M B H I O C E L N
If anyone out there does know how to uniquely schedule 5 and more player games, please let me know!
(These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to!)
#13, Issue 69 - Design of a 16 player pair tournament in which you pair everyone else just once.
In it he lists various combinations of pairs such that, if you look at the tournament layout for 16 players:
* Each player partners each of the others exactly once. * Each player opposes each of the others exactly twice. * No set of three players meet together more than once.
So this would make an ideal tournament for 16 players, partnering each other once over 15 games and meeting everyone else just twice. In this article, I'll describe how you can set up an Excel sheet to make the matchups easier.
(Please note that Mr Wiseman is indeed very, very wise. His explanation of the workings of his table are "This design is constructed in an unusual way, specific to powers of 2: index the players by the elements of a four-dimensional vector space V over the field of two elements, and the rounds by the non-zero elements of that vector space. Find a bijection f:V→V such that the function x→x+f(x) is also a bijection; then, in round x, player y partners y+x against f(y) and f(y)+x. )
Anyway, on with more mundane matters...
Firstly enter a table of the 16 alpha characters with the player's names next to them. Enter a second table of the 15 rounds, 4 games and pair matchups:
Click image to enlarge.
The first table you make into the lookup table (as described in an earlier article) called, say "Table". You can then design a new table, perhaps on a separate sheet, that has the pair matchups in a friendlier format. i.e. The players names are "looked-up" from Table using a formula as follows; (Round 1, Game 1, Player i) name is picked up from Table using the formula:
i.e. the content of the sheet called "Players", cell F5 (which has the first letter "D" in it) is looked up in "Table" and column 2 containing "Player 4" is copied into the cell. The "FALSE" item in the function is to ensure accurate copies are made.
You then copy this code across to the other three columns to automatically insert the correct names. To do the other columns for Player i you need to repeat the same procedure, pointing at the correct position in the table - OK a bit of work, but once done, the same table can be used for any number of tournaments - just change the 16 players names and the game matchups are automatically inserted in their correct positions.
Since this is a doubles tournament, the layout of the invites is slightly different from the singles format, so I won't tackle it in this article, but perhaps the next.
So, back to the possible tournament; "Partnering Your Opponents". Thanks to Julian's information, we have 15 rounds of games partnering everyone once and against everyone twice, so it's a balanced tournament and we only need to work out the points scoring and map details. Points based on number of wins (max 15) might result in many ties and it would have to be a neutral map if only one game is played in each round. So, we could have every player choosing their own map and play 4 games per round, giving us the possibility of a maximum score of 60, with fewer chances of ties.
Ideal, except that I have yet to find an easy way of starting games with every player having a different map and setting. Any hints out there???
(These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are many different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to!)
#14, Issue 70 - Excel code for inviting players into team games.
In issue #60 I suggested an Excel spreadsheet for automatically creating the TO tag as follows: Column B is the randomised player list and column C the RAND(0) function which was used to randomise the player list (and has changed its values once again!). The password is in D2.
The code for the second players has a 2 in place of the 1 to designate Team 2.
To invite the players you then copy across the two Team 1 players and paste into the address box (remembering to put a carriage return after the first player's name bedore uploading the names). You then copy the corresponding code in column T as part of the message - remember also that you can include an automated message as suggested in issue #60 by modifying the code slightly. The same process applies to all the other sets of players.
If your tournament has 3, 4 or more players per team, simply insert additional rows and copy down the code, changing the team numbers as required.
Next time I'll suggest a few ways of keeping track of team games which may have various types of results scoring.
(These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to!)
#15, Issue 71 - Excel code for updating team games.
In Issue #61 I showed you a simple way in Excel of updating a 1 vs 1 tournament game:
Click image to enlarge.
Columns B and C are the lists of competing players and column D the game number. The next column is blank until the game is finished, then the winning player's name is copied in. The code in column E is:
="[game]"&D3&"[/game] "&IF(E3="",B3&" vs "&C3,IF(E3=B3,B3&" vs [s]"&C3&"[/s]","[s]"&B3&"[/s] vs "&C3))
For those of you who want to understand the Excel functions, after the text to insert the game number there is a conditional statement; if the winner cell is blank, then put in the two players' names , else if the winner text is PlayerA, include the "s" tag for PlayerB, otherwise include the "s" tag for Player A. The "s" tag puts lines through the loser's name. When the text in column E is copied to your tournament thread in the appropriate place, you get the following: Game 1234567 Player4 vs Player2 Game 1234566 Player5 vs Player1 Game 1234565Player3 vs Player7
When you have teams playing then there are various options. You could simply refer to "Team 1" etc in which case the coding would be the same as for singles tournaments. You might always ask the teams themselves to provide a team name, which is more personable!
Another alternative that works well in doubles games where you have fixed pairings is to name a team as "DaveH & Will Lee" for instance. Therefore, once again you can use the single player Excel codings. For trebles and quads it can get a bit lengthy, but, once copied, the coding will take care of names no matter how long!
Alternatively, if you want to have the individual players names (as in a tournament where pairings change), then the coding is only a little different. For instance, let us look at a results format for a doubles single game elimination tournament.
Click image to enlarge.
Note that I have indicated the winning team simply by entering 1 or 2 rather than one or both player's names.
="[game]"&F3&"[/game] "&IF(G3="",B3&", "&C3&" vs "&D3&", "&E3,IF(G3=1,B3&", "&C3&" vs [s]"&D3&", "&E3&"[/s]","[s]"&B3&", "&C3&"[/s] vs "&D3&", "&E3))
which appears in you thread as: Game 1234567 Player 1, Player 2 vs Player 3, Player 4 Game 1234566 Player 5, Player 6 vs Player 7, Player 8 Game 1234565Player 9, Player 10 vs Player 11, Player 12
Of course there are many, many different formats for tournaments: some may have the best of 3 or 5 games; others perhaps terminator games in which the elimination order is used in the results. We will look at these and others in future editions.
(These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to! Please note that I use Excel 2003, so there may be variation in the menu instructions if you have Excel 2007)
#16, Issue 72 - Using Excel to get a list of all of CC's maps.
Many tournament organisers want to use all of CC's maps, so in this issue I'll show you how to create a full list of CC maps in Excel. Actually there are two ways of copying the full list of 209 maps- the first is that you can type them all in and the second is the one that I'll explain below - as it is a little easier!
There is a list of CC maps in the Completed Map section of the Forum:
You can copy and paste this list into Excel. Unfortunately they have additional information appended to them, so I'll show you an easy way of extracting the map name. Copy the list - but do not copy the map number, which would make the extraction a little more complex. To avoid including the numbers, copy across in two blocks, 1-99 and 100-209 (or whatever the last one is when you do this) and highlight just the maps.
When copied, the data starts off as follows:
Classic (2006-01-03 04:20:04) - Classic Shapes | Classic Art - Classic (Wed Dec 30, 2009 8:48 pm) Asia (Far East) (2006-01-03 12:54:45) - Far East (2012-02-07 11:34) USA (2006-01-04 06:00:48) Middle East (2006-03-14 19:32:33) - Middle East (Mon May 21, 2007 2:04 am) Canada (2006-03-23 15:05:47) - Canada (Sun Oct 28, 2007 12:58 am) Europe (2006-03-23 15:26:19)
Firstly you should remove the hyperlinks that may keep taking you back to your browser. In a blank cell enter 1 and copy the cell. Select the column with the map list in it and select Edit/Paste Special and select operation = Multiply. Click OK and you are done. Now to extract the names.
Excel has a useful function called "Find" which finds the position in text of a character or characters. We need to find the position of the bracket, "(". We are therefore going to copy the Left most characters up to 2 characters before the "(". This code is:
(assuming that the map list is in column B starting at B3)
Copy this code down alongside the map list and all the map names will be extracted apart from a few marked "#VALUE!" for which the date has been written in the next line due to a marker character in the text. For these just copy a space and bracket next to the map name " (" and delete the next line with the date information in it. If you come across and other small marker characters ("?") just select and delete.
There are some "- Limited Edition" maps such as the Christmas maps that you may want to edit to exclude the limited edition title and a few maps that have later editions in which their names were changed. There is a way of using Excel Visual Basic coding to find out if there is a second or third edition of any maps, but it is complex and better is to check down the list and change the names manually - should you want to. The names are not that different anyway, as far as I have observed.
Now we have a full set of maps with a few that may have earlier names than the ones that are now in play. These map names in formula format, so you want to select and copy this list and Edit/Paste Special selecting Paste=Values to get them in text format. You can then sort as you wish.
(These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to! Please note that I use Excel 2003, so there may be variation in the menu instructions if you have Excel 2007)
#17, Issue 73 - Excel code to update tournament winners from a list.
In the last newsletter I showed you how to extract a list of maps that many TO's use in progressing their series of games through every single CC map. Greenoaks reminded me that some TO's also like to keep track of their winners and easily update the number of wins by different players. In this issue therefore, I'll show you the code for updating a tournament winners table.
For simplicity, I'll start off as though the list of individual game winners are in a column on an Excel spreadsheet next to the tournament they won. The first few lines in the macro are to specify a few variables:-
Sub SortWins() Dim i, j, itotal As Integer ' itotal will be the total number in the table Dim Aname, Bname As String 'Aname and Bname will contain the player's names itotal = 0
The first thing to do is to put a "1" next to the players' names (as they have each won one tournament).
' put 1 in the next column and find out how many are listed For i = 3 To 100 'the start row number is 3; 100 assumed to be larger than the last row Aname = Cells(i, 2).Value 'Aname will have the value in cell(3,2) If Aname <> "" Then ' If a player name Cells(i, 3).Value = 1 'put 1 next to the player's name itotal = itotal + 1 'increase the number in the list Else i = 100 'if the cell is blank we are at the end of the list and can end End If Next i
The last process is to scan down the table and, if a duplicate player's name exists, add on the second total to the first and delete the second entry. The tournament name is also added:-
Range(Cells(3, 1), Cells(3 + itotal + j, 3)).Select 'sort from the top left cell to the bottom right cell Selection.Sort Key1:=Range("B3"), Order1:=xlAscending 'sort by player's names
This process is then repeated until you have no more duplicates:-
You now have just one entry per winning player with their total number of wins. All you need to do now is to sort by totals and finish the subroutine:-
Range(Cells(3, 1), Cells(3 + itotal, 3)).Select 'specify the area for the sort Selection.Sort Key1:=Range("C3"), Order1:=xlDescending 'specify a descending sort based on the score End Sub
And there you have it; a table of winner's of your tournament winners in the order of number of wins.
OK it may look better if the tournaments/maps won were in the right column and aligned to the left, but that can be done by tweeking the above code. The total code is listed here:-
Sub SortWins() Dim i, j, itotal As Integer ' itotal will be the total number in the table Dim Aname, Bname As String 'Aname and Bname will contain the player's names itotal = 0 ' put 1 in the next column and find out how many are listed For i = 3 To 100 'the start row number is 3; 100 assumed to be larger than the last row Aname = Cells(i, 2).Value 'Aname will have the value in cell(3,2) If Aname <> "" Then ' If a player name Cells(i, 3).Value = 1 'put 1 next to the player's name itotal = itotal + 1 'increase the number in the list Else i = 100 'if the cell is blank we are at the end of the list and can end End If Next i ' sort by winners name Range(Cells(3, 1), Cells(2 + itotal, 3)).Select Selection.Sort Key1:=Range("B3"), Order1:=xlAscending j = 1 'assume duplicates exist While j > 0 j = 0 'no duplicates yet For i = 1 To itotal - 1 Aname = Cells(i + 2, 2) '1st player's name Bname = Cells(i + 3, 2) '2nd player's name If Aname = Bname Then 'if they are the same... j = j + 1 'increment duplicate counter Cells(i + 2, 3) = Cells(i + 2, 3) + Cells(i + 3, 3) 'add the scores Cells(i + 3, 2) = "" 'delete the second player's name Cells(i + 3, 3) = "" 'delete the second score Cells(i + 2, 1) = Cells(i + 2, 1) & ", " & Cells(i + 3, 1) Cells(i + 3, 1) = "" itotal = itotal - 1 'reduce total in the table End If Next i Range(Cells(3, 1), Cells(3 + itotal + j, 3)).Select 'sort from the top left cell to the bottom right cell Selection.Sort Key1:=Range("B3"), Order1:=xlAscending 'sort by player's names Wend Range(Cells(3, 1), Cells(3 + itotal, 3)).Select 'specify the area for the sort Selection.Sort Key1:=Range("C3"), Order1:=xlDescending 'specify a descending sort based on the score End Sub
If you can't remember from previous articles how to put this macro into a button on your spreadsheet you need to do the following in your spreadsheet:-
select Tools/Customize
scroll down to forms and select "button" and close
click the button that now appears on your menu bar
draw a button shape on your spreadsheet
Click on your macro name to assign it to the button
Done!
(These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are many different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to!)
#18, Issue 74 - Setting up an additional Excel menu
In previous articles I have shown how macros can be used to automate tournament results updating. These macros were accessed by a button, but they can also be accessed from a new Excel menu and in this article I'll show how you can customise your menu task bar to include sub-menus that run different macros.
The coding looks rather complex, so the best way of getting to know what is happening is to install a new menu bar and then you can more easily see what is happening. OK lets start Excel and carry out the following sequence:
* Create a new spreadsheet; * Press Alt+F11 from Excel which opens the Visual Basic Editor window; * In the top left pane you get "VBAProject (Book1)"; * Open the menu and sub-menu by clicking the [+] buttons; * Click "ThisWorkbook" to open a new window on the right; * Copy the following code and paste into the blank window;
Private Sub Workbook_Open() Dim cmbBar As CommandBar Dim cmbControl As CommandBarControl
Set cmbBar = Application.CommandBars("Worksheet Menu Bar") Set cmbControl = cmbBar.Controls.Add(Type:=msoControlPopup, temporary:=True) 'adds a menu item to the Menu Bar With cmbControl .Caption = "&My Macros" 'names the menu item With .Controls.Add(Type:=msoControlButton) 'adds a dropdown button to the menu item .Caption = "My Macro No 1" 'adds a description to the menu item .OnAction = "RunMyMacro1" 'runs the specified macro .FaceId = 1098 'assigns an icon to the dropdown End With With .Controls.Add(Type:=msoControlButton) .Caption = "My Macro No 2" .OnAction = "RunMyMacro2" .FaceId = 108 End With With .Controls.Add(Type:=msoControlButton) .Caption = "My Macro No 3" .OnAction = "RunMyMacro3" .FaceId = 21 End With End With End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error Resume Next 'in case the menu item has already been deleted Application.CommandBars("Worksheet Menu Bar").Controls("My Macros").Delete 'delete the menu item End Sub
* On the "Run" menu, click on "Run Sub./UserForm" (or press F5); * On the Spreadsheet a new menu heading appears "My Macros"; * Clicking this opens up the menu to reveal three new sub-menus, each with pictures next to them.
OK, so let's look into the coding:-
.Caption = "&My Macros" ; Your main menu title can be inserted here; The sub menus are defined by the following set of code lines; With .Controls.Add(Type:=msoControlButton); This "With/End With" set of codes adds a new sub-menu; .Caption = "My Macro No 1"; Sub-menu title .OnAction = "RunMyMacro1"; Assign the macro name here; .FaceId = 1098; Adds a nice little picture (try changing the number!) End With
This set of 5 code lines are repeated as many times as you need sub-menus.
If you want to see more pictures - there are hundreds! - you can download a browser to look through these icons at http://skp.mvps.org/faceid.htm. Have fun!
(These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to! Please note that I use Excel 2003, so there may be variation in the menu instructions if you have Excel 2007)
#19, Issue 75 - Excel code that creates a tournament spreadsheet template - Part 1.
In the last newsletter I gave the code for creating a new menu. This created an additional menu called "My Macros" with three headings under it to run "My Macro No's 1 to 3". In this article we will start to define the coding you need to set up a template that you can use for managing a tournament.
Initially we will extend the code we had in the last newsletter to define the code needed to put a new menu bar into the Excel spreadsheet menu bar. This will have a "Number of Players" selection so that you can select 8, 16, 32 or 64 players. You can then select "Type" which will be singles or doubles, and then a "Create a Template" selection to create a new spreadsheet set out ready for you to fill in and start your tournament!
_____________
In this article we have the coding for a routine called "CreateMenu" which you can run to insert the new menu heading. The macros that can be called from the menu will be defined in future articles (explanations to the coding are included in the lines of code);
Sub CreateMenu() Dim cMenu1 As CommandBarControl 'Dimension as a command bar control Dim cbcCutomMenu As CommandBarControl Dim cbMainMenuBar As CommandBar 'Dimension as a command bar Dim iHelpMenu As Integer
Dim PlayerNumber As Integer Dim GameType As String
PlayerNumber = 16 GameType = "Singles"
'Delete the existing "Tournament" menu heading On Error Resume Next 'In case it does not exist! Application.CommandBars("Worksheet Menu Bar").Controls("Tournament").Delete On Error GoTo 0
'Set a CommandBar variable to Worksheet menu bar Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar")
'Return the Index number of the Help menu. We can then use 'this to place a custom menu before. iHelpMenu = cbMainMenuBar.Controls("Help").Index
'Add a Control to the "Worksheet Menu Bar" before Help. 'Set a CommandBarControl variable to it Set cbcCutomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup, before:=iHelpMenu)
'Give the control our caption as "Tournament" 'The ampersand is before the underligned shortcut letter cbcCutomMenu.Caption = "Tourna&ment"
'Add the first menu item that will lead off to another menu 'Set a CommandBarControl variable to it With cbcCutomMenu.Controls.Add(Type:=msoControlPopup) ' Give the control a caption .Caption = "Number of Players"
'Working with our new Control, add a sub control and 'give it a Caption and tell it which macro to run (OnAction). With .Controls.Add(Type:=msoControlButton) .Caption = "8" '.OnAction = "Players8" 'not yet defined If PlayerNumber = 8 Then 'Tick if 8 players .State = msoButtonDown Else .State = msoButtonUp End If End With
' Repeat for the 16, 32 and 64 player menu headings With .Controls.Add(Type:=msoControlButton) .Caption = "16" '.OnAction = "Players16" 'not yet defined If PlayerNumber = 16 Then 'Tick if 16 players .State = msoButtonDown Else .State = msoButtonUp End If End With
With .Controls.Add(Type:=msoControlButton) .Caption = "32" '.OnAction = "Players32" 'not yet defined If PlayerNumber = 32 Then 'Tick if 32 players .State = msoButtonDown Else .State = msoButtonUp End If End With
With .Controls.Add(Type:=msoControlButton) .Caption = "64" '.OnAction = "Players64" 'not yet defined If PlayerNumber = 64 Then 'Tick if 64 players .State = msoButtonDown Else .State = msoButtonUp End If End With
End With
'Add the second menu item that will lead off to another menu 'Set a CommandBarControl variable to it
With cbcCutomMenu.Controls.Add(Type:=msoControlPopup) ' Give the control a caption .Caption = "Game Type"
With .Controls.Add(Type:=msoControlButton) .Caption = "Singles" '.OnAction = "PlayersSingles" 'not yet defined If GameType = "Singles" Then 'Tick if 64 players .State = msoButtonDown Else .State = msoButtonUp End If End With
With .Controls.Add(Type:=msoControlButton) .Caption = "Doubles" '.OnAction = "PlayersDoubles" 'not yet defined If GameType = "Doubles" Then 'Tick if 64 players .State = msoButtonDown Else .State = msoButtonUp End If End With
End With
With cbcCutomMenu.Controls.Add(Type:=msoControlButton) ' Give the control a caption .Caption = "Create Template" ' .OnAction = "FormatCode" 'not yet defined .FaceId = 625 'assigns an icon to the dropdown
End With
End Sub
Sub DeleteMenu() ' Use this if you get more than one "Tournament" menu item! On Error Resume Next Application.CommandBars("Worksheet Menu Bar").Controls("Tournament").Delete On Error GoTo 0 End Sub
A few notes on the above code:
* The second subroutine "DeleteMenu" can be used to delete the "Tournament" menu heading should you be experimenting with the code and get more than one heading; * The "If, Else, End If" loops are to put the tick selection next to the correct submenu item; * The icon was chosen looking at a full set of icons listed on the following site: http://www.outlookexchange.com/articles/toddwalker/BuiltInOLKIcons.asp:
The instructions for copying this set of codes into Excel are copied from the last article as follows:
* Open Excel to create a new spreadsheet; * Press Alt+F11 from Excel which opens the Visual Basic Editor window; * In the top left pane you get "VBAProject (Book1)"; * Open the menu and sub-menu by clicking the [+] buttons; * Click "ThisWorkbook" to open a new window on the right; * Copy the above code and paste into the blank window; * To run the code either select Run from the menu or press F5
In the next article we will look at the macros used to change the "ticked" selections.
(These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to! Please note that I use Excel 2003, so there may be variation in the menu instructions if you have Excel 2007)
#20, Issue 76 - Excel code that creates a tournament spreadsheet template - Part 2.
In the last newsletter I showed you the code for putting a new menu into an Excel sheet. The appearance is as follows:
_____________
In this article we will put in the coding that changes the settings according to which "Player Number" and "Type" you choose. We also need to write these settings to the Excel sheet.
The total code is later in this article; firstly I'll go through the additions to last week's code. We check if our spreadsheet has any information in it; if not we write the default settings of "16 Players" and "Singles".
' Check that there is game information on the spreadsheet ' If not, put some default information in Cells.Select Selection.Font.Bold = False If Cells(3, 2).Value = "" Then Cells(2, 2).Value = "Tournament Settings:" Cells(3, 2).Value = "16" Cells(3, 3).Value = "Players" End If If Cells(4, 2).Value = "" Then Cells(4, 2).Value = "Singles" End If Cells(2, 2).Select Selection.Font.Bold = True
This writes the following to your spreadsheet:
There are then 4 routines to select either 8,16,32 or 64 players and 2 routines to select either "Singles" or "Doubles". Each of the sets of coding is similar i.e.
Sub TypeSingles() Cells(4, 2).Value = "Singles" 'Change the cell at (row 4, column 2) to "Singles CreateMenu 'run the main menu subroutine to put the "tick" in the correct place! End Sub
Sub CreateMenu() Dim cMenu1 As CommandBarControl Dim cbcCutomMenu As CommandBarControl Dim cbMainMenuBar As CommandBar Dim iHelpMenu As Integer Dim cbcSubMenu As CommandBarControl
Dim PlayerNumber As Integer '8, 16, 32 or 64 etc Dim GameType As String 'Singles or doubles etc Dim ColStart As Integer 'starting column number Dim Rowstart As Integer 'starting row number
' Check that there is game information on the spreadsheet ' If not, put some default infrmation in Cells.Select Selection.Font.Bold = False If Cells(3, 2).Value = "" Then Cells(2, 2).Value = "Tournament Settings:" Cells(3, 2).Value = "16" Cells(3, 3).Value = "Players" End If If Cells(4, 2).Value = "" Then Cells(4, 2).Value = "Singles" End If Cells(2, 2).Select Selection.Font.Bold = True
On Error Resume Next Application.CommandBars("Worksheet Menu Bar").Controls("Tournament").Delete On Error GoTo 0 Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar") iHelpMenu = cbMainMenuBar.Controls("Help").Index Set cbcCutomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup, before:=iHelpMenu) cbcCutomMenu.Caption = "Tourna&ment"
Set cbcSubMenu = cbcCutomMenu.Controls.Add(Type:=msoControlPopup) cbcSubMenu.Caption = "Number of Players"
' Set the '8' player menu heading With cbcSubMenu.Controls.Add(Type:=msoControlButton) .Caption = "8" .OnAction = "Players8" If Cells(3, 2).Value = "8" Then .State = msoButtonDown Else .State = msoButtonUp End If End With
' Repeat for the 16, 32 and 64 player menu headings With cbcSubMenu.Controls.Add(Type:=msoControlButton) .Caption = "16" .OnAction = "Players16" If Cells(3, 2).Value = "16" Then .State = msoButtonDown Else .State = msoButtonUp End If End With
With cbcSubMenu.Controls.Add(Type:=msoControlButton) .Caption = "32" .OnAction = "Players32" If Cells(3, 2).Value = "32" Then .State = msoButtonDown Else .State = msoButtonUp End If End With
With cbcSubMenu.Controls.Add(Type:=msoControlButton) .Caption = "64" .OnAction = "Players64" If Cells(3, 2).Value = "64" Then .State = msoButtonDown Else .State = msoButtonUp End If End With 'Add the second menu item that will lead off to another menu 'Set a CommandBarControl variable to it Set cbcSubMenu = cbcCutomMenu.Controls.Add(Type:=msoControlPopup) ' Give the control a caption cbcSubMenu.Caption = "Game Type"
With cbcSubMenu.Controls.Add(Type:=msoControlButton) .Caption = "Singles" .OnAction = "TypeSingles" If Cells(4, 2).Value = "Singles" Then .State = msoButtonDown Else .State = msoButtonUp End If End With
With cbcSubMenu.Controls.Add(Type:=msoControlButton) .Caption = "Doubles" .OnAction = "TypeDoubles" If Cells(4, 2).Value = "Doubles" Then .State = msoButtonDown Else .State = msoButtonUp End If End With
Set cbcSubMenu = cbcCutomMenu.Controls.Add(Type:=msoControlButton) ' Give the control a caption cbcSubMenu.Caption = "Create Template" cbcSubMenu.OnAction = "FormatCode" 'Main programme cbcSubMenu.FaceId = 625 'assigns an icon to the dropdown
End Sub Sub DeleteMenu() ' Use this if you get more than one "Tournament" menu item! On Error Resume Next Application.CommandBars("Worksheet Menu Bar").Controls("Tournament").Delete On Error GoTo 0 End Sub Sub Players8() Cells(3, 2).Value = "8" CreateMenu End Sub Sub Players16() Cells(3, 2).Value = "16" CreateMenu End Sub Sub Players32() Cells(3, 2).Value = "32" CreateMenu End Sub Sub Players64() Cells(3, 2).Value = "64" CreateMenu End Sub Sub TypeSingles() Cells(4, 2).Value = "Singles" CreateMenu End Sub Sub TypeDoubles() Cells(4, 2).Value = "Doubles" CreateMenu End Sub
As before copy and paste this code to your Visual Basic code panel and run it. In case you have not seen the way of doing this, I shall repeat it as follows:
* Open Excel to create a new spreadsheet; * Press Alt+F11 from Excel which opens the Visual Basic Editor window; * In the top left pane you get "VBAProject (Book1)"; * Open the menu and sub-menu by clicking the [+] buttons; * Click "ThisWorkbook" to open a new window on the right; * Copy the above code and paste into the blank window; * To run the code either select Run from the menu or press F5
In the next article we will go though some of the coding needed for the "Create Template" main programme.
(These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to! Please note that I use Excel 2003, so there may be variation in the menu instructions if you have Excel 2007)
As we continue the coding for setting up a tournament spreadsheet, in this article we will start the "Create Template" menu heading coding.
We will start off simply enough, defining a grid where the singles or doubles players will have their names inserted. The size depends on the 8, 16, 32 or 64 player selection and whether the names are listed in one or two columns according to the singles/doubles selection. For instance a doubles tournament with 16 players or an 8 player singles tournament will appear as follows:
Selecting the "Create Template" menu item, as coded in the last two articles, runs the "FormatCode" macro. This is started off as follows:
Sub FormatCode() Dim irow As Integer Dim icol As Integer Dim aplayer As String irow = 7 'starting row position icol = 3 'starting column position
i = Cells(3, 2).Value 'Number of players aplayer = Cells(4, 2).Value 'Type of game k = 0 'Flag = 0 for Singles) If aplayer = "Doubles" Then k = 1 'Flag = 1 for Doubles)
The irow, icol matrix position on the spreadsheet is defined as the starting position of the grid. "i" records the number of players in cell position (3,2) - as defined by the other menu selections in the last 2 articles and "aplayer" whether this is a singles or doubles tournament. If it is a doubles tournament "k" is set to 1 from 0.
For j = 0 To k 'One or two player rows Cells(irow, icol + j).Select With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous 'Top border End With
The "For/Next" loop runs for 1 loop for singles or 2 loops for doubles as defined by the flag "k". the next "With/End With" loop is the rather long-winded method used to format a cell. Firstly the cell is defined, "Cells(irow, icol + j).Select" then the loop is used to define the type of formatting used; in this first case an border line at the top of the defined cell.
Cells(irow, icol + j).Offset(i / (k + 1), 0).Select With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous 'Bottom border End With
Now a bottom edge - note the offset addition is coded so that if it is a doubles tournament the lower line is half the number of players because there will be two columns for doubles tournaments.
Cells(irow, icol + j).Select With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous Selection.Font.Bold = True If aplayer = "Singles" Then ActiveCell.Value = "Players" Else If j = 0 Then ActiveCell.Value = "Player A" If j = 1 Then ActiveCell.Value = "Player B" End If End With
Next we put a lower line after the heading and include the heading as either "Players" or, for doubles, "Player A" with "Player B" in the next cell.
For l = 0 To i / (k + 1) Cells(irow, icol + j).Offset(l, 0).Select If l > 0 And j = 0 Then Cells(irow, icol + j).Offset(l, -1).Value = l Cells(irow, icol + j).Offset(l, -1).HorizontalAlignment = xlRight With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous 'Left border End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous 'Right border End With Next l Next j End Sub
The last part of the routine is to define the edges of the grid and number the players, or doubles team numbers, down the left hand edge. The codes used are as follows: "i/(k+1)" is the number of players divided by 1 (for singles) or 2 (for doubles) to define the number of rows required. "If l > 0 And j = 0 Then Cells(irow, icol + j).Offset(l, -1).Value = l" states that if the counter is not 0 (i.e. not at the heading position) and it is the first pass through the two loop cycle then put "l" which is the player/team number in the defined cell, offset by -1 into the column before the defined cell.
Sub FormatCode() Dim irow As Integer Dim icol As Integer Dim aplayer As String irow = 7 'starting row position icol = 3 'starting column position
i = Cells(3, 2).Value 'Number of players aplayer = Cells(4, 2).Value 'Type of game k = 0 'Flag = 0 for Singles) If aplayer = "Doubles" Then k = 1 'Flag = 1 for Doubles) For j = 0 To k 'One or two player rows Cells(irow, icol + j).Select With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous 'Top border End With Cells(irow, icol + j).Offset(i / (k + 1), 0).Select With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous 'Bottom border End With Cells(irow, icol + j).Select With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous Selection.Font.Bold = True If aplayer = "Singles" Then ActiveCell.Value = "Players" Else If j = 0 Then ActiveCell.Value = "Player A" If j = 1 Then ActiveCell.Value = "Player B" End If End With For l = 0 To i / (k + 1) Cells(irow, icol + j).Offset(l, 0).Select If l > 0 And j = 0 Then Cells(irow, icol + j).Offset(l, -1).Value = l Cells(irow, icol + j).Offset(l, -1).HorizontalAlignment = xlRight With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous 'Left border End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous 'Right border End With Next l Next j End Sub
Next article we will see about randomising the players names.
(These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to! Please note that I use Excel 2003, so there may be variation in the menu instructions if you have Excel 2007)
#22, Issue 78 - Excel code that creates a tournament spreadsheet template - Part 4; Randomising the player list.
As we continue the coding for setting up a tournament spreadsheet, in this article we will define the coding for randomising the list of players.
Firstly we add a "Randomise" menu heading to our "Tournament" Excel menu bar that will run the code in the subroutine "RandomCode". The extra code for this, which is added to the subroutine "CreateMenu()", is as follows:
' Add a menu heading for randomising the list of players Set cbcSubMenu = cbcCutomMenu.Controls.Add(Type:=msoControlButton) ' Give the control a caption cbcSubMenu.Caption = "Randomise" cbcSubMenu.OnAction = "RandomCode" 'Randomise programme cbcSubMenu.FaceId = 1376 'assigns an icon
This adds to the existing menu, so that you get the following:
Sub RandomCode() Dim irow As Integer 'starting row Dim icol As Integer 'starting column
irow = 7 'starting row position icol = 3 'starting column position
'check that the table exists A = Cells(irow, icol).Value 'First Heading If A = "" Then MsgBox "You need to select 'Create Template' first." Exit Sub Else
'check that the table is full i = Cells(3, 2).Value 'Number of players k = 0 If Cells(4, 2).Value = "Doubles" Then i = i / 2 k = 1 End If j = 1 While j < i + 1 If Cells(irow + j, icol).Value = "" Then MsgBox "The player list is not complete" Exit Sub Else j = j + 1 End If Wend
'check that the list is not already random If Cells(5, 2).Value = "Randomised" Then MsgBox "The player list has already been randomised" Exit Sub Else
'copy in a random number For i = 1 To j - 1 Cells(irow + j + i + 1, icol + k + 1).Value = "=RAND()" Next i
'sort and clear the random number Range(Cells(irow + j + 2, icol), Cells(irow + j + i, icol + k + 1)).Select Selection.Sort Key1:=Cells(irow + j + 2, icol + k + 1), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range(Cells(irow + j + 2, icol + k + 1), Cells(irow + j + i, icol + k + 1)).Clear
'Show that the list is now random With Cells(irow + j + 1, icol - 1) .Value = "Random" .HorizontalAlignment = xlRight .Font.Bold = True End With With Cells(5, 2) .Value = "Randomised" .HorizontalAlignment = xlRight .Font.Bold = False End With End If End If End Sub
There are a number of checks to make before randomising the list of players' names. Does the table exist?
'check that the table is full i = Cells(3, 2).Value 'Number of players k = 0 If Cells(4, 2).Value = "Doubles" Then i = i / 2 k = 1 End If j = 1 While j < i + 1 If Cells(irow + j, icol).Value = "" Then MsgBox "The player list is not complete" Exit Sub Else j = j + 1 End If Wend
'Show that the list is now random With Cells(irow + j + 1, icol - 1) .Value = "Random" .HorizontalAlignment = xlRight .Font.Bold = True End With With Cells(5, 2) .Value = "Randomised" .HorizontalAlignment = xlRight .Font.Bold = False End With
The table now looks like the following (depending on your settings!):
All the code for all subroutines is as follows. (Those of you follow the coding closely may spot that some parts of my previous publications have been simplified. That's because I too am still learning!)
Sub CreateMenu() Dim cMenu1 As CommandBarControl Dim cbcCutomMenu As CommandBarControl Dim cbMainMenuBar As CommandBar Dim iHelpMenu As Integer Dim cbcSubMenu As CommandBarControl
Dim PlayerNumber As Integer '8, 16, 32 or 64 etc Dim GameType As String 'Singles or doubles etc Dim ColStart As Integer 'starting column number Dim Rowstart As Integer 'starting row number
' Put some default information in Cells.Select Selection.Font.Bold = False If Cells(3, 2).Value = "" Then Cells(2, 2).Value = "Tournament Settings:" Cells(3, 2).Value = "16" Cells(3, 3).Value = "Players" End If If Cells(4, 2).Value = "" Then Cells(4, 2).Value = "Singles" End If Cells(2, 2).Select Selection.Font.Bold = True
On Error Resume Next Application.CommandBars("Worksheet Menu Bar").Controls("Tournament").Delete On Error GoTo 0 Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar") iHelpMenu = cbMainMenuBar.Controls("Help").Index Set cbcCutomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup, before:=iHelpMenu) cbcCutomMenu.Caption = "Tourna&ment"
Set cbcSubMenu = cbcCutomMenu.Controls.Add(Type:=msoControlPopup) cbcSubMenu.Caption = "Number of Players"
' Set the '8' player menu heading With cbcSubMenu.Controls.Add(Type:=msoControlButton) .Caption = "8" .OnAction = "Players8" If Cells(3, 2).Value = "8" Then .State = msoButtonDown Else .State = msoButtonUp End If End With
' Repeat for the 16, 32 and 64 player menu headings With cbcSubMenu.Controls.Add(Type:=msoControlButton) .Caption = "16" .OnAction = "Players16" If Cells(3, 2).Value = "16" Then .State = msoButtonDown Else .State = msoButtonUp End If End With
With cbcSubMenu.Controls.Add(Type:=msoControlButton) .Caption = "32" .OnAction = "Players32" If Cells(3, 2).Value = "32" Then .State = msoButtonDown Else .State = msoButtonUp End If End With
With cbcSubMenu.Controls.Add(Type:=msoControlButton) .Caption = "64" .OnAction = "Players64" If Cells(3, 2).Value = "64" Then .State = msoButtonDown Else .State = msoButtonUp End If End With 'Add the second menu item that will lead off to another menu 'Set a CommandBarControl variable to it Set cbcSubMenu = cbcCutomMenu.Controls.Add(Type:=msoControlPopup) ' Give the control a caption cbcSubMenu.Caption = "Game Type"
With cbcSubMenu.Controls.Add(Type:=msoControlButton) .Caption = "Singles" .OnAction = "TypeSingles" If Cells(4, 2).Value = "Singles" Then .State = msoButtonDown Else .State = msoButtonUp End If End With
With cbcSubMenu.Controls.Add(Type:=msoControlButton) .Caption = "Doubles" .OnAction = "TypeDoubles" If Cells(4, 2).Value = "Doubles" Then .State = msoButtonDown Else .State = msoButtonUp End If End With
' Add a menu heading for creating the template Set cbcSubMenu = cbcCutomMenu.Controls.Add(Type:=msoControlButton) ' Give the control a caption cbcSubMenu.Caption = "Create Template" cbcSubMenu.OnAction = "FormatCode" 'Main programme cbcSubMenu.FaceId = 625 'assigns an icon to the dropdown
' Add a menu heading for randomising the list of players Set cbcSubMenu = cbcCutomMenu.Controls.Add(Type:=msoControlButton) ' Give the control a caption cbcSubMenu.Caption = "Randomise" cbcSubMenu.OnAction = "RandomCode" 'Randomise programme cbcSubMenu.FaceId = 3817 'assigns an icon End Sub Sub FormatCode() Dim aplayer As String Dim irow As Integer 'starting row Dim icol As Integer 'starting column
irow = 7 'starting row position icol = 3 'starting column position
With Cells(2, 2) .Font.Bold = True .Value = "Tournament Settings:" End With Cells(3, 3).Value = "Players"
i = Cells(3, 2).Value 'Number of players aplayer = Cells(4, 2).Value 'Type of game k = 0 'Flag = 0 for Singles) If aplayer = "Doubles" Then k = 1 'Flag = 1 for Doubles) i = i / 2 'Table is half size End If
For j = 0 To k 'One or two player rows Cells(irow, icol + j).BorderAround _ ColorIndex:=0, Weight:=xlThin Range(Cells(irow, icol + j), Cells(irow + i, icol + j)).BorderAround _ ColorIndex:=0, Weight:=xlThin Next j For j = 1 To i Cells(irow + j, icol).Offset(0, -1).Value = j Next j If k = 0 Then 'Singles With Cells(irow, icol) .Value = "Players" .HorizontalAlignment = xlCenter .Font.Bold = True End With Else 'Doubles With Cells(irow, icol) .Value = "Player 1" .HorizontalAlignment = xlCenter .Font.Bold = True End With With Cells(irow, icol + 1) .Value = "Player 2" .HorizontalAlignment = xlCenter .Font.Bold = True End With End If End Sub Sub RandomCode() Dim irow As Integer 'starting row Dim icol As Integer 'starting column
irow = 7 'starting row position icol = 3 'starting column position
'check that the table exists A = Cells(irow, icol).Value 'First Heading If A = "" Then MsgBox "You need to select 'Create Template' first." Exit Sub Else
'check that the table is full i = Cells(3, 2).Value 'Number of players k = 0 If Cells(4, 2).Value = "Doubles" Then i = i / 2 k = 1 End If j = 1 While j < i + 1 If Cells(irow + j, icol).Value = "" Then MsgBox "The player list is not complete" Exit Sub Else j = j + 1 End If Wend
'check that the list is not already random If Cells(5, 2).Value = "Randomised" Then MsgBox "The player list has already been randomised" Exit Sub Else
'copy in a random number For i = 1 To j - 1 Cells(irow + j + i + 1, icol + k + 1).Value = "=RAND()" Next i
'sort and clear the random number Range(Cells(irow + j + 2, icol), Cells(irow + j + i, icol + k + 1)).Select Selection.Sort Key1:=Cells(irow + j + 2, icol + k + 1), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range(Cells(irow + j + 2, icol + k + 1), Cells(irow + j + i, icol + k + 1)).Clear
'Show that the list is now random With Cells(irow + j + 1, icol - 1) .Value = "Random" .HorizontalAlignment = xlRight .Font.Bold = True End With With Cells(5, 2) .Value = "Randomised" .HorizontalAlignment = xlRight .Font.Bold = False End With End If End If End Sub Sub DeleteMenu() ' Use this if you get more than one "Tournament" menu item! On Error Resume Next Application.CommandBars("Worksheet Menu Bar").Controls("Tournament").Delete On Error GoTo 0 End Sub Sub Players8() Cells(3, 2).Value = "8" CreateMenu End Sub Sub Players16() Cells(3, 2).Value = "16" CreateMenu End Sub Sub Players32() Cells(3, 2).Value = "32" CreateMenu End Sub Sub Players64() Cells(3, 2).Value = "64" CreateMenu End Sub Sub TypeSingles() Cells(4, 2).Value = "Singles" CreateMenu End Sub Sub TypeDoubles() Cells(4, 2).Value = "Doubles" CreateMenu End Sub
(These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to! Please note that I use Excel 2003, so there may be variation in the menu instructions if you have Excel 2007)
#23, Issue 79 - Use of VLookup function for assigning scores to a tournament spreadsheet.
As a break from the coding to set up a tournament, I'll be showing you the way in which a table of values can be "looked up". The coding discussed is code I use to transfer a set of scores from a lookup table and assign them to a player according to their final position in a tournament.
The particular use is for updating the Tournament Players' Association scoresheet. Each tournament has its own sheet with the following layout: There is a list of the players' names in order of placement; their actual place and the routine that I will describe puts the scores in according to the placements. Note that some players share a position so must each have the same score which is the average of the total scores assigned to their different placements.
Firstly we declare a few variables and assign some values;
Sub Score() 'Name of subroutine Dim a As String Dim i As Integer Dim PlayerCol As Integer Dim ScoreCol As Integer
PlayerCol = 1 'column number of players' names PositionCol = 3 'column number of final positions ScoreCol = 4 'column number to put scores in RowPos = 2 'first row used
Next we find out what type of tournament it is; either Standard, Premier or Major according to cell (1,5). The type decides what range of scores to use:
atype = Cells(1, 5).Value If atype = "Major" Then TourneyType = 1 ElseIf atype = "Premier" Then TourneyType = 2 ElseIf atype = "Standard" Then TourneyType = 3 Else MsgBox "Tournament Type is out of permitted range!" Exit Sub End If
Note that 1, 2 or 3 are assigned to the "TourneyType" unless there is none of the three words in the correct position (row=1; column=5) in which case an error message is shown and the subroutine terminated. Now find out how many players are in the tournament by scanning down the list until a blank is found:
'itotal will be the total number of players taking part itotal = 0 a = Cells(RowPos + itotal, PlayerCol).Value While a <> "" itotal = itotal + 1 a = Cells(RowPos + itotal, PlayerCol).Value Wend Cells(1,6).Value =itotal
'scan down the column of positions For j = 1 To itotal k = Cells(RowPos + j - 1, PositionCol).Value If k > 33 Then k = 33
Note that k is equal to the player's position in row j and if k is larger than 33 then k is assigned to 33. This is because the score table is only 33 rows deep and all positions after 33 get the points as assigned to position 33.
Now let's see the points table and how Vlookup works. This table is set up on a separate sheet with the name "Scoring. The table has to be defined firstly by highlighting the table and going to the menu Insert/Name/Define and enter the name of the table. You will see that the range of the table you highlighted appears in the range box. The next line of code is: l = WorksheetFunction.VLookup(k, Sheets("Scoring").Range("A2:D34"), TourneyType + 1, False) OK so it looks complex, but think of is as VLookup(Row Value, Table, Position, Partial match?), the first value being the value of the row, which is k; then the table which is on sheet "Scoring" and from A2 to D32 in range; then the column position of the score which is one more than the TourneyType variable; then FALSE ensures an exact match otherwise it would be the nearest value.
So we have variable (l) equal to the points for a given position according to one of three possible values in the table. Now we check to see what the next player's position is. If the next value is the same then we have to keep checking the following players positions to see if they also are the same - so that an average score has to be applied.
m = 0 While Cells(RowPos + j + m, PositionCol).Value = k m = m + 1 l = l + WorksheetFunction.VLookup(k + m, Sheets("Scoring").Range("A2:D34"), TourneyType + 1, False)
Wend
"m" is used to count the number of players at position k. The "While-Wend" routine runs around itself until a different position is detected at which point m is equal to the number of players at the same position and l is the total of the scores in each position. The last bit is to put the score into the sheet:
If m <> 0 Then l = Round(l / (m + 1), 0) For n = 0 To m Cells(RowPos + j - 1 + n, ScoreCol).Value = l Next n j = j + m Else Cells(RowPos + j - 1, ScoreCol).Value = l End If Next j End Sub
Either m = 0 to show that only one player has that position, or m is the number of players in the same position in which case all the scores relating to the different players (l) are divided by the number of players (m) and rounded up to an integer, then copied into all these players' positions.
Sub Score() Dim a As String Dim i As Integer Dim PlayerCol As Integer Dim ScoreCol As Integer
PlayerCol = 1 'column number of players' names PositionCol = 3 'column number of final positions ScoreCol = 4 'column number to put scores in RowPos = 2 'first row used
atype = Cells(1, 5).Value If atype = "Major" Then TourneyType = 1 ElseIf atype = "Premier" Then TourneyType = 2 ElseIf atype = "Standard" Then TourneyType = 3 Else MsgBox "Tournament Type is out of permitted range!" Exit Sub End If
'itotal will be the total number of players taking part itotal = 0 a = Cells(RowPos + itotal, PlayerCol).Value While a <> "" itotal = itotal + 1 a = Cells(RowPos + itotal, PlayerCol).Value Wend
'scan down the column of positions For j = 1 To itotal k = Cells(RowPos + j - 1, PositionCol).Value If k > 33 Then k = 33 l = WorksheetFunction.VLookup(k, Sheets("Scoring").Range("A2:D34"), TourneyType + 1, False) m = 0 While Cells(RowPos + j + m, PositionCol).Value = k m = m + 1 l = l + WorksheetFunction.VLookup(k + m, Sheets("Scoring").Range("A2:D34"), TourneyType + 1, False)
Wend If m <> 0 Then l = Round(l / (m + 1), 0) For n = 0 To m Cells(RowPos + j - 1 + n, ScoreCol).Value = l Next n j = j + m
Else Cells(RowPos + j - 1, ScoreCol).Value = l
End If
Next j
End Sub
Next week will either be a continuation of setting up a tournament or the code I use in transferring these scores to a master spreadsheet of results.
(These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to! Please note that I use Excel 2003, so there may be variation in the menu instructions if you have Excel 2007)
#24, Issue 80 - TPA2 Scoring Spreadsheet - Adding an individual tournament score to a master spreadsheet.
Tournament Players Association Year 2 (TPA2) is a series of tournaments that goes on over a year. The first year there was one tournament released every week, this year I am helping to maintain the scoring for the 46 tournaments either completed or in progress and have used the spreadsheet that I will be describing in this article.
Last issue's article described the routine used to update the individual tournament scores from the finishing positions. Each tournament has its own sheet, so the routine needs to do the following actions: * Input the tournament that you want to add its scores to the main spreadsheet scores; * Add the players names and their scores to the main spreadsheet; * Sort the spreadsheet by players names; * Check for duplicate names - if you fine a duplicate, add the scores to one and delete the other; * Sort by scores
And that's it - basically. It is complicated by the fact that the three types of tournaments have their own sub totals and also that it is such a large spreadsheet that I have found some anomalies with the way in which Excel works. For instance, I like to maintain totals as a way of checking and have found that the formula used in Excel, SUM (A1:A300) say, needs to be restated as it can reduce in value even though the routine has not changed it. I therefore found that checksums were incorrect when the size of the spreadsheet increased beyond a certain point.
OK. The spreadsheet looks like this:
Click image to enlarge.
This is sheet 1 of the spreadsheet. Sheets 2 to 48 hold the individual tournament results and sheet 49 is the Scoring sheet that I referred to last week and holds the table of possible scores for the three different types of tournament.
We start, as usual, defining a few variables and assigning them values:
Sub AddTourney() 'name of this routine Dim irow As Integer 'row number for start Dim icol As Integer 'column number for start Dim isheet As Integer 'sheet stating number Dim iplayers As Integer 'number of players Dim itourneys As Integer 'number of tournaments Dim aplayer As String 'player's name Dim asheet As String 'sheet number
irow = 7 'starting row position icol = 2 'starting column position iplayer = 0 itourneys = 52 '52 maximum tournaments isheet = 3 aplayer = "" addsheet = True 'this is a flag to erase a tournament score
Then we have a call to a subroutine to count the number of players:
Call FindNumber(irow, icol, iplayers) 'find number of players
This subroutine takes the starting row and column position of the player list and also the starting number of players to the routine and is is coded as follows:
Sub FindNumber(irow, icol, iplayers) 'Find number of players on master sheet Sheets("Summary").Activate 'ensure you at the master sheet (called "Summary") iplayers = 0 'number of players already aplayer = Cells(irow + iplayers, icol).Value While aplayer <> "" 'do this loop until we find a blank player name iplayers = iplayers + 1 'increment number of players aplayer = Cells(irow + iplayers, icol).Value 'get next player name Cells(irow + iplayers, icol).Value = StrConv(aplayer, vbLowerCase) ' convert to lower case Wend Cells(irow - 1, icol).Value = iplayers & " Players" End Sub
The conversion to lower case is to minimise the chance of mistyping players names. We now want to find out which tournament is to be added to the master score sheet.
'Add a sheet For i = 1 To iplayers + 100 Cells(irow + i - 1, 1).Value = i 'put ranking order in Next i ' which sheet? B = InputBox("Add a tournament's scores? Enter week number (or 0 to exit) Enter a negative week number to subtract the scores.") If B < 0 Then addsheet = False 'subtract that sheet B = -B End If If B <> "0" And B <> "" And B < itourneys Then 'b=week number asheet = "Week " & B 'go to Sheets(asheet).Activate Else Exit Sub 'invalid sheet number End If
The ranking order is copied to the first column as this was found to change as the spreadsheet grew - without doing anything to it. Perhaps Excel 2007 gets over this? You enter the week number of the tournament to enter and you can also enter a negative number to erase a sheet's scores. Entering zero or a sheet higher than 52 or pressing cancel will exit the routine. The sheet is then activated so that you can get the information you need.
i = 0 aplayer = Cells(i + 2, 1).Value 'player name
If aplayer = "" Then 'no players' names in MsgBox ("Week " & B & " is not completed yet.") Sheets("Summary").Activate Exit Sub ElseIf Cells(8, 1).Value = "Entered" Then MsgBox ("Week " & B & "'s results have already been entered.") Sheets("Summary").Activate Exit Sub End If While aplayer <> "" i = i + 1 'i=number of players on the individual tourney sheet aplayer = Cells(i + 2, 1).Value If addsheet = False Then Cells(i + 1, 4).Value = -Cells(i + 1, 4).Value Wend
Range(Cells(2, 1), Cells(2 + i - 1, 1)).Select Selection.Copy 'copy list of players atype = Cells(1, 5).Value 'type of tournament
The sheet is checked to see if there is a players name in the first position. If not then there is an error message. If the cell in position (8, 1) has "Entered" in it then an error message is given and you return to the main spreadsheet screen. Otherwise the list of players is counted and copied as is the type of tournament. Note that if the scores are to be subtracted from the master spreadsheet than the scores are all changed to negative scores.
Select Case atype Case Is = "Major" j = 1 Case Is = "Premier" j = 2 Case Is = "Standard" j = 3 Case Else MsgBox (atype & " is not permitted") Exit Sub End Select
l = 1 If addsheet = False Then l = -1 For k = 1 To i Cells(irow + iplayers + k - 1, icol + j).Value = Cells(irow + iplayers + k - 1, icol + j).Value + l 'increment number of Tournaments Next k
The tournament type is checked to be a valid one of the three types and there is a "1" or a "-1" put into the tournament number next to all the new players' names under the correct column for the tournament type.
Back to the tournament page to copy the score. Returning to the master sheet this is pasted under the correct column for the tournament itself and also the column for the type of tournament.
If addsheet = False Then Sheets(asheet).Activate 'back to individual sheet Cells(8, 1).Value = "Entered" For k = 1 To i Cells(k + 1, 4).Value = -Cells(k + 1, 4).Value Next k Sheets("Summary").Select End If
If the scores were negative than the routine returns to the individual sheet and turns them positive. "Entered" is copied to the sheet to inhibit repeated entry.
For k = 1 To iplayers - 1 aplayer = Cells(irow + k - 1, icol).Value aplayer2 = Cells(irow + k, icol).Value If aplayer = "" Then ElseIf aplayer = aplayer2 Then
For l = 10 To 10 + itourneys - 1 Cells(irow + k - 1, l).Value = Cells(irow + k - 1, l).Value + Cells(irow + k, l).Value If Cells(irow + k - 1, l).Value = 0 Then Cells(irow + k - 1, l).Value = "" Next l
ActiveWindow.Panes(1).Activate Rows((irow + k) & ":" & (irow + k)).Select Selection.Delete Shift:=xlUp End If Next k
The next block of code is to scan down the list of players' names and, if it finds two identical ones then each column value is added to the top row and then the bottom row deleted.
Call FindNumber(irow, icol, iplayers) 'find number of players For i = 1 To iplayers Cells(irow + i - 1, 1).Value = i 'put ranking order in Next i Range("F4").Select ActiveCell.FormulaR1C1 = "=SUM(R[3]C:R[" & iplayers + 10 & "]C)" Range("F4:I4").Select Selection.FillRight Range("I6").Select ActiveCell.FormulaR1C1 = "=SUM(R[1]C:R[" & iplayers + 10 & "]C)" Range("I6:BD6").Select Selection.FillRight End Sub
To finish off the niumber of players is copied down and put in a heading box and then a couple of formulas put in to act as checksums.
Sub AddTourney() Dim irow As Integer 'row number for start Dim icol As Integer 'column number for start Dim isheet As Integer 'sheet stating number Dim iplayers As Integer 'number of players Dim itourneys As Integer 'number of tournaments Dim aplayer As String Dim asheet As String 'sheet number
Call FindNumber(irow, icol, iplayers) 'find number of players
'Add a sheet For i = 1 To iplayers + 100 Cells(irow + i - 1, 1).Value = i 'put ranking order in Next i ' which sheet? B = InputBox("Add a tournament's scores? Enter week number (or 0 to exit) Enter a negative week number to subtract the scores.") If B < 0 Then addsheet = False 'subtract that sheet B = -B End If If B <> "0" And B <> "" And B < itourneys Then 'b=week number asheet = "Week " & B 'go to Sheets(asheet).Activate Else Exit Sub 'invalid sheet number End If
i = 0 aplayer = Cells(i + 2, 1).Value 'player name
If aplayer = "" Then MsgBox ("Week " & B & " is not completed yet.") Sheets("Summary").Activate Exit Sub ElseIf Cells(8, 1).Value = "Entered" Then MsgBox ("Week " & B & "'s results have already been entered.") Sheets("Summary").Activate Exit Sub End If While aplayer <> "" i = i + 1 'i=number of players on the individual tourney sheet aplayer = Cells(i + 2, 1).Value If addsheet = False Then Cells(i + 1, 4).Value = -Cells(i + 1, 4).Value Wend
Range(Cells(2, 1), Cells(2 + i - 1, 1)).Select Selection.Copy 'copy list of players atype = Cells(1, 5).Value 'type of tournament
Sheets("Summary").Select Cells(irow + iplayers, icol).Select ActiveSheet.Paste 'paste list of players below last one on master
Select Case atype Case Is = "Major" j = 1 Case Is = "Premier" j = 2 Case Is = "Standard" j = 3 Case Else MsgBox (atype & " is not permitted") Exit Sub End Select
l = 1 If addsheet = False Then l = -1 For k = 1 To i Cells(irow + iplayers + k - 1, icol + j).Value = Cells(irow + iplayers + k - 1, icol + j).Value + l 'increment number of Tournaments Next k
Sheets(asheet).Activate 'back to individual sheet Range(Cells(2, 4), Cells(1 + i, 4)).Select Selection.Copy 'copy score list
Sheets("Summary").Select Cells(irow + iplayers, Val(B) + 9).Select ActiveSheet.Paste 'paste in next column
If addsheet = False Then Sheets(asheet).Activate 'back to individual sheet Cells(8, 1).Value = "Entered" For k = 1 To i Cells(k + 1, 4).Value = -Cells(k + 1, 4).Value Next k Sheets("Summary").Select End If
Call FindNumber(irow, icol, iplayers) 'find number of players Rows(irow & ":" & irow + iplayers - 1).Select 'select the players rows
Application.CutCopyMode = False Selection.Sort Key1:=Range("B" & icol), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal 'sort into alpha order
For k = 1 To iplayers - 1 aplayer = Cells(irow + k - 1, icol).Value aplayer2 = Cells(irow + k, icol).Value If aplayer = "" Then ElseIf aplayer = aplayer2 Then
For l = 10 To 10 + itourneys - 1 Cells(irow + k - 1, l).Value = Cells(irow + k - 1, l).Value + Cells(irow + k, l).Value If Cells(irow + k - 1, l).Value = 0 Then Cells(irow + k - 1, l).Value = "" Next l
ActiveWindow.Panes(1).Activate Rows((irow + k) & ":" & (irow + k)).Select Selection.Delete Shift:=xlUp End If Next k
Call FindNumber(irow, icol, iplayers) 'find number of players
For k = 1 To iplayers Cells(irow + k - 1, 9).Value = Cells(irow + k - 1, 8).Value + Cells(irow + k - 1, 7).Value + Cells(irow + k - 1, 6).Value Next k
Call FindNumber(irow, icol, iplayers) 'find number of players For i = 1 To iplayers Cells(irow + i - 1, 1).Value = i 'put ranking order in Next i Range("F4").Select ActiveCell.FormulaR1C1 = "=SUM(R[3]C:R[" & iplayers + 10 & "]C)" Range("F4:I4").Select Selection.FillRight Range("I6").Select ActiveCell.FormulaR1C1 = "=SUM(R[1]C:R[" & iplayers + 10 & "]C)" Range("I6:BD6").Select Selection.FillRight End Sub Sub FindNumber(irow, icol, iplayers) 'Find number of players on master sheet Sheets("Summary").Activate iplayers = 0 'number of players already aplayer = Cells(irow + iplayers, icol).Value While aplayer <> "" iplayers = iplayers + 1 aplayer = Cells(irow + iplayers, icol).Value Cells(irow + iplayers, icol).Value = StrConv(aplayer, vbLowerCase) Wend Cells(irow - 1, icol).Value = iplayers & " Players" End Sub
Phew!
(These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to! Please note that I use Excel 2003, so there may be variation in the menu instructions if you have Excel 2007)
#25, Issue 81 - Using Buttons - Getting to url's via buttons or pictures.
From the very large programme in the last edition to a small article on the use of buttons in your tournament thread to either go to a different url or perform a search. By "button" I mean either a picture, possibly of a button, or a line of text. For instance;
The scoreboard is at :
Clicking on the picture of the button takes you to a spreadsheet. The code for this is quite simple (thanks to Dukasaur who explained it to me!):
[url=X][img]Y[/img][/url]
Where X is the address of the destination url and Y is the url of the picture.
Clicking on the text performs a search of games. Again the code is quite straight forward;
[url=X]text[/url]
"text" is the line of text that you want to use to initiate the search, however X in this case is the address of the conquer club search criteria, which in the above case is:
Looks awesome, but if you look closely at this you will see the coded name of a tournament (ContinentConquer%3A+World+Tour+%5BTPA2%5D) and a Round number (Round+2) and a Page number (&page=1) and if you look closer still you will see the various names which actually refer to fields in the conquer club game finder function; i.e. "gn" = game number; "gs" is game list; "np" is number of players; "mp" is map; "ty" is type; "it" is initial troops; "po" is play order; etc down to p1, p2, p3, p4 which refer to the players 1 to 4.
However, there are two easy ways to get this code (and thanks again, Duke, for the alternative one!).
Either: [*]Perform the game finder search that you want to use (I have searched for Round 2 of the ContinentConquer tournament); [*]Pressing search brings up the games you wanted to search for; [*]Now "Right-Click" anywhere on the page and you bring up a menu; [*]Select "View Page Source" to open a page of coding for that page, which includes the search criteria; [*]On line 108 (this number may vary I believe) you find the line of code used for the search; this is gotoUrl = "player.php?submit=Search&gn=&gs=&np=&mp=&ty=&it=&po=&bc=&ft=&wf=&tw=&rl=&sg=&pt=&to=ContinentConquer%3A+World+Tour+%5BTPA2%5D&lb=Round+2&p1=&p2=&p3=&p4=&so=&page=";; [*]Simply select the part of the code from "player.php" to "page="; [*]Add the address "http://www.conquerclub.com/" to the front and "1" to the end and you have your line of search criteria.
Or; [*]Perform the search that you want to include; [*]Click to go to the next page; [*]The url in the address box is the code that you want, though pointing to page 2; [*]Change the last "2" to a "1" to point to page 1 and the code can be copied.
(These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to! Please note that I use Excel 2003, so there may be variation in the menu instructions if you have later versions)
#26, Issue 82 - Tournament Rescue and Round Robins
As you probably have guessed, I get great satisfaction from using Excel in organising tournaments. However, I have not had any new/different tournament ideas recently so I looked at some of the tournaments that needed rescuing and picked up three that were part of the TPA series of tournaments.
You quickly realise just how inventive others are in their tournament design and it certainly made me think about different ways of managing them using Excel. As an example there is one that started with 100 players playing 100 games on 100 different maps - fantastic!
At the stage that I picked it up the remaining 39 players needed to complete 47 more sets of games. 38 of these is by every player playing everyone else - a Round Robin of 39 players. To do any round robin, irrespective of the number of players there is a simple way of designing it. For instance, with 14 players the match up in Round 1 would be:
1 vs 14 2 vs 13 3 vs 12 4 vs 11 5 vs 10 6 vs 9 7 vs 8
For the remaining rounds you leave player 1 in first place and "rotate" the rest of the players. i.e.
Round 2 1 vs 13 14 vs 12 2 vs 11 3 vs 10 4 vs 9 5 vs 8 6 vs 7
and so on;
Round 3 1 vs 12 13 vs11 14 vs 10 2 vs 9 3 vs 8 4 vs 7 5 vs 6
To translate these into sets of players, the numbers are written into the spreadsheet in two columns and the second set can be coded by referencing the appropriate position in the first set (according to the above sequence). When all positions in the second set are complete, the whole table can be copied sequentailly to complete the number of rounds.
Part of the table therefore looks like: The round numbers are on top. (The players reference numbers are not sequentially as they are from a previous list that was not ordered alphabetically)
The maths for the round robin types of tournament are as follows. For n players , if n is even, there will be n-1 rounds with n/2 games each round (for 1 vs 1 type games of course!). If n is odd then you have to use(n+1) as the "n" value.
OK, so for my tournament I have 38 rounds and, in my spreadsheet, I have a table of 38 sets of two columns of the 39 player match-ups. I have to assign players' names to this and, as in previous articles, I use the Excel VLOOKUP function.
Firstly all the 39 players' names are in a vertical column with 1-39 copied next to them. I highlight the table and, using Insert/Name/Define, I define them as a table called, say, "Players". In the columns next to my two sets of numbers I use Vlookup to assign the appropriate player name - as "=VLOOKUP(player number position, Players, 2, FALSE)" which brings the player name from column 2 of the table "Players" where the number in column 1 matches exactly the player number position specified in my list of match-ups.
I copy the function to every row and so have a double list of players names matching the numbers. I can then create the 19 games and list their numbers next to the two players names. For instance I have 1___Player Number 1_____Player Number 20____20____Game Number in 5 columns on my spreadsheet.
This looks like (for Round 64 on Europe 1914 map: With 39 players, of course the odd number means that the round robin is designed for 40 players and each turn a different player will miss a game. For each round, 19 games are created and the first challenge is to get 39 players assigned to their correct game. The easiest way is to send a list of all the games to all the players involved and allow them to join their correct game. This may be difficult if you have novices or player who do not look at their private messages very frequently, though I expect that a tournasment that has already had 53 rounds would, by now, only contain the battle-hardened tournament professionals!
In the next column on my spreadsheet I therefore create the joining information for the two players and the game number and the "to" CC function.
Round 54; Player 1 vs Player 2 View Game 123456 (Auto-Join Game)
All I need to do is to add a message for the one person who will not get a game due to the odd number of players. To do this I use an IF function; IF(condition, Do this if correct, Otherwise do this) i.e. IF(GameNumber<>"",Player game information as above,"Player X does not have a game this round"). The condition is that the game number ="" (i.e. blank)
and looks like: Player 1 vs Player 2 Game 11702170. This is used to publish the information to the players to keep them informed every round without taking up much time to do. I think it is always important to publish as much information for your players as possible, but if you can do it with a few lines of Excel code, all the better!
In the next issue I will show you how to automate the above sequence.
(These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to! Please note that I use Excel 2003, so there may be variation in the menu instructions if you have other versions of Excel)
#27, Issue 83 - Excel code to automate the game information for a round robin tournament.
In the last issue I showed how a spreadsheet of match-ups can be used to make the invites and tournament thread information easier to manage. In this one I will describe a macro to automate the creation of this information.
The round robin matchups were in tables (as described in the last issue):
The round number is above a table of the 1 vs 1 player number match-ups. Once one table is created (as described in the last issue) it is simply copied sequentially as many times as required to complete the round robin series.
This spreadsheet is called "Round Robins" and a different spreadsheet ("Games") has the detailed game information with the VLookup function to assign the player names to their respective numbers.
This spreadsheet is as follows: Note that there are two blanks in the table. This is where two players ave dropped out and, since this particular tournament was in its final stages, they are not being replaced. The blanks mean that the opponent has a bye that round. Also, at the end of the tournament all players who played a dropped out player and won must have their total scores reduced by one.
Once one table is created, the following macro can be used to create the next one. The descriptions of the stages are described in the coding:
Sub AddRound() ' select the "Games" spreadsheet Sheets("Games").Select
'select the row number of the last table's map reference number cell - this we will call LineStart LineStart = InputBox(Prompt:="Last Row Number", _ Title:="Enter the last row number", Default:="Enter Previous Row Number of the Round Number")
'select the map number in column "B" and Referenced Map in column "C" Range("B" & LineStart, "C" & LineStart).Select Selection.Copy
'add 23 to LineStart (depth of the table) for a variable we can call LineDest linedest = LineStart + 23
'Select and paste the two values to the destination cell Range("B" & linedest, "C" & linedest).Select ActiveSheet.Paste
'get the previous round number and increment it iRoundNumber = Cells(linedest, 2).Value + 1
'paste this value into the destination cell - the VLookup function that we pasted next to it will automatically update the map to the new value Cells(linedest, 2).Value = iRoundNumber
'select the Round Robin sheet Sheets("Round Robin").Select
'Look for the next round number (starting at column 8 and incremented by 3 for the next table I = 8 While Cells(1, I).Value <> iRoundNumber I = I + 3 Wend
'select the table below this round number Range(Cells(3, I), Cells(22, I + 1)).Select Selection.Copy
' go back to the games spreadsheet Sheets("Games").Select
'select the next table start position Cells(linedest + 2, 2).Select
'paste the values into the table - not the formulae! - using paste special. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False
'Move the second column; selecting it first.... Range(Cells(linedest + 2, 3), Cells(linedest + 21, 3)).Select
'...cutting it... Selection.Cut
'...selecting the new position for it... Cells(linedest + 2, 5).Select
'...and pasting it there ActiveSheet.Paste
'select the VLookup function from the previous table... Range(Cells(linedest - 2, 3), Cells(linedest - 2, 4)).Select
'copy it.. Selection.Copy
'and paste in the first row of the second table Cells(linedest + 2, 3).Select ActiveSheet.Paste
'select the whole table and copy the functions down Range(Cells(linedest + 2, 3), Cells(linedest + 21, 4)).Select Selection.FillDown
'copy this table... Selection.Copy
'and paste alongside - this is pasting the values not the formulae - for safety in case of changes to the player list Cells(linedest + 2, 7).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False
'search for any "0" values and replace them with blanks For I = 1 To 19 If Cells(linedest + 1 + I, 7) = "0" Then Cells(linedest + 1 + I, 7) = "" If Cells(linedest + 1 + I, 8) = "0" Then Cells(linedest + 1 + I, 8) = "" Next I
'Now paste in the formula for allowing players to join - this is complex! a = "=IF(I" & (linedest + 2) & "="""",G" & (linedest + 2) a = a & "&H" & (linedest + 2) a = a & "&"" has no game this round.""," & "" a = a & """Round ""&B$" & linedest & "&""; ""&G" & (linedest + 2) a = a & "&"" vs ""&H" & (linedest + 2) & "& "" [to]""&I" a = a & (linedest + 2) & "&"" R""&B$" & (linedest) & "&""[/to]"")" Cells(linedest + 2, 10).Value = a
'Paste the thread formula into the next column a = "=IF(I" & (linedest + 2) & "="""",G" & (linedest + 2) a = a & "&H" & (linedest + 2) a = a & "&"" has no game this round.""," & "" a = a & """Round ""&B$" & linedest & "&""; ""&G" & (linedest + 2) a = a & "&"" vs ""&H" & (linedest + 2) & "& "" [game]""&I" a = a & (linedest + 2) & "&""[/game]"")" Cells(linedest + 2, 11).Value = a
'copy down the two formulae Range(Cells(linedest + 2, 10), Cells(linedest + 21, 11)).Select Selection.FillDown
'Add the headers that can go into the invites and tournament thread Cells(linedest, 10).Value = "Round " & Cells(linedest, 2).Value & " is on map " & Cells(linedest, 3).Value & ". Join just 1. 2 players have byes." Cells(linedest, 11).Value = "Round " & Cells(linedest, 2).Value & " is on map " & Cells(linedest, 3).Value & ". 2 players have byes."
End Sub
To use a new block of code a pm is sent to all the players in the tournament with the block of data below column 10. This can be used for the players to join their correct game. The block of data in column 11 can be written into the tournament thread to track the rounds and provide access information.
In principle, any required sequences of operations can be written into Excel using the Record Macro function. So, even if you don't know or fully understand the above coding, you can still use this recording function to automate a sequence of Excel operations.
(These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to! Please note that I use Excel 2003, so there may be variation in the menu instructions if you have other versions of Excel)
#28, Issue 84 - Excel code for extracting the order of terminations.
Some tournaments have points awarded according to the order that players are terminated. It can therefore be a laborious process to enter each game and note the orders so, even though it requires you still to enter each game, I'll show you a method of speeding up getting the results.
On each terminator game is the list of termination details:
The text from this list can be copied and pasted into a spreadsheet and if you look closely at the important termination details, say "2011-10-28 02:32:44 - JJ41375 eliminated morleyjoe from the game in round - 6" you can pick out the two player names as the first is after "- " and the second after "terminated".
There is a useful function in Excel that can find text, so =FIND("- ",C3,1) will look for the "- " characters in cell C3 which in this case holds the line of text from the game details. This comes back with "21" which is the character position of the "- " text. Similarly =FIND("eliminated ",C3,1) will come back with character position 31. We can therefore pick out the first player's name with the code =MID(C3,E3+2,F3-E3-2) which takes the middle text out of cell C3 starting at 21 plus 2 (which is the start position of the player's name) and the number of characters in the player's name is 31-21-2 i.e. start position of "terminated" less the start position of "- " less 2.
The formula to get the second player's name is =MID(C3,F3+11,FIND(" from",C3,F3)-F3-10) which uses the Find function again to get to the end of the second player's names.
So, we can get the two names in two columns of the spreadsheet using Excel functions. We now need to use a bit of Visual Basic to get rid of the two lines of text regarding points (unless your tournament uses these values of course!) so that we get the relevant lines of termination details.
This gives the terminations in the order they occurred and there are blank cells next to the names into which the Visual Basic code can put "scores" according to the tournament rules. i.e.
In this tournament's rules (Sonic goes back to school) there is a point per termination; and extra 8 points for the winner and points that increase in the order of terminations - as shown next to the player's names.
So, on to the coding used. Firstly let me show you the finished page of results, which gives you an idea of what the required outputs are:
Click image to enlarge.
This has the game number on the left and the termination data for each game next to it. On the right is a summary list of all the totals in the order of scores and the far right column is a list of the unique terminations which was required in this tournament as there is a second medal for its leader. The two buttons trigger the two sets of code; the first "Rationalise" extracts the termination data and ascribes the scores and the second "Total" summarises the two columns of scores and terminations.
The coding for the two buttons is as follows (with descriptions within the code):
'Find the end of the list ListEnd = 3 While Cells(ListEnd, 3).Value <> "" ListEnd = ListEnd + 1 Wend
'Find the start of the last set of data ListStart = ListEnd While Cells(ListStart, 2).Value = "" ListStart = ListStart - 1 Wend
' Delete the non-relevant information i = ListStart While ListEnd <> i Rows(i + 1 & ":" & i + 1).Select Selection.Delete Shift:=xlUp Selection.Delete Shift:=xlUp ListEnd = ListEnd - 2 i = i + 1 Wend ListEnd = ListEnd - 1 'Select and paste down the Excel functions Range(Cells(ListStart - 1, 5), Cells(ListEnd, 9)).Select Selection.FillDown
'put in the values required by the tournament j = 1 For i = ListStart To ListEnd Cells(i, 10).Value = j j = j + 1 Cells(i, 8).Value = 1 Next i
Cells(i - 1, 8).Value = 9 End Sub
Note that this code will not work properly if a player is thrown out for missing his goes, as there are extra lines of non-relevant text in the list that have to removed manually before the code can be run.
'Find the last cell used While Cells(ListEnd, 3).Value <> "" ListEnd = ListEnd + 1 Wend ListEnd = ListEnd - 1
'Select the first two columns Range(Cells(ListStart, 7), Cells(ListEnd, 8)).Select Selection.Copy
'Paste special into the results column Range(Cells(ListStart, 12), Cells(ListEnd, 13)).Select Selection.PasteSpecial Paste:=xlPasteValues
'Also paste into the list of terminations Range(Cells(ListStart, 15), Cells(ListEnd, 16)).Select Selection.PasteSpecial Paste:=xlPasteValues
'Put in just 1 termination For i = 3 To ListEnd Cells(i, 16) = 1 Next i
'Select the second set of columns Range(Cells(ListStart, 9), Cells(ListEnd, 10)).Select Selection.Copy
'Paste on the end of the first set of columns Range(Cells(ListEnd + 1, 12), Cells(ListEnd + ListEnd + 1, 13)).Select Selection.PasteSpecial Paste:=xlPasteValues
'Select and sort by name Range(Cells(ListStart, 12), Cells(ListEnd + ListEnd + 1, 13)).Select Selection.Sort Key1:=Range("L3"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal
'Select and sort the second set of columns Range(Cells(ListStart, 15), Cells(ListEnd, 16)).Select Selection.Sort Key1:=Range("O3"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal
'Check for names the same - if so delete and add on the scores For i = 3 To ListEnd + ListEnd + 1 a = Cells(i, 12).Value b = Cells(i + 1, 12).Value If b = a Then Cells(i + 1, 13).Value = Cells(i + 1, 13).Value + Cells(i, 13).Value Cells(i, 12).Value = "" Cells(i, 13).Value = "" End If Next i
'Do the same for the second list 'Check for names the same - if so delete and add on the scores For i = 3 To ListEnd a = Cells(i, 15).Value b = Cells(i + 1, 15).Value If b = a Then Cells(i + 1, 16).Value = Cells(i + 1, 16).Value + Cells(i, 16).Value Cells(i, 15).Value = "" Cells(i, 16).Value = "" End If Next i
'Sort by score Range(Cells(ListStart, 15), Cells(ListEnd + ListEnd + 1, 16)).Select Selection.Sort Key1:=Range("P3"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub Sub AddAll()
Dim ListEnd As Integer Dim ListStart As Integer
ListEnd = 2 ListStart = 2
'Find the last cell used While Cells(ListEnd, 3).Value <> "" ListEnd = ListEnd + 1 Wend ListEnd = ListEnd - 1 'Select and sort by name Range(Cells(ListStart, 2), Cells(ListEnd + ListEnd + 1, 3)).Select Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal
'Select and sort the second set of columns Range(Cells(ListStart, 5), Cells(ListEnd, 6)).Select Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal
'Check for names the same - if so delete and add on the scores For i = 2 To ListEnd + ListEnd + 1 a = Cells(i, 2).Value b = Cells(i + 1, 2).Value If b = a Then Cells(i + 1, 3).Value = Cells(i + 1, 3).Value + Cells(i, 3).Value Cells(i, 2).Value = "" Cells(i, 3).Value = "" End If Next i
'Do the same for the second list 'Check for names the same - if so delete and add on the scores For i = 2 To ListEnd a = Cells(i, 5).Value b = Cells(i + 1, 5).Value If b = a Then Cells(i + 1, 6).Value = Cells(i + 1, 6).Value + Cells(i, 6).Value Cells(i, 5).Value = "" Cells(i, 6).Value = "" End If Next i
'Sort by score Range(Cells(ListStart, 5), Cells(ListEnd + ListEnd + 1, 6)).Select Selection.Sort Key1:=Range("F2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub
What is then required for this particular tournament is to add all the scores together from the different "subject" sheets and publish them in the tournament thread.
(These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to! Please note that I use Excel 2003, so there may be variation in the menu instructions if you have other versions of Excel)
#29, Issue 85 - Excel code used to add tournament result points to the TPA2 master spreadsheet.
In newsletter #80 I detailed the results spreadsheet for TPA2 scores and coding to update it with an individual tournament results. This does not, however cope with the change that was introduced after TPA1 to select the best scores from a number of scores. For instance there are only 8 Premier tournament scores allowed from the 13 actual premier tournaments and only 16 from the 28 Standard tournaments.
The problem with doing this within the main coding system is that the checksums would be compromised, so I have decided to do this using an additional routine that is accessed from a button on the main spreadsheet. The process of working out the coding follow the same sequence that you would do manually; for instance:
[*]Check each player in turn; [*]Check the number of Premier and Standard tournaments completed against the allowed number; [*]If the number is exceeded then get all the tournament scores of that particular type; [*]Sort them into order; [*]Take away the sum of all the lowest scores above the maximum number allowed from the total.
Done!
In doing the coding it is straight forward until we get to the sorting. For instance (with description within the coding);
Sub CheckNumber() 'set variables iPlayerRow = 7 'First Player Row iListStart = 58 'Row used for lists iPlayerTotal = 0 'Total of Number of Players
'I decided to copy the players names and totals in blank columns after the main spreadsheet, hence the starting position of column 58 'Copy Names and Totals into blank columns - also getting the total number of competitors While Cells(iPlayerRow + iPlayerTotal, 2).Value <> "" Cells(iPlayerRow + iPlayerTotal, iListStart).Value = Cells(iPlayerRow + iPlayerTotal, 2).Value Cells(iPlayerRow + iPlayerTotal, iListStart + 1).Value = Cells(iPlayerRow + iPlayerTotal, 9).Value iPlayerTotal = iPlayerTotal + 1 Wend
'Select Premier and Standard Columns, icol being th enumber of these column totals For icol = 4 To 5
'Select the Maximum Number of Tournaments and call it iMax iMax = Cells(2, icol).Value
'Scan down the columns to see if the maximum number has been exceeded For irow = iPlayerRow To iPlayerRow + iPlayerTotal
If Cells(irow, icol).Value > iMax Then 'maximum number exceeded
'put this number into the column next to the palyers' names Cells(irow, iListStart + icol - 2).Value = Cells(irow, icol).Value
We are now at the position of getting the scores and putting them into a table or array. I would normally use an array, but sorting is easier in excel, so I decided to use 1 table of 30 rows (higher than the 28 max standard tournaments within the spreadsheet itself). This has the added advantage of being easier to check when testing;
'clear table For i = 1 To 30 Cells(iPlayerRow + i, iListStart + 4).Value = 0 Next i
'Scan across all 47 tournaments For i = 10 To 56
'Check that the tournament type matches either Standard or Premier If (Cells(4, i).Value = "Premier" And icol = 4) Or (Cells(4, i).Value = "Standard" And icol = 5) Then
'Check that there is a score from this tournament If Cells(irow, i).Value > 0 Then
'If so, find next blank position in table and enter the score j = 1 While Cells(iPlayerRow + j, iListStart + 4).Value <> 0 j = j + 1 Wend
'insert the value into the table Cells(iPlayerRow + j, iListStart + 4).Value = Cells(irow, i).Value
End If End If Next i
So far so good!
We can sort using the Excel sorting system. An easy way of getting the coding is to manually create a Macro, select the table and sort. Then look at the coding used by the macro and simply copy it into your programme. After sorting in reducing order we sum the scores above the permitted maximum and subtract them from the total score - this coding was taken from the Internet and I still do not understand the format!
'Sort High to low 'This coding is straight from the macro created by Excel Range(Cells(iPlayerRow + 1, iListStart + 4), Cells(iPlayerRow + 30, iListStart + 4)).Select Selection.Sort Key1:=Range("BJ8"), Order1:=xlDescending, Header:=xlGuess _ , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal
'Reduce the total by the sum of values below the value that is the iMax number 'Coding taken from the Internet! Range("BJ7").Formula = "=SUM(" & Range(Cells(iPlayerRow + 1 + iMax, iListStart + 4), Cells(iPlayerRow + 30, iListStart + 4)).Address(False, False) & ")"
'Reduce the total and adjust the number of tournaments to iMax Cells(irow, iListStart + 1).Value = Cells(irow, iListStart + 1).Value - BJ7 Cells(irow, iListStart + 2).Value = iMax End If Next irow Next icol End Sub
And that is it. I access this routine from a button on the spreadsheet. To put in a button, see my archive Archive and check out number 10 in my series (Newsletter #66).
(These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to! Please note that I use Excel 2003, so there may be variation in the menu instructions if you have other versions of Excel)
#30, Issue 86 - Finding a game without using Game Finder and what the various labels in refer to.
[url=http://www.conquerclub.com/player.php?submit=Search&gn=&gs=&np=&mp=&ty=&it=&po=&bc=&ft=&wf=&tw=&rl=&sg=&pt=1&to=&lb=&p1=&p2=&p3=&p4=&so=&page=1][b]Find All Round 2 Games[/b][/url]
In this code there are lots of variables such as "gn=" and only a few are set to a value. It looks quite horrendous, but if you compare it with the Game Finder screen, you will see that it starts to make sense.
Click image to enlarge.
The "&" is to link each of the variables together. Let's define what these variables are:
gn= Game Number gs= Game Status (W=Waiting; A=Active; F=Finished} np= Number of Players (2, 3 etc) mp= Map (Map Number; not Name!) ty= Game Type (S (or blank) =Standard; C=Terminator; A=Assassin; D=Doubles; T=Triples; Q=Quads) it= Initial Troops (M=Manual) po= Play Order (F=Freestyle) bc= Type of Spoils (1 (or blank)=No Spoils; 2= Escalating; 3=Flat Rate; 4 = Nuclear) ft= Reinforcement Type (O for Adjacent; M=Unlimited; C=Chained)) wf= With Fog (Y = yes) tw= Trench Warfare (Y = yes) rl= Round Limit (20 etc) sg= Special Gamesplay (5 etc) pt= Private (y = Yes; T = Tournament) to=Tournament Title i.e.ContinentConquer%3A+World+Tour+%5BTPA2%5D lb= Game Label i.e. Round+2 p1= Player 1 p2= Player 2 p3= Player 3 p4= Player 4 so=???? page= Page Number
Perhaps "so" is for a variable that is yet to be specified? Does anyone know?
(Please note that the above letters in red are my interpretation of their derivation!) Just a quick note on the Tournament Title, which codes "ContinentConquer: World Tour [TPA2]" as ContinentConquer%3A+World+Tour+%5BTPA2%5D. The %3A represents the colon; + represents a space and %5B and %5D represent "[" and "]". If "[" and/or "]" were used instead then they interfere with the coding which results in an error message. This is the reason that using Game Finder followed by page forward and page back and getting the resulting code from the thread address does not work if either of these characters is in the title.
(These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to! Please note that I use Excel 2003, so there may be variation in the menu instructions if you have other versions of Excel)
Last edited by DaveH on Wed Jan 09, 2013 12:16 am, edited 3 times in total.
When you want to do something like inviting players, you use CC Code such as [to]GameNumber Password[/to]when sending a pm to players. If this is to be coded from Excel then you need to use the following code: ="[to]" & LocationOfGameNumber & " " & LocationOfPassword & "[/to]" Note that the "&" is used to link text and data locations.
If you now need to code the above using Visual Basic for Excel (VBE) then there is an added complexity. Let's take the above code piece by piece:
="[to]" must be coded as ="=""[to]"" using double quotes in VBE where you need to get a single quote in Excel. A location such as A1 can be coded as "A" & Linedest where Linedest is the variable used in VBE to hold the row number.
The full coding for the above will therefore be = "=""[to]"" & " & "M" & Linedest & " & " & """ ""& N" & Linedest & "&""[/to]"""
This coding should be assigned to a variable and then written into Excel. i.e. a = "=""[to]"" & " & "M" & Linedest & " & " & """ ""& N" & Linedest & "&""[/to]""" Cells(Row, Column).Value = a
After running the Visual Basic routine you can then copy the resulting Excel from the spreadsheet and paste it into a pm to invite the player. Obviously this is useful if you want to automate a series of invites such as another example of inviting two players into a game:
This is taken from the spreadsheet used to create the invites for Centrum Tournament, which consisted of 100 rounds, with only 9 players taking the final set of round-robin games. The Excel routine which is listed in my previous newsletters, puts all this infomation into the spreadsheet. All we are interested in is this example is the coding for the player invites in Column J.
Player 1 is in column G; Player 2 in column H and the game number in column I. The variable "linedest" points to the line number of each line of the invites.
The required output in Excel for sending a pm to inite players into games is either "Round 100; ThrushAAX vs Gromph View Game 12044506 (Auto-Join Game)" where the two players have a game, or "Bones2484 has no game this round." where no game number is specified.,
With "linedest" = 955, this needs to be written into Excel as: =IF(I957="",G957&H957&" has no game this round.","Round "&B$955&"; "&G957&" vs "&H957& " [to]"&I957&" R"&B$955&"[/to]") where B955 contains the round number and the password is R followed by the round number (for simplicity).
This becomes a little more complex when writing the VBE code that will create this text; note that I have broken up the coding to try and make it a little less complex:
a = "=IF(I" & (linedest + 2) & "="""",G" & (linedest + 2) a = a & "&H" & (linedest + 2) a = a & "&"" has no game this round.""," & "" a = a & """Round ""&B$" & linedest & "&""; ""&G" & (linedest + 2) a = a & "&"" vs ""&H" & (linedest + 2) & "& "" [to]""&I" a = a & (linedest + 2) & "&"" R""&B$" & (linedest) & "&""[/to]"")" Cells(linedest + 2, 10).Value = a
When this routine is run for the values of "linedest" incremented down the table, Column J is created. This block of data can then be copied and pasted into a pm to the players involved in this Round-Robin to allow them to join their appropriate games.
(These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to! Please note that I use Excel 2003, so there may be variation in the menu instructions if you have other versions of Excel)
#32, Issue 88 - Scoring termination games by analysing the game log.
When a game is finished and archived the round information is not retained. This can be problematical for any TO who might be giving additional points to serial terminations, such as the prolific TO, greenoaks, as he does in his Good Luck, Good Hunting series. In these tournaments a player gets 1 point for a termination but then 3 points for the next termination if it is in the same round (and so on); i.e. another player has not played a move between terminations.
If we look at part of the full game log: You can see that immediately after the termination there is information about points lost and gained by both players, otherwise if there were another termination that same round, there would be no other players name as the first text after the date and time date.
So, if we were to automate a programme to analyse the full game log, it would have to scan down the lines to find the word "eliminated", ignore the next line and check if the same player's name occurs up to the next termination. Simple in theory but the world of programming is never straight forward!
The following routine produces the following summary from the full game log: The full game text is pasted (as paste special - text) in the area indicated and the button links to the macro detailed below. The second termination is in the same round so has the "IN SAME ROUND" text after it (if required this could also put in points and the player's name). What the programme does is scan down all lines of the log, erasing them from their position and rewriting at the top for analysis. Only if "eliminated" is found is the text retained, in which case the player's name is kept and compared with every name subsequently until the next "eliminated" is found. Obviously, apart from the points line, the same player's name will be on every line if it is the same round, otherwise it must be a "1 point" termination.
Sub TerminateData() ' ' Subroutine to analyse terminator data from game chat '
iStartRow = 5 '1st line of game log text iStartCol = 2 'column used for game log iPlaceRow = 5 'start line for putting summarised text
i = 1 'index for the text line position k = 0 'flag for "eliminated found" b = "" 'name of player that has "eliminated" another
'Does the next row have any text? While Cells(iStartRow + i, iStartCol).Value <> ""
'If so move it up to the next line used for summary Cells(iStartRow + i, iStartCol).Select Selection.Cut Cells(iPlaceRow, iStartCol).Select ActiveSheet.Paste
'if the flag is set, get the part of the text containing the player's name
If k = 1 Then m = 1
'firstly check for the end of the name (space) c = Mid(Cells(iPlaceRow, iStartCol).Value, 23 + m, 1) While c <> " " And m < 10 m = m + 1 c = Mid(Cells(iPlaceRow, iStartCol).Value, 23 + m, 1) Wend c = Mid(Cells(iPlaceRow, iStartCol).Value, 23, m)
'If the player's name has changed then it is not the same round for the next elimination If c <> b Then k = 0 End If
' Increment line number i = i + 1
'Check for "eliminated" For j = 20 To 50 If Mid(Cells(iPlaceRow, iStartCol).Value, j, 10) = "eliminated" Then
'If so clear the next two lines Cells(iStartRow + i, iStartCol).Select Selection.Clear i = i + 1 Cells(iStartRow + i, iStartCol).Select Selection.Clear i = i + 1
' get player name (to space) m = 1 b = Mid(Cells(iPlaceRow, iStartCol).Value, 23 + m, 1) While b <> " " And m < 10 m = m + 1 b = Mid(Cells(iPlaceRow, iStartCol).Value, 23 + m, 1) Wend
'b=player's name b = Mid(Cells(iPlaceRow, iStartCol).Value, 23, m)
'increment placement Index iPlaceRow = iPlaceRow + 1
'if the k flag is still set it is the same round If k = 1 Then Cells(iPlaceRow, iStartCol).Value = "IN SAME ROUND" iPlaceRow = iPlaceRow + 1 End If k = 1 End If Next j Wend End Sub
(These "tips" started off as ones that I used to make it a bit easier in managing my own tournaments. Now, I am looking at other types of tournaments and scoring systems by all you innovative TO's out there. So, if you have a unique scoring system that you would like me to have a go at automating, please let me know and I'll be pleased to try and assist. Any queries on the above I'll be glad to respond to of course. Please note that I use Excel 2003, so there may be variation in the menu instructions if you have other versions of Excel)
#33, Issue 89 - Part 1 of coding that can be used in League type tournaments.
In this and a few of my next articles I want to pay tribute to flexmaster33 who has had to leave CC under very unfortunate circumstances. All of you that have played in one of his games series, such as 2012 NFL season, will know how detailed his tournaments were, following the schedule of game leagues and with professional looking graphics and quite complex scoring methods.
I have picked up the continuation of the above tournament of his and while I won't be able to continue it with the same level of sophistication as flex's, I hope that if I show you the system I use to simplify the running of such tournaments, it may encourage someone who does have these skills to run such tournaments in future.
The overview of his tournaments is as follows. In the usual way a set of teams is published and players assign themselves to one of the teams, either as individuals, as in the tournament I will describe, or as a team of players, and specify home map (or maps) and preferred settings.
The match-ups are formed according to the real league schedules and games published accordingly. The scoring on the tournament I will describe is according to the winning round, and then the results are published and the league tables updated with these results.
There are a number of elements that have to be linked; the league team to the player/s; the player/s to the games; the winning rounds to the score and the scores back to the team, via the player/s. For linking these I find the simplest function in Excel is the Vlookup function and the best tool in CC for completed games is the "Tour Stats" add-on that can by accessed by the tournament organiser in the game finder when the tournament is selected.
There are three screens obtained using this button; Tournament Summary; which links players to Number of wins and losses and any active games:
Tournament Results; which links game numbers to winning round and players; and Labels; which brings player data pertaining to the labels that the TO adds when creating the games.
All these forms can be copied and pasted (paste special/text) into Excel and then, using the VLookup function, all the necessary data for assisting the TO can be obtained.
I'll get into more details starting in the next issue.
(These "tips" started off as ones that I used to make it a bit easier in managing my own tournaments. Now, I am looking at other types of tournaments and scoring systems by all you innovative TO's out there. So, if you have a unique scoring system that you would like me to have a go at automating, please let me know and I'll be pleased to try and assist. Any queries on the above I'll be glad to respond to of course. Please note that I use Excel 2003, so there may be variation in the menu instructions if you have other versions of Excel)
#34, Issue 90 - Part 2 of coding that can be used in League type tournaments.
In this article I will continue with the steps I am using to continue the 2012 NFL season tournament I picked up from flexmaster33.
The first thing I had to do was to update two weeks of results. Using Game Finder and selecting the tournament and then pressing the "Tour Stats" button, I selected the Results page which looks like: and copied all the results and pasted into Excel.
I sorted by the tournament number and then made it into a Lookup table by selecting the whole table, going to Insert/Name Define and called it "Results". This table links game numbers to Winning Round and Players.
I next needed to find all the games for the "Week 10" results, which was by using Game Finder with "W10" in the Game Label box under the tournament name. The easiest way of doing this is to paste the largest game number at the top of a column and copy the formula "=CelNoAbove-1". This creates a game number one less than the largest game number - and so on down the column. This works very well if all the games had been created consecutively, but there are invariably gaps which means I have to change the formula substituting -2, -3 etc for the -1 in some places in order to replicate the order exactly. OK, so I have checked and have a list of Week 10 games numbers, so I copy the list and select the menu Edit/Paste Special/Values to translate the formulae into numerals (formulas are OK but one error at the top of the table can spread all the way down!).
In the next columns I may want the winning player; losing player and Round number of the win. This is easy to do by selecting the VLookup function to lookup the relevant data from the table we just created. The function is VLOOKUP(Game Number Column, Results, 4, FALSE) for getting the data from column 2 of the Results table (Winning Player); VLOOKUP(Game Number Column, Results, 5, FALSE) for Losing Player; VLOOKUP(Game Number Column, Results, 3, FALSE) for Round Number.
In the next column I may want the Score attributed to winning in that particular round. This is obtained using another Vlookup function looking up the Score from a table based on the winning round. The scores that were used in this tournament were;
Click image to enlarge.
With a 50 round limit, in Excel I needed to have a table of Round Number (from 1 to 50); Winning Points and Losing Points which I called Score and, for the special game field goal points a table called ScoreS with Round Number (1 to 50) and Winning Points. To get these points into the next column of my table I used the function VLOOKUP(WINNING ROUND CELL, Score, 2, FALSE) and VLOOKUP(WINNING ROUND CELL, Score, 3, FALSE) for the winning and losing points respectively. I also had to paste in the VLOOKUP(WINNING ROUND CELL, ScoreS, 2, FALSE) formula in those places where the special maps were used.
To get the Team Names into the next column of my table, I had to create a Vlookup table of the Players Names (in alphabetical order and called "Players") next to the team name they represented, so the two functions VLOOKUP(WINNING PLAYER CELL, Teams, 2, FALSE) and VLOOKUP(LOSING PLAYER CELL, Teams, 2, FALSE) put the team names of winners and losers into the next columns respectively.
My completed table now had: Game Number; Winning Player Name; Losing Player Name; Round Number; Winning Team Points; Losing Team Points; Winning Team; Losing Team, which gives me all the data I need to update the results for Week 10. Having created the various lookup tables, it was straight forward to do the same for the next week's data.
To simplify the process of extracting the data from the tables into a master scoresheet I created a Visual Basic Routine, which I will describe in the next article.
(These "tips" started off as ones that I used to make it a bit easier in managing my own tournaments. Now, I am looking at other types of tournaments and scoring systems by all you innovative TO's out there. So, if you have a unique scoring system that you would like me to have a go at automating, please let me know and I'll be pleased to try and assist. Any queries on the above I'll be glad to respond to of course. Please note that I use Excel 2003, so there may be variation in the menu instructions if you have other versions of Excel)
#35, Issue 91 - Part 3 of coding that can be used in League type tournaments.
In this article I will continue with the steps I am using to continue the 2012 NFL season tournament I picked up from flexmaster33.
In the last article I explained the creation of lookup tables so that the points awarded to the winning and losing teams could be translated from the winning round. This created the following table (though the order of items is a little different than I explained in the last article - due to improvement tinkering!)
From this table I created a list of teams and total points using the following routine:
j = 1 While j = 1 j = 0 For i = 74 To 213 a = Cells(i, 3).Value If a = "" Then i = 213 Else b = Cells(i + 1, 3).Value If b = a Then Cells(i, 4).Value = Cells(i, 4).Value + Cells(i + 1, 4).Value Cells(i + 1, 3).Value = "" Cells(i + 1, 4).Value = "" j = 1 End If End If Next i Range("C74:D213").Select Application.CutCopyMode = False Selection.Sort Key1:=Range("C74"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Wend End Sub
This routine simply forms a list of winning teams with points and adds the losing teams and points before adding any duplicate team names and sorting into alphabetical order. I could then extract the various points for the following summary; Note that I have highlighted the winning team - for my own information only.
In the next article I will explain the routine that updates the master scorechart from this data:
(These "tips" started off as ones that I used to make it a bit easier in managing my own tournaments. Now, I am looking at other types of tournaments and scoring systems by all you innovative TO's out there. So, if you have a unique scoring system that you would like me to have a go at automating, please let me know and I'll be pleased to try and assist. Any queries on the above I'll be glad to respond to of course. Please note that I use Excel 2003, so there may be variation in the menu instructions if you have other versions of Excel)
#36, Issue 92 - Part 4 of coding that can be used in League type tournaments.
In this article I will continue with the steps I am using to continue the 2012 NFL season tournament I picked up from flexmaster33.
In the last article I explained the creation of lookup tables so that the points awarded to the winning and losing teams could be translated from the winning round. This created a table from which I created a list of teams and total points using the routine that I called "AddScores()".
This routine formed a list of teams with winning and losing points added from which I manually formed the following summary; Note that I have highlighted the winning team - for my own information only.
To update the master scorechart, I need a routine that selects the starting row of the week's results. It then picks out, in turn, the winning team and score and the losing team and score. It then goes down the master scorechart to match the name of either team. The home or away points are added and also the number of wins or loses also.
Sub Update() iResRowStart = InputBox("Enter the Starting Row Number of the Results:")
'select each matchup in turn and extract the winning and losing data For i = 0 To 15 aAwayTeam = Cells(iResRowStart + i, 3).Value aHomeTeam = Cells(iResRowStart + i, 5).Value iAwayScore = Cells(iResRowStart + i, 4).Value iHomeScore = Cells(iResRowStart + i, 6).Value
For j = 3 To 34 'run down the team name column of the master scorechart If Cells(j, 3).Value = aAwayTeam Then 'an away team match Cells(j, 8).Value = Cells(j, 8).Value + iAwayScore 'update the home and away scores Cells(j, 9).Value = Cells(j, 9).Value + iHomeScore If iHomeScore > iAwayScore Then 'Home Win Cells(j, 5).Value = Cells(j, 5).Value + 1 ElseIf iHomeScore = iAwayScore Then 'Tied Cells(j, 6).Value = Cells(j, 6).Value + 1 Else 'Away Win Cells(j, 4).Value = Cells(j, 4).Value + 1 End If
ElseIf Cells(j, 3).Value = aHomeTeam Then ' repeat as above but for a home team Cells(j, 8).Value = Cells(j, 8).Value + iHomeScore Cells(j, 9).Value = Cells(j, 9).Value + iAwayScore If iHomeScore > iAwayScore Then 'Home Win Cells(j, 4).Value = Cells(j, 4).Value + 1 ElseIf iHomeScore = iAwayScore Then 'Tied Cells(j, 6).Value = Cells(j, 6).Value + 1 Else 'Away Win Cells(j, 5).Value = Cells(j, 5).Value + 1 End If Else End If Next j Next i End Sub
One of the problems with a any routine such as this is if the team name is spelled incorrectly, in which case there will be missing data in the master scorechart. To check on this I tally the total scores in the week's results and check that, in updating it, twice this value has been added to the previous total (twice because each team score is recorded twice on the master scorechart).
(These "tips" started off as ones that I used to make it a bit easier in managing my own tournaments. Now, I am looking at other types of tournaments and scoring systems by all you innovative TO's out there. So, if you have a unique scoring system that you would like me to have a go at automating, please let me know and I'll be pleased to try and assist. Any queries on the above I'll be glad to respond to of course. Please note that I use Excel 2003, so there may be variation in the menu instructions if you have other versions of Excel)
#37, Issue 93 - Using Excel to create a table in a thread.
I have yet to find an easy way of creating a table in a tournament thread. For instance if you want a league table with name, played, won, lost etc. that you have in Excel, you either have to take a picture of the formatted table and upload it, or you can use Challonge or other suitable internet sites to keep track of results.
The problem in CC is that multiple spaces are ignored, so all the table data is concatenated. To try and compensate for this I have written the following software that creates a table. OK - it is not wonderful, as the different characters have unequal spacing, but it is readable and quicker to transfer from Excel to your thread than via a photo.
The routine allows you to specify the starting and finishing rows and columns of your table and then includes underlines (_) to space out the different columns according to the length of the text in them. Were the text of equal spacing (as say teletext font) then it would be perfectly in line, but as the font is not of equal spacing, it does wave about a bit!
Sub FormLines() ' ' FormLines recorded 12/12/2012 by DaveH ' ' specify the starting and finishing rows and columns of the table Dim iRowStart As Integer Dim iRowFinish As Integer Dim iColStart As Integer Dim iColFinish As Integer
Dim iLength(100) As Integer 'to hold the max text length in each column
' allow input of the 4 variables iRowStart = InputBox("Enter the Starting Row Number:") iRowFinish = InputBox("Enter the Finishing Row Number:") iColStart = InputBox("Enter the Starting Column Number:") iColFinish = InputBox("Enter the Finishing Column Number:")
'scan down for the longest name in each column For j = iColStart To iColFinish For i = iRowStart To iRowFinish - 1
'clear cell at start If i = iRowStart Then Cells(i, iColFinish + 2).Value = "" iLength(j) = 0 End If
'Find the longest word a = Cells(i, j).Value b = Cells(i + 1, j).Value If Len(b) > iLength(j) Then iLength(j) = Len(b) If Len(a) >= Len(b) Then If Len(a) > iLength(j) Then iLength(j) = Len(a) End If Next i Next j
'include the required number of underlines For i = iRowStart To iRowFinish For k = iColStart To iColFinish Cells(i, iColFinish + 2).Value = (Cells(i, iColFinish + 2).Value) & (Cells(i, k).Value) l = 0 If k <> iColFinish Then While Len(Cells(i, k).Value) + l < iLength(k) + 2 Cells(i, iColFinish + 2).Value = (Cells(i, iColFinish + 2).Value) & "_" l = l + 1 Wend
End If
Next k Next i End Sub
AFTER WEEK 15_________________Won__Lost__Tied__Percent__Pts Fr__Pts Ag AFC West_______San Diego______10___5_____0_____0.666____418_____331 AFC West_______Oakland________9____5_____0_____0.642____388_____315 AFC West_______Kansas_________7____8_____0_____0.466____325_____345 AFC West_______Denver_________6____9_____0_____0.4______314_____411 AFC East_______Miami__________11___4_____0_____0.733____482_____248 AFC East_______NY Jets________10___4_____0_____0.714____415_____293 AFC East_______New England____8____7_____0_____0.533____417_____342 AFC East_______Buffalo________7____8_____0_____0.466____348_____401 AFC North______Pittsburgh_____11___3_____0_____0.785____434_____285 AFC North______Cleveland______6____7_____0_____0.461____293_____391 AFC North______Cincinnati_____5____9_____0_____0.357____345_____411 AFC North______Baltimore______2____11____0_____0.153____220_____416 AFC South______Indianapolis___10___4_____0_____0.714____410_____275 AFC South______Tennessee______9____6_____0_____0.6______389_____383 AFC South______Jacksonville___4____10____0_____0.285____319_____393 AFC South______Houston________5____9_____0_____0.357____337_____447
(These "tips" started off as ones that I used to make it a bit easier in managing my own tournaments. Now, I am looking at other types of tournaments and scoring systems by all you innovative TO's out there. So, if you have a unique scoring system that you would like me to have a go at automating, please let me know and I'll be pleased to try and assist. Any queries on the above I'll be glad to respond to of course. Please note that I use Excel 2003, so there may be variation in the menu instructions if you have other versions of Excel)
in the last issue I complained that there was not a good way of formatting tables in Conquer Club code (phpBB apparently) and I proposed a macro to give some readability to tables.
Well I was wrong and many thanks to agentcom for showing me the light
There are tags similar to HTML that allow row and column separators and these can be put directly into an Excel table as agentcom showed me:
[table] First column (filled all the way down)[tr][td] Second column: <Data> Third:[/td][td] Fourth: <Data> Fifth:[/td][td]<Continue alternating between data and the above code until the last column> Last column:[/td][/tr][/table]
"td" and "/td" are the column separators and "tr" and "/tr" the row separators. To put the table into a thread, simply copy the whole table and paste into the thread.
An alternative is to put the above coding into a macro so that you can specify the rows and columns that you want to make into a table:
Sub FormLines() ' ' ' FormLines recorded 22/04/2013 by DaveH ' Dim iRowStart As Integer 'Table start row Dim iRowFinish As Integer 'Table finish row Dim iColStart As Integer 'Table start column Dim iColFinish As Integer 'Table finish column
'Enter the table parameters iRowStart = InputBox("Enter the Starting Row Number:") iRowFinish = InputBox("Enter the Finishing Row Number:") iColStart = InputBox("Enter the Starting Column Number:") iColFinish = InputBox("Enter the Finishing Column Number:")
For i = iRowStart To iRowFinish If i = iRowStart Then Cells(i, iColFinish + 2).Value = "[table][tr][td]" 'note that the data is written into the column 2 positions to the right of the data table Else Cells(i, iColFinish + 2).Value = "[tr][td]" End If For k = iColStart To iColFinish - 1 Cells(i, iColFinish + 2).Value = Cells(i, iColFinish + 2).Value & Cells(i, k).Value & "[/td][td]" Next k Cells(i, iColFinish + 2).Value = Cells(i, iColFinish + 2).Value & Cells(i, k).Value & "[/td][/tr]" Next i Cells(i, iColFinish + 2).Value = Cells(i, iColFinish + 2).Value & "[/table]" End Sub
This puts the code into one column:
Click image to enlarge.
Either of these methods puts an orderly table into your thread:
Winners:
Total inc R26:
R27 Wins:
Total
Asbks
19
19
Bruceswar
17
1
18
Wendt100
17
1
18
Clacie
16
1
17
Wizz00
16
1
17
Blindman30
15
15
Marshallbobby
15
15
Aussie02
14
14
Frank_tank
13
1
14
However there are blank lines above your table due to the separate lines used. An alternative macro that puts all the code into a single cell to get rid of these blank lines is as follows.
Sub FormLines() ' ' ' FormLines recorded 22/04/2013 by DaveH ' Dim iRowStart As Integer 'Table start row Dim iRowFinish As Integer 'Table finish row Dim iColStart As Integer 'Table start column Dim iColFinish As Integer 'Table finish column Dim A As String
'Enter the table parameters iRowStart = InputBox("Enter the Starting Row Number:") iRowFinish = InputBox("Enter the Finishing Row Number:") iColStart = InputBox("Enter the Starting Column Number:") iColFinish = InputBox("Enter the Finishing Column Number:")
For i = iRowStart To iRowFinish If i = iRowStart Then A = "[table][tr][td]" 'note that the data is written into the column 2 positions to the right of the data table Else A = A & "[tr][td]" End If For k = iColStart To iColFinish - 1 A = A & Cells(i, k).Value & "[/td][td]" Next k A = A & Cells(i, k).Value & "[/td][/tr]" Next i Cells(iRowStart, iColFinish + 2).Value = A & "[/table]" End Sub
This then gets rid of the leading spaces.
Winners:
Total inc R26:
R27 Wins:
Total
Asbks
19
19
Bruceswar
17
1
18
Wendt100
17
1
18
Clacie
16
1
17
Wizz00
16
1
17
Blindman30
15
15
Marshallbobby
15
15
Aussie02
14
14
Frank_tank
13
1
14
GreenBaize
13
1
14
Herus
13
1
14
So perhaps there are additional phpBB codes that we could also put to use - perhaps someone out there can feedback more information? And thanks again agentcom!
(These "tips" started off as ones that I used to make it a bit easier in managing my own tournaments. Now, I am looking at other types of tournaments and scoring systems by all you innovative TO's out there. So, if you have a unique scoring system that you would like me to have a go at automating, please let me know and I'll be pleased to try and assist. Any queries on the above I'll be glad to respond to of course. Please note that I use Excel 2003, so there may be variation in the menu instructions if you have other versions of Excel)
#39, Issue 95 - More uses for the Excel VLookup function.
In one of my recent tournaments I have teams of three playing a combination of singles doubles and triples. With different players winning, I needed a way of linking the wins back to the team. The same type of issue arose in a tournament of doubles in which I am using "Tour Stats" for the results. In this I need to order the players in team order rather than alphabetical player order.
One solution is to have two lookup tables. One has the teams with a reference number and the other is the alphabetical list of players names with the team reference number. i.e. As a reminder on using VLookup, the process is to copy the table of 4 columns and, in Excel, select Insert/Name/Define and type in the name to be used; in this case "Teams" (note that the area selected is defined in the bottom window).
The next table is the alphabetical list of all the players with their team number in the next column. i.e. Same procedure, naming this table "Players". Now, if I have a winning player, or from the Tour Stats data a list of players, I can get the team names from the player's names by using the function VLookup(Player_Name_Cell, Players, 2, false) and then, from this team number, the captain of the team or all three or two players using VLookup(above_cell, Teams, 2, false) for the first player's name; VLookup(above_cell, Teams, 3, false) for the second and VLookup(above_cell, Teams, 4, false) for the third.
(These "tips" started off as ones that I used to make it a bit easier in managing my own tournaments. Now, I am looking at other types of tournaments and scoring systems by all you innovative TO's out there. So, if you have a unique scoring system that you would like me to have a go at automating, please let me know and I'll be pleased to try and assist. Any queries on the above I'll be glad to respond to of course. Please note that I use Excel 2003, so there may be variation in the menu instructions if you have other versions of Excel)
#40, Issue 96 - Extracting terminator data - updated.
In issue 84 I wrote some code to extract the order of terminations and highlighted if sequential terminations were made in the same round. Following that article I was asked if I could alter the code to cater for scores according to the order of termination.
It turns out that very few additions are required for this. However, as with all revised programmes, I updated and simplified it. I like to think that it was based upon the knowledge gained in writing the original but more accurately it was because I could not remember how my original worked.
Anyway, the following is hopefully a universal programme that extracts the termination data and assigns points to the terminator and terminee (if that is a word!).
The points are held in an 7 x 2 array and are pre-programmed with values. The first line holds the points for the terminator (as wanted for the order of terminations) and the second for the person terminated, which can always be made negative or not used at all. An Excel spreadsheet has the game number in column 2 and the data from the log (either the Termination Log data or the Game Log itself) is written into column 3 - as shown below: After the macro is run to summarise the data, the result is as follows: The next game data can then be written below the summary information as shown below: The code itself works as follows. It scans down the log data to find the last line of data iFinishRow and then scans upwards in column 2 to find the game number iStartRow. We can then scan down column 3 between iStartRow and iFinishRow to check each line for the word "terminated". If it is not found in a row, that row is cleared and the next row scanned. When terminated is found, the two names are extracted and written into columns 4 and 6 and the appropriate scores from the array written into columns 5 and 7. That's it really. There are a few bits of code used to extract the names - note that if a player's name has a space in it then only the first word of that name is extracted. Further explanation is in the following code:
iScoreIndex = 1 'pointer to first line of array k = 0 'flag for "eliminated found" b = "" 'name of player that has "eliminated" another
'Find the end of the game log text iFinishRow = iStartRow While Cells(iFinishRow, iStartCol).Value <> "" iFinishRow = iFinishRow + 1 Wend iFinishRow = iFinishRow - 1
'Find start of the block of data iStartBlock = iFinishRow While Cells(iStartBlock, iStartCol - 1).Value = "" iStartBlock = iStartBlock - 1 Wend
iPlaceRow = iStartBlock For i = iStartBlock To iFinishRow k = 0 'check for "eliminated" For j = 20 To 50 If Mid(Cells(i, iStartCol).Value, j, 10) = "eliminated" Then 'Move it up to the next line used for summary Cells(i, iStartCol).Select Selection.Cut Cells(iPlaceRow, iStartCol).Select ActiveSheet.Paste m = 1 'Check for the end of the name (space after "terminated") c = Mid(Cells(iPlaceRow, iStartCol).Value, 23 + m, 1) While c <> " " And m < 15 m = m + 1 c = Mid(Cells(iPlaceRow, iStartCol).Value, 23 + m, 1) Wend
b = Mid(Cells(iPlaceRow, iStartCol).Value, 23, m) Cells(iPlaceRow, iStartCol + 1).Value = b Cells(iPlaceRow, iStartCol + 2).Value = iScore(iScoreIndex, 1)
'check for the losing name (from end of "terminated" to "from") While c <> "from" m = m + 1 c = Mid(Cells(iPlaceRow, iStartCol).Value, 33 + m, 4) Wend
b = Mid(Cells(iPlaceRow, iStartCol).Value, j + 11, 21 + m - j) Cells(iPlaceRow, iStartCol + 3).Value = b Cells(iPlaceRow, iStartCol + 4).Value = iScore(iScoreIndex, 2)
'increment next line to paste termination data iPlaceRow = iPlaceRow + 1
k = 1 'flag to say "terminated" was found End If Next j If k = 0 Then
'if flag = 0 then clear line Cells(i, iStartCol).Select Selection.Clear
End If Next i
End Sub
Next issue will have the summarise coding and a reminder of how to link buttons to the macros.
(These "tips" started off as ones that I used to make it a bit easier in managing my own tournaments. Now, I am looking at other types of tournaments and scoring systems by all you innovative TO's out there. So, if you have a unique scoring system that you would like me to have a go at automating, please let me know and I'll be pleased to try and assist. Any queries on the above I'll be glad to respond to of course. Please note that I use Excel 2003, so there may be variation in the menu instructions if you have other versions of Excel)
Last edited by DaveH on Thu Sep 26, 2013 1:12 pm, edited 7 times in total.
In the previous edition (#96) I wrote some code to extract termination data and assign points according to the order of terminations in tournaments of this type. In this article I will go through the code used to summarise these points.
When the round or series of terminator games has completed, you end up with something like:
Click image to enlarge.
The terminate data has been summarised and you get a list of terminators scores next to the list of terminees with any points they have.
The "Total" button runs a macro that summarises these lists of scores. The process is straight forward; 1 list both columns together; 2 sort by player number; 3 if a player's name is duplicated, add the scores together on one and delete the other; 4 repeat until there are no duplicate players' names; 5 sort by total.
1 If the number of players and totals in the list is not known, we need to check on the number so we scan down the list until we have a blank cell. Something like; istart=row number of first player in the list Continue to loop, incrementing the cell row variable until there is no player's name in the cell indicating the sell after the last one in the list; i=istart ilast=istart+1 While Cells(ilast,column number of first cell).value<>"" ilast=ilast+1 Wend ilast=ilast-1 Now we have istart as the first cell row and ilast as the last cell row. This code is as follows:
Sub Total() istart = 5 icolumn=7 i = istart ilast = istart + 1 While Cells(ilast, icolumn).Value <> "" ilast = ilast + 1 Wend ilast = ilast - 1 End Sub
We can then copy the first two columns and paste it in a blank column and then copy and paste the second two columns below it. This code is as follows:
'Select the first two columns Range(Cells(istart, icolumn), Cells(ilast, icolumn + 1)).Select Selection.Copy
'Paste special into the results column Cells(istart, icolumn + 5).Select Selection.PasteSpecial Paste:=xlPasteValues
'Repeat for the second column Range(Cells(istart, icolumn + 2), Cells(ilast, icolumn + 3)).Select Selection.Copy
'Paste special into the results column Cells(ilast + 1, icolumn + 5).Select Selection.PasteSpecial Paste:=xlPasteValues
2 Sort by player's names. Let me show you an easy way of doing this using the "Record New Macro" Excel command. Simply select Tools/Macro/Record new macro. Leave the name as it is (Macro1) and start the recording. Select the two columns and sort by player's names and then stop the macro. Select Tools/Macro/Macros. Select Macro1 and edit - you get the following code:
Sub Macro1() ' ' Macro1 Macro ' Macro recorded 07/03/2013 by DT Homer ' Range("L5:M48").Select Selection.Sort Key1:=Range("L5"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub
We can now add our own range selection and copy the Macro1's sort information as follows:
'New Range definition inewlast=ilast + ilast + 1 - istart ' this is the row position of the last name in the new column Range(Cells(istart, icolumn + 5), Cells(inewlast, icolumn + 6)).Select
3 Look for duplicate names. Something like a For/Next loop from the first to last player checking on duplicate names. For i=istart to inewlast -1 a= cells(i, icolumn+5).Value b= cells(i+1, icolumn+5).Value If a=b then there is a duplicate name
End if Next i
4 When a duplicate is found, the first score is increased by the second and the second line deleted. cells(i, icolumn+6).Value=cells(i, icolumn+6).Value+cells(i+1, icolumn+6).Value Range(Cells(i+1, icolumn+5), Cells(i+1, icolumn + 6)).Select Selection.ClearContents
For i = istart To inewlast - 1 a = Cells(i, icolumn + 5).Value b = Cells(i + 1, icolumn + 5).Value If a = b Then Cells(i, icolumn + 6).Value = Cells(i, icolumn + 6).Value + Cells(i + 1, icolumn + 6).Value Range(Cells(i + 1, icolumn + 5), Cells(i + 1, icolumn + 6)).Select Selection.ClearContents End If Next i
The clever bit is to resort and repeat until there are no more duplicates. We use a flag (j) to set when there is a duplicate and a While/Wend loop until the flag is not set.
5 The final part is to sort into the Score order and link the macro to a button. All the final code is as follows.
'Select the first two columns Range(Cells(istart, icolumn), Cells(ilast, icolumn + 1)).Select Selection.Copy
'Paste special into the results column Cells(istart, icolumn + 5).Select Selection.PasteSpecial Paste:=xlPasteValues
'Repeat for the second column Range(Cells(istart, icolumn + 2), Cells(ilast, icolumn + 3)).Select Selection.Copy
'Paste special into the results column Cells(ilast + 1, icolumn + 5).Select Selection.PasteSpecial Paste:=xlPasteValues
'New Range definition inewlast = ilast + ilast + 1 - istart j = 1 While j <> 0 j = 0 Range(Cells(istart, icolumn + 5), Cells(inewlast, icolumn + 6)).Select Selection.Sort Key1:=Range("L5"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal For i = istart To inewlast - 1 a = Cells(i, icolumn + 5).Value b = Cells(i + 1, icolumn + 5).Value If a = b And a <> "" Then Cells(i, icolumn + 6).Value = Cells(i, icolumn + 6).Value + Cells(i + 1, icolumn + 6).Value Range(Cells(i + 1, icolumn + 5), Cells(i + 1, icolumn + 6)).Select Selection.ClearContents j = j + 1 End If Next i Wend Stop Range(Cells(istart, icolumn + 5), Cells(inewlast, icolumn + 6)).Select Selection.Sort Key1:=Range("M5"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub
(These "tips" started off as ones that I used to make it a bit easier in managing my own tournaments. Now, I am looking at other types of tournaments and scoring systems by all you innovative TO's out there. So, if you have a unique scoring system that you would like me to have a go at automating, please let me know and I'll be pleased to try and assist. Any queries on the above I'll be glad to respond to of course. Please note that I use Excel 2003, so there may be variation in the menu instructions if you have other versions of Excel)
#42, Issue 98 - Coding for using the Tournament Statistics facility to get results data.
At the time of writing this, Greenoaks is recruiting for his "The Amazing Race 2013 [TPA3]" which follows the interesting format of the tournament he rescued a couple of years ago from CDL. In these there is a 15 minute penalty for every round taken to complete a game, with the winner deducting an hour and the loser adding an hour to their total times. The overall winner is therefore the player who takes the lowest amount of time to complete all games.
To automate the scoring for this, the "Tour Stats" facility comes into its own. The "Results" tab gives the Round information as well as the winner and loser:
Click image to enlarge.
Note that some of the player's names have suffixes which are their team number so that, in a multi-team game, you can see which team was eliminated first (thanks to sempaispellcheck for explaining this to me!). More of this later.
This information can be copied to a spreadsheet:
Click image to enlarge.
It is then straight forward to add columns of the times taken, according to the formulae; Winning player's time = Round Number * 0.25 - 1 hours and Losing player's time = Round Number * 0.25 + 1 hours. These formulae can be written into the next columns next to the team name:
Click image to enlarge.
In this the column "G" has the formula Round Number * 0.25 (i.e. G3=D3*0.25). Column H has the winners names; column I has Column G - 1; column J has the losers names and column K has Column G+1.
At the end of a full round you will then have two sets of columns that can be copied into a single column and sorted to get similar names next to each other. However, as you can see from the way CC specifies the suffixes, the names of the team members are not consistent and some names have "undefined" appended to them where an unknown character was used. Firstly let us simplify the team names by extracting the first player's name. The formula is to find the comma and use that as a marker for the end of the first player's name. The code for this is:
However this will create an error when you only have one name in the column. To get around this the ISERROR function can be used. This is TRUE if there is an error and FALSE otherwise. Therefore we can test for an error first; if so take the single name; otherwise do the Left/Find function. This code is then:
This will then deliver a set of single names associated with the winners and the losers in columns L and M respectively:
Click image to enlarge.
There are still some differences in the names though but you can get around this though by creating a lookup table of the team members next to their team name. Sort them by player name and use a Vlookup function without the "false" definition which makes it find the name that is closest and not an exact match. The two columns of team names for winners and losers with their scores can be sorted into order to simplify the final additions required (which can always be done using a macro!).
(These "tips" started off as ones that I used to make it a bit easier in managing my own tournaments. Now, I am looking at other types of tournaments and scoring systems by all you innovative TO's out there. So, if you have a unique scoring system that you would like me to have a go at automating, please let me know and I'll be pleased to try and assist. Any queries on the above I'll be glad to respond to of course. Please note that I use Excel 2003, so there may be variation in the menu instructions if you have other versions of Excel)
#43, Issue 99 - Macros and coding for updating a tournament such as the "7-Up" one.
I recently rescued the 7-Up [TPA3} tournament from peanutman, who in turn rescued the original TPA2 one from barterer2002 who designed it. It poses several interesting problems with automating it as I will explain.
Like many tournament team games it has team members participating in different formats of games. In this case the seven team members 1 - 7 have a quad team (1, 2, 3, 4) a triples team (5, 6, 7), three doubles teams (1, 2 and 3, 4 and 5, 6) and a singles player (7). All play on 1 predetermined map, with the singles player playing the best of 5 games. Points are awarded for winning and additional points for winning in less than 10 rounds.
The teams I arranged into a worksheet as shown:
Click image to enlarge.
I made it a lookup table called "Teams". The team name is followed by the captain and players in the order in which they will play the various formats of the game. Into the thread I posted the team information using the following code in column M for the first team:
="[b]"&C3&": "&D3&"[/b]: "&E3&", "&F3&", "&G3&", "&H3&", "&I3&", "&J3&" and "&K3
which appears as TFFS: shoop76: Bigroo4601, aaronvollrath, seechster, patrickaa317, tintrumpet, spitemalice and shoop76.
The maps I arranged as follows: These are also made into a lookup table called (surprisingly ) "Maps". Note my reminders with yellow backgrounds (which sometimes do not work as some of you players might have noticed )
For the matchups which are as a round robin, the following (well used) table of matchups are used:
Click image to enlarge.
This may look complex but if you scan across the first set of numbers you will see that "1" stays on the same place and the other numbers "rotate" by one in an anticlockwise direction. For the 18 teams it extends to 17 sets of games before it starts repeating. This ensures that every team (as numbered) play against every other team.
I have created 6 sets of similar sets of numbers to account for the different sets of games (Quads, Trips, Doubs 1, Doubs2, Doubs3, Singles). On the "Games" worksheet I have created the matchups as follows. Look at the set of matchups for Round 3:
Click image to enlarge.
For round 3 the matchups are copied as per the table in column labelled "3". There are then lookup instructions from the Team lookup chart to bring the 2 team titles and the players into the table. Note that the map number is used to bring the relevant map name from the"Map" lookup table.
There are 6 sets of data for the 6 types of games - all we now need are the joining instructions - see next issue!
(These "tips" started off as ones that I used to make it a bit easier in managing my own tournaments. Now, I am looking at other types of tournaments and scoring systems by all you innovative TO's out there. So, if you have a unique scoring system that you would like me to have a go at automating, please let me know and I'll be pleased to try and assist. Any queries on the above I'll be glad to respond to of course. Please note that I use Excel 2003, so there may be variation in the menu instructions if you have other versions of Excel)
#44, Issue 100 - Second part of updating tournaments such as the "7-Up" on.
This article is a continuation of last issue's piece about the way I am managing the 7-up tournament using Excel. I created two lookup tables of Teams and Maps and a table of the round robin matchups.
These were brought together on a seperate worksheet as shown:
Click image to enlarge.
For round 3 the matchups are copied as per the table in column labelled "3". There are then lookup instructions from the Team lookup chart to bring the 2 team titles and the players into the table. Note that the map number is used to bring the relevant map name from the "Map" lookup table.
There are 6 sets of data for the 6 types of games and the games can be created in sets of 9 and the numbers pasted into the spreadsheet (as shown for the part of the spreadsheet with the five sets of singles game numbers):
Click image to enlarge.
I have explained before that, when creating games and pasting the game numbers into the spreadsheet, I paste the highest number and then the following cells have the formula =(Previous cell number -1) so that, if the games were created sequentally, all I had to input was the original number. However, since there are usually other games created whilst I am creating mine, I have to check the sequence and amend as necessary. Rather than leaving the formula in the game number cells I always (if I remember!) copy and Edit/Paste Special/Values back into the cells so that if one game number changes it does not affect other game numbers.
Now all is required is to put in the formulae to invite the players. For the quads this is;
' select the "Games" spraedsheet Sheets("Games").Select
'select the row number of the last table's map reference number cell - this we will call LineStart linestart = InputBox(Prompt:="Last Row Number", _ Title:="Enter the last row number", Default:="Enter Previous Row Number of the Round Number")
'select the map number in column "B" and Referenced Map in column "C"and Range("B" & linestart, "E" & linestart).Select Selection.Copy
'add irowdepth +3 to LineStart (depth of the table) for a variable we can call LineDest linedest = linestart + irowdepth + 3
'Select and paste the two values to the destination cell Range("B" & linedest, "E" & linedest).Select ActiveSheet.Paste
'get the previous round number and increment it iRoundNumber = Cells(linedest, 2).Value + 1
'paste this value into the destination cell - the VLOOkup function that we pasted next to it will automatically update the map to the new value Cells(linedest, 2).Value = iRoundNumber
'get the previous map number and increase it Cells(linedest, 3).Value = Cells(linedest, 3).Value + 6
'select the Round Robin sheet Sheets("Round Robin").Select
'Look for the next round number (starting at column 8 and incremented by 3 for the next table i = 3 While Cells(1, i).Value <> iRoundNumber i = i + 3 Wend
'select the table below this round number Range(Cells(3, i), Cells(2 + irowdepth, i + 1)).Select Selection.Copy
' go back to the games spreadsheet Sheets("Games").Select
'select the next table start position Cells(linedest + 2, 2).Select
'paste the values into the table not the formulae! Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False
'select the VLookup function from the previous table... Range(Cells(linestart + 2, 4), Cells(linedest - 2, 15)).Select
'copy it.. Selection.Copy
'and paste in the first row of the second table Cells(linedest + 2, 4).Select ActiveSheet.Paste
'put the next map numbers in For i = 1 To 5 Cells(linedest + i * 12, 3).Value = Cells(linedest, 3).Value + i Next i End Sub
I then copy and paste the invitation code from the previous round and all that is left to do is send the invites.
For updating the thread, rather than write separate code I have simply copied the invitation code into a separate spreadsheet; find and replace the "tot" with "game"; take out the password and team number and then paste into the tournament thread.
(These "tips" started off as ones that I used to make it a bit easier in managing my own tournaments. Now, I am looking at other types of tournaments and scoring systems by all you innovative TO's out there. So, if you have a unique scoring system that you would like me to have a go at automating, please let me know and I'll be pleased to try and assist. Any queries on the above I'll be glad to respond to of course. Please note that I use Excel 2003, so there may be variation in the menu instructions if you have other versions of Excel)
#45, Issue 101 - Indicating results in a poker simulation tournament
I rescued crazymilkshake5's CCSOP TPA2 tournament some months ago and it had the novel concept of recreating the rules of Poker. Players select a map and settings and then all players bid according to the rules of poker (apart from not being able to communicate in order) before setting up the game - the winner taking the chips. From the comments of the guys playing in this tournament, I then decided to have a TPA3 Poker tournament but to try and iron out some of the problems I encounted during the running of the first one.
The main problem was communicating between players for their maps and settings and their bids. The delay in getting map information was an easy one to solve by having all entrants sign up with their map and settings ahead of the start of the games. Communication on bids was difficult to manage as a bid might affect all the other players' bids, so needed a pm updating the thread with the changes, so each game tended to take quite a long time and effort to organise.
I started off with an Excel table of a particular poker game.
Click image to enlarge.
This has all the relevant information; list of players on that table; the map used (as looked up from a table of the players and their maps and settings; their bids and chip positions. The formula used is =VLOOKUP(VLOOKUP(B5,Numbers,2,FALSE),Maps,3,FALSE)) where B5 is the players name and the first lookup is the number that the player has in the table of maps and settings. i.e there is a "Numbers" table of players in alphabetical order together with the row number of their map details in the "Maps" table which has all the players, maps and settings as displayed on the thread. To the right of the table details is the pm text that was sent to all 6 players to initiate the game, the text of the map details being copied from the game table. The button accesses a macro which summarises the table in a form that can be displayed in CC. The coding is:
' ' ' FormLines recorded 22/04/2013 by DaveH ' Dim iRowStart As Integer 'Table start row Dim iRowFinish As Integer 'Table finish row Dim iColStart As Integer 'Table start column Dim iColFinish As Integer 'Table finish column Dim A As String
For i = iRowStart To iRowFinish If i = iRowStart Then A = "[table][tr][td]" 'note that the data is written into the column 2 positions to the right of the data table Else A = A & "[tr][td]" End If For k = iColStart To iColFinish - 1 A = A & Cells(i, k).Value & "[/td][td]" Next k A = A & Cells(i, k).Value & "[/td][/tr]" Next i Cells(iRowStart, iColFinish + 2).Value = A & "[/table]" End Sub
This macro creates the code that is partially visible to the left of the button. This displays the table in the CC thread as: This table is repeated for all the games, with the macro information altered to represent that table only (i.e. the macro number and the variables iRowStart and IRowFinish changed for each table).
Now for the clever bid The cell with the table information is referenced from a different sheet in the spreadsheet containing the rest of the thread text. For instance the tournament thread editing data is copied to the Excel sheet and each table is appended in the following format:
="[spoiler=Table 1 - Game 1]" & Games!I2&"[/spoiler]"
i.e Games!I2 references the cell with the table code in it and the rest of the line of code puts this into a "spoiler" with a title.
The sequence of operations on receiving a bid is therefore as simple as I can possibly make it. I receive a pm with a bid which is copied to the Excel game sheet, say it is "Call". This looks up the stake value (say 100) and adjusts the table values accordingly. I then click the button (to update the table data) and copy the sheet of the tournament thread (which now has the amended table data in a line of it) and replace the Poker thread, so updating the visible information.
All I need now is something to prompt some of the players into a more immediate response!
(These "tips" started off as ones that I used to make it a bit easier in managing my own tournaments. Now, I am looking at other types of tournaments and scoring systems by all you innovative TO's out there. So, if you have a unique scoring system that you would like me to have a go at automating, please let me know and I'll be pleased to try and assist. Any queries on the above I'll be glad to respond to of course. Please note that I have just moved to Excel 2007, so there may be variation in the menu instructions if you have other versions of Excel - and a few hiccups as I get used to it - as some of my tournament players have experienced, unfortunately )
#46, Issue 102 - Using pictures to illustrate your tournament results
Or Tic-Tac-Toe as it is referred to across the pond and elsewhere. This is a tournament I started when, following on from the last article, I realised that you could also manipulate images in the same way as tables and/or other text. My aim was to create a tournament which was so easy to update that once I had input a change, it would be represented on the main tournament thread.
For instance, the initial idea was to play Tic-Tac-Toe using maps; for instance: A player chooses a map and you play that map and the winner puts his "O" or "X" in the space of that map. The other player than chooses a map and so on until a line is made, or the majority of spaces are won. Obviously I have divided the grid into 9 separate images and created an additional three images of "Game In Progress" (or "Game"), "X" and "O". On a spreadsheet I have a simple 3 x 3 grid that represents the game:
Game
X
X
CCU
O
Pelo
Epic
USA
NYC
The images are held in a lookup table which I called "Maps";
AoR2 [img]http://img11.imageshack.us/img11/2396/aor2s.jpg[/img] Arms [img]http://img43.imageshack.us/img43/4339/armss.jpg[/img] CCU [img]http://img839.imageshack.us/img839/7236/ccuk.jpg[/img] Epic [img]http://img713.imageshack.us/img713/4940/epicip.jpg[/img] Game [img]http://img7.imageshack.us/img7/3066/gameinprogress.jpg[/img] NA [img]http://imageshack.us/a/img402/5643/92803801.jpg[/img] NYC [img]http://img402.imageshack.us/img402/18/nycrc.jpg[/img] O [img]http://img191.imageshack.us/img191/2585/70602248.jpg[/img] Pelo [img]http://img825.imageshack.us/img825/6818/peloy.jpg[/img] USA [img]http://img818.imageshack.us/img818/9204/usasw.jpg[/img] World [img]http://img199.imageshack.us/img199/324/worldgb.jpg[/img] X [img]http://img842.imageshack.us/img842/1497/13727152.jpg[/img]
Note that the first (reference) column has to be in alphabetical order. Now I just need to lookup the correct image from the simple 3 x 3 table using a 3 x 3 table with the following code in each of the 9 positions:
where "Games" is the name of the sheet containing my 3 x 3 reference grid. Copy the complete Excel sheet to the tournament thread (wait a while...) and "Hey Presto" the above sets of images appear for each game! http://www.conquerclub.com/forum/viewtopic.php?t=191283
I'm sure this could be used to better effect, so any and all ideas welcome....
(These "tips" started off as ones that I used to make it a bit easier in managing my own tournaments. Now, I am looking at other types of tournaments and scoring systems by all you innovative TO's out there. So, if you have a unique scoring system that you would like me to have a go at automating, please let me know and I'll be pleased to try and assist. Any queries on the above I'll be glad to respond to of course. Please note that I have just moved to Excel 2010, so there may be variations if you have other versions of Excel)
#47, Issue 103 - Tables for 5-player tournaments in which every player plays each other player just once.
Way back in Issue 68, "Designing a 4-player tournament in which each player meets everyone else just once.", I showed how Chapcrap scheduled a 4-player tournament with 16 entrants in which everyone played each other only once. Well, after printing the following:
Which leaves us with the possibility that Chapcrap is really the mathmatical genius "mathtalk-ga" and perhaps he even knows how to uniquely schedule 5 player matches? ... If anyone out there does know how to uniquely schedule 5 and more player games, please let me know!
And, guess what shortly afterwards Chapcrap sent me the information about a 5-player tournament for 25 players in which each player plays everyone else only once.
So, how can you use this information in a tournament?
Well, what is required is to have the 5 by 5 sets of players by the game number with code to invite them into the game and code to print on the game thread and code to pick out the winner/scores - perhaps the games being terminators with points for each termination?
To start with, assign the players into a list next to their number in an Excel spreadsheet. Select this two-column array and calling it, say, "Player" (In Excel 2010 this is Formulas/Define Name to bring up a New Name screen) It appears as =Sheet1!$B$5:$C$29 in the "Name Manager". If you recreate the above table in spreadsheet; select the table and call it, say, "Table", then you can lookup sets of games and players as required. This appears as "=Sheet1!$E$5:$J$29".
For instance, to get player number 22, say, the formula "=LOOKUP(Cell that has 22 in it, Player, 2, FALSE)" gets the name from column 2 of the Player array into the cell with the formula in it. In my spreadsheet I have the two arrays as shown below: [/url]
The formulae that can then be used are shown below: [/url] For Round 1 you can select the column M5 to M26 and put it in a pm to all the players. The code that appears is :
If you copy these two lines they can be pasted into all the other 4 games in round 1 and then all 5 can be pasted across the 5 rounds - Excel upgrading the code appropriately.
For getting the results; if it is say a terminator game, then see the articles I have previously written for terminator scoring.
(These "tips" started off as ones that I used to make it a bit easier in managing my own tournaments. Now, I am looking at other types of tournaments and scoring systems by all you innovative TO's out there. So, if you have a unique scoring system that you would like me to have a go at automating, please let me know and I'll be pleased to try and assist. Any queries on the above I'll be glad to respond to of course. Please note that I have just moved to Excel 2010, so there may be variations if you have other versions of Excel)
#48, Issue 104 - Updated coding to extract terminator data from a finished game.
One requirement in an abandoned tournament I picked up Sonic Goes Back To School was that unique kills were specified as one requirement for a medal. This gives the added problem of working out the number of terminations by each player and also subtracting any duplicate terminations. To summarise; the number of terminations and points allocated to the order of terminations must be determined as well as the number of unique terminations. To get this information I have already written code to extract the necessary data. I use the "Terminator Points Summary" information. For instance:
2013-07-20 01:46:09 - freyme eliminated Nicho from the game in round - 1 2013-07-20 01:46:09 - Nicho lost 10 points in round - 1 2013-07-20 01:46:09 - freyme gained 10 points in round - 1 2013-07-20 22:21:37 - Ishihara eliminated mcshanester29 from the game in round - 1 2013-07-20 22:21:37 - mcshanester29 lost 15 points in round - 1 2013-07-20 22:21:37 - Ishihara gained 15 points in round - 1 2013-07-21 17:32:28 - dowian2 eliminated freyme from the game in round - 1 2013-07-21 17:32:28 - freyme lost 21 points in round - 1 2013-07-21 17:32:28 - dowian2 gained 21 points in round - 1 2013-07-21 17:50:03 - dowian2 eliminated mviola from the game in round - 1 2013-07-21 17:50:03 - mviola lost 20 points in round - 1 2013-07-21 17:50:03 - dowian2 gained 20 points in round - 1 2013-07-21 23:14:13 - Ishihara eliminated dowian2 from the game in round - 1 2013-07-21 23:14:13 - dowian2 lost 20 points in round - 1 2013-07-21 23:14:13 - Ishihara gained 20 points in round - 1
From this information we can discard all lines apart from those with the word "eliminated" in. This gives the following summary:
2013-07-20 01:46:09 - freyme eliminated Nicho from the game in round - 1 2013-07-20 22:21:37 - Ishihara eliminated mcshanester29 from the game in round - 1 2013-07-21 17:32:28 - dowian2 eliminated freyme from the game in round - 1 2013-07-21 17:50:03 - dowian2 eliminated mviola from the game in round - 1 2013-07-21 23:14:13 - Ishihara eliminated dowian2 from the game in round - 1
From this we can pick out the word before "eliminated" and the one after it to give the two players:
The first column are those players who have eliminated another (i.e. 1 termination each) and also points can be allocated according to the tournament instructions. In the case of "Sonic" they are 1 point for each termination plus points for the last player standing such that the total of points distributed is 13. For instance with 6 players the points are 1, 1, 1, 1, 9; for 5 players 1, 1, 1, 10 and for 4 players 1, 1, 11. In addition there are points allocated to the order of terminations and for Sonmic they are 1, 2, 3, 4, 5 for 6 players and 2, 3, 4, 5 for 5 players etc.
We therefore have, in the above game, points allocated as:
Thus, when I summarise several games I have three lists; Points, Terminations and a list of concatenated eliminations. This list can be sorted alphabetically and any duplicates are non-unique terminations. Therefore the number of unique terminations for each player are their total terminations minus any duplicate terminations. (These "tips" started off as ones that I used to make it a bit easier in managing my own tournaments. Now, I am looking at other types of tournaments and scoring systems by all you innovative TO's out there. So, if you have a unique scoring system that you would like me to have a go at automating, please let me know and I'll be pleased to try and assist. Any queries on the above I'll be glad to respond to of course. Please note that I have just moved to Excel 2010, so there may be variations if you have other versions of Excel)
#49, Issue 105 - Keeping your thread up-to-date straight from an Excel sheet.
The best aspect of using Challonge in your tournament thread is that, once you have updated the results in Challonge, the changes are immediately reflected in your tournament thread.
So, what are the other ways in which you can limit the time you have to spend after updating results in your spreadsheet in writing them into your thread?
The obvious one is to have the thread copied from your spreadsheet, so that, after updating your spreadsheet results, one of your spreadsheets with the thread copy on is also updated and can be copied directly to the thread. This sheet does not have to be the one that you update results on, but obviously linked to the results spreadsheet.
For instance, in my Noughts and Crosses tournament, as I wrote about in article 102, one page contains the complete tournament thread and the following is from another sheet showing each game. To reiterate this article, but from the perspective of creating a spreadsheet with all the information on it that can easily be written into your tournament thread I have the two opponents, one playing "O" and the other "X" and a record of their game choices. Opponents chose their map in turn. The game winner then has their "X" or "O" inserted into the game matrix. In the above example, Epitaph1 won the game on Feudal Epic and has his "O" inserted into the appropriate square and osman76 won on Arms Race and so has his "O" inserted. The current game is specified by putting "Game" into the appropriate matrix position which in this case is where Pelloponesian War is.
If you note that the game matrix is mapped out with a second matrix next to it that has references to "img". This information comes from a lookup table (called Maps) on a different Excel sheet that has the actual image references in it i.e. The table of images are then linked by the Excel lookup function i.e. The top left square has "=VLOOKUP(H59,Maps,2,FALSE)" which will lookup the map reference from cell H59, which is the top left (World) position on the game matrix. The sheet with the thread content then has a link to the image matrix i.e.
which, when the content of the sheet is copied to the thread itself, appears as:
Lines 73 to 77 contain the three by three sets of images. If I look at the content of line 74 it is "=Games!L59&Games!M59&Games!N59" which brings the three row image references from my "Games" sheet to the thread.
OK so I did not get the images to exactly the same size, but I have greatly simplified the time it takes to update the thread. Another advantage is that I can easily copy the game image matrix into the pm I send to the player whose turn it is next to give them a pictorial representation of the game progress so they can more easily select the map they next want to play on.
These "tips" started off as ones that I used to make it a bit easier in managing my own tournaments. Now, I am looking at other types of tournaments and scoring systems by all you innovative TO's out there. So, if you have a unique scoring system that you would like me to have a go at automating, please let me know and I'll be pleased to try and assist. Any queries on the above I'll be glad to respond to of course. Please note that I have just moved to Excel 2010, so there may be variations if you have other versions of Excel)
#50, Issue 106 - A macro for writing a round robin set of match-ups for any number of players.
In this article I have an Excel macro or subroutine that will create the match-ups for any number of players in a round robin tournament.
The way of creating match-ups is quite simple; for instance if you have 6 players you can write down 1 to 6 in a circular fashion to give your three round 1 matches: 1 plays 6 2 plays 5 3 plays 4 Now you can leave 1 where it is and "rotate" the others in an anti-clockwise direction for subsequent rounds: 1 plays 5 6 plays 4 2 plays 3 then:
1 plays 4 5 plays 3 6 plays 2 then:
1 plays 3 4 plays 2 5 plays 6 and finally:
1 plays 2 3 plays 6 4 plays 5
So, for coding we need to input the number of players, first defining the name of the subroutine
Sub RoundRobin() PlayerNo = InputBox(Prompt:="Enter the number of players", _ Title:="Enter the number of players", Default:=32)
This sets the variable "PlayerNo" to the input value. Of course it may be an odd number, in which case we must add another player that will give a "bye" to each player throughout the tournament. The test for an even number is if the integer of half the number is equal to the integer of half of (the number plus one)
Sub RoundRobin() PlayerNo = InputBox(Prompt:="Enter the number of players", _ Title:="Enter the number of players", Default:=32) If Int(PlayerNo / 2) <> Int((PlayerNo + 1) / 2) Then PlayerNo = PlayerNo + 1 End If
Cells(2, 3).Value = 1
For i = 1 To PlayerNo / 2 Cells(3 + i, 2).Value = i Cells(3 + i, 3).Value = PlayerNo + 1 - i Next i
For i = 1 To PlayerNo - 2 Cells(2, 3 + i * 3).Value = i + 1 For j = 1 To PlayerNo / 2 Select Case j Case 1 Cells(4, 2 + i * 3).Value = 1 Cells(4, 3 + i * 3).Value = Cells(5, 3 + (i - 1) * 3).Value Case 2 Cells(5, 2 + i * 3).Value = Cells(4, 3 + (i - 1) * 3).Value Cells(5, 3 + i * 3).Value = Cells(6, 3 + (i - 1) * 3).Value Case PlayerNo / 2 Cells(3 + PlayerNo / 2, 2 + i * 3).Value = Cells(2 + PlayerNo / 2, 2 + (i - 1) * 3).Value Cells(3 + PlayerNo / 2, 3 + i * 3).Value = Cells(3 + PlayerNo / 2, 2 + (i - 1) * 3).Value Case Else Cells(j + 3, 2 + i * 3).Value = Cells(j + 2, 2 + (i - 1) * 3).Value Cells(j + 3, 3 + i * 3).Value = Cells(j + 4, 3 + (i - 1) * 3).Value End Select Next j Next i
End Sub
You can then match your player list - perhaps even automating it using lookups!
These "tips" started off as ones that I used to make it a bit easier in managing my own tournaments. Now, I am looking at other types of tournaments and scoring systems by all you innovative TO's out there. So, if you have a unique scoring system that you would like me to have a go at automating, please let me know and I'll be pleased to try and assist. Any queries on the above I'll be glad to respond to of course. Please note that I have just moved to Excel 2010, so there may be variations if you have other versions of Excel)
Last edited by DaveH on Fri Mar 28, 2014 7:03 am, edited 3 times in total.
DaveH wrote:At the time of writing this, chapcrap was continuing his many tournaments with (the rather unseasonal!) Chap's Christmas: Terminator Robin Style and Chap's Easter: Terminator Robin Style. http://www.conquerclub.com/forum/viewtopic.php?t=155760 and http://www.conquerclub.com/forum/viewtopic.php?t=155924#p3417398 ... Which leaves us with the possibility that Chapcrap is really the mathmatical genius "mathtalk-ga" and perhaps he even knows how to uniquely schedule 5 player matches? ... If anyone out there does know how to uniquely schedule 5 and more player games, please let me know!
I know how to do that too. Do you still want the information?
This article, which is now one of my occasional ones rather than as a regular series, shows how Excel can be used to find out which players are missing from your tournament's invites list.
As some of you may know, I am presently doing a tournament of 111 rounds for 150 players, which means setting up 225 games every few days. This, I can easily automate using invites sent out by pm, but the lengthy process of following up missing invites by sending out either reminders or direct invites can be very frustrating.
However, there is a way in which this, too can be automated. The first step is to find the games which still have "invite" status.
Click image to enlarge.
This shows the first two games of the 59 that are missing players. Now copy the whole of the 59 games and paste text into column 2 of a blank spreadsheet of your tournament's Excel spreadsheet.
Click image to enlarge.
The first game missing a player is at row 2 and the 59'th is at row 266. Note that the game number is the first 8 letters of the second row of the game information and the player's name that has joined the game is in the last column of the pasted information, together with their rating.
On my tournament I have a sheet with all the invite information - that has been explained in previous articles.
Click image to enlarge.
This shows the first game of Round 13 and the last game of Round 15 (my missing players are all from rounds 13, 14 and 15) and includes the game number and the players that should both have joined their games.
Obviously, if I scan down the list of games that are missing players and find that game number on my spreadsheet, I can also find the names of both players. Having obtained both names, I can see which one (or both) are missing and create a list of names of players that I need to remind to join their games.
Sub CheckMissing() ' ' 'get the start and finish rows
'On the sheet with the full games information I need to specify the first game in round 13 and the last game in round 15 Sheets("Games").Activate iRowGStart = InputBox("Enter the first row number of the lowest round") iRowGFinish = InputBox("Enter the last row number of the highest round")
'I now need to specify the first and last record of the "found" set of games with missing players Sheets("Missing Invites").Activate iRowMStart = InputBox("Enter the first row number of the lowest missing game") iRowMFinish = InputBox("Enter the first row number of the highest missing game")
'Now I can scan down the missing players records For i = iRowMStart To iRowMFinish Step 4 'stepping 4 is because each record is on 4 lines A = Abs(Left(Cells(i + 1, 2).Value, 8)) ' this extracts the game number from the rest of the text
'Back to the games sheet to scan down to find the same game number Sheets("Games").Activate For j = iRowGStart To iRowGFinish B = Cells(j, 13).Value
If A = B And A <> "" Then 'The game numbers match so I can get the names of the two players C = Cells(j, 6).Value D = Cells(j, 9).Value
'Back to the missing invites sheet and copy down the two names Sheets("Missing Invites").Activate Cells(i + 1, 9).Value = C Cells(i + 2, 9).Value = D
'Extract the name of the first player to join (if one did!) E = Left(Cells(i + 1, 7).Value, Len(Cells(i + 1, 7).Value) - 4)
'If this name matched the first name on the list then the second name is the missing player's name If E = C Then Cells(i + 1, 10).Value = D 'If this name matched the second name on the list then the first name is the missing player's name ElseIf E = D Then Cells(i + 2, 10).Value = C 'If neither name matched then both player's names are missing Else Cells(i + 1, 10).Value = D Cells(i + 2, 10).Value = C End If
End If Next j Next i
'I now have a set of players names (most of whom will have missed all three games) 'There may be a player's name down 3 times but this does not matter when pasting into a pm 'sort them into alphabetical order, and I can paste into a pm and now send a reminder Range(Cells(iRowMStart + 1, 10), Cells(iRowMFinish + 2, 10)).Select Selection.Sort Key1:=(Cells(3, 10)), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal
End Sub
This produces a list of names of players who have not taken up their invites, so I can copy the list and copy into a pm reminding them to get take up their auto-invites.
Of course a player not responding to a pm may also not respond to this reminder, but at least I have a record of players that do not respond promptly!
#52, Issue 114 - Updating the "find" information to include in a thread.
In my Tips series, number 25 (Newsletter Issue 81) I discussed how to put into a thread a search instruction. i.e. as you would perform a "Find". Also, in Tips number 30 (Newsletter Issue 86) I went a bit further to define the various parts of the Find instruction as required in a search instruction. Since then, with all the improvements and additional types of possible game, these instructions no longer work.
Let me update some of the information in this article. The example I will use is to find Round 20 games in my "A Non-Classic Nelson Of Maps" tournament.
If I perform a Find for these games, I get, as the address;
Using this as an address does not work. However, the address has a "[" and a "]" in it and these must be replaced by the codes that represent them. i.e. "[" = %5B and "]"= %5D
Note that we now have the option of hiding reserved games and the additional flag is hr which can be N for No or Y for Yes.
If we now want to find active games then the game status (gs) now is gs[]=A, rather than gs=A as was previously. Once again these two brackets must be replaced for the statement to work.
The same is true with the Number of Players flag, np[]= and Game Type gt[]= which also have to have the brackets replaced. As are the following: The additional Polymorphic setting is represented as gt[]=P Zombie spoiles are represented as bc[]=5 Trench warfare introduces a new flag tw[]=Y Parachute reinforcements are ft[]=P
In article #30 I listed the various parts that make up the Find instruction. To this we need to add these following changes (in red):
gn= Game Number hr = Hide Reserved Games (N=no; Y=Yes) gs= Game Status (W=Waiting; A=Active; F=Finished} np= Number of Players (2, 3 etc) mp= Map (Map Number; not Name!) ty= Game Type (S (or blank) =Standard; C=Terminator; A=Assassin; D=Doubles; T=Triples; Q=Quads P=Polymorphic) it= Initial Troops (M=Manual) po= Play Order (F=Freestyle) bc= Type of Spoils (1 (or blank)=No Spoils; 2= Escalating; 3=Flat Rate; 4 = Nuclear 5 = Zombie) ft= Reinforcement Type (O for Adjacent; M=Unlimited; C=Chained; P=Parachute) wf= With Fog (Y = yes) tw= Trench Warfare (Y = yes) rl= Round Limit (20 etc) sg= Special Gamesplay (5 etc) pt= Private (y = Yes; T = Tournament) to=Tournament Title i.e.ContinentConquer%3A+World+Tour+%5BTPA2%5D lb= Game Label i.e. Round+2 p1= Player 1 p2= Player 2 p3= Player 3 p4= Player 4 order = sort order (=D, newest or A, oldest first) page= Page Number
#53, Issue 117 - Getting game data from a list of game numbers.
BGtheBrain posted the following query in the Tools and Tips forum:
Would it be possible to make a sheet formula where I could input a game # in column A, then column B would reflect Map Name, Column C would show player x points won/lost for each player?
The only way I know is to copy the game details and paste into Excel where you can then play with the data. However the wonderful MrBenn knows xml inside out and posted a routine which draws out the details directly from the website into Excel. I have added a few minor details into the code to meet BGtheBrain's requirements and the result is a routine that TO's can use to give them most of the data from a list of games. Not satisfied with that, MrBenn has updated and tidied the coding to extract all the data from a given tournament. However, you will have to wait for the next edition to receive that - and there may even be further "tweeks" to come!
j = 1 While j = 1 j = 0 For i = 2 To R - 1 A = Cells(i, 12).Value B = Cells(i + 1, 12).Value If A = B And A <> "" Then Cells(i, 13).Value = Cells(i, 13).Value + Cells(i + 1, 13).Value Cells(i + 1, 12).Value = "" Cells(i + 1, 13).Value = "" j = 1 End If
Cells(1, Col + 1).Resize(1, UBound(headers(2)) - LBound(headers(2)) + 1).Value = headers(2) Col = Col + UBound(headers(2)) - LBound(headers(2)) + 1
CurPage = 1 nextRow = 2 'Get tournament name from inputbox Deftourney = ActiveSheet.Name 'Deftourney = "7 Man Madness!" tourney = InputBox("Please enter a Conquer Club Tournament Name:", , Deftourney) If tourney = "" Then Exit Sub
Do APIpath = "http://www.conquerclub.com/api.php?mode=gamelist&to=" & tourney _ & "&names=Y&events=Y" If CurPage > 1 Then APIpath = APIpath & "&page=" & CurPage
Set xmlDoc = New MSXML2.DOMDocument
With xmlDoc 'Load the xml from CC API .async = False .validateOnParse = False .Load (APIpath)
'Get number of pages Set xNode = .FirstChild.SelectSingleNode("page") With xNode l = InStr(.Text, " ") CurPage = CInt(Left(.Text, l)) l = InStrRev(.Text, " ") MaxPage = CInt(Mid(.Text, l)) End With
'Get number of games in xml Set xNode = .FirstChild.SelectSingleNode("games") MaxGames = xNode.Attributes.getNamedItem("total").Text FileGames = xNode.ChildNodes.Length
Select Case gData(5) 'Game Type Case "S" gData(5) = "Standard" Case "C" gData(5) = "Terminator" Case "A" gData(5) = "Assassin" Case "P" gData(5) = "Polymorphic" Case "D" gData(5) = "Doubles" Case "T" gData(5) = "Triples" Case "Q" gData(5) = "Quads" Case Else 'Unknown Game Type End Select
Select Case gData(7) 'game state (W)aiting, (A)ctive or (F)inished Case "W" gData(7) = "Waiting" Case "A" gData(7) = "Active" Case "F" gData(7) = "Finished" Case Else 'Unknown Game State End Select
ReDim pData(1 To gData(4), 0 To 5) '(playerNo, (0=name, 1=Elim Order, 2=Kills, 3=Points, 4=Result, 5=Round) )
For p = 1 To gData(4) With .SelectSingleNode("players").ChildNodes(p - 1) pData(p, 0) = .Text pData(p, 4) = .Attributes.getNamedItem("state").NodeValue End With 'Players pData(p, 5) = .SelectSingleNode("round").Text Next p
ko = 1 For e = 1 To .SelectSingleNode("events").ChildNodes.Length With .SelectSingleNode("events").ChildNodes(e - 1) 'GameXML.childNodes(19).childNodes(e - 1)
If Right(.Text, 7) = " points" Then l = InStr(.Text, " ") p = CInt(Left(.Text, l)) pData(p, 3) = pData(p, 3) + _ CInt(Replace(Replace(Replace( _ Mid(.Text, l, Len(.Text)), _ "loses", "-"), "gains", "+"), "points", "")) ElseIf Right(.Text, 14) = " from the game" Then l = InStr(.Text, " ") p = CInt(Left(.Text, l)) If Not p = 0 Then 'not eliminated by neutral pData(p, 2) = pData(p, 2) + 1 End If t = .Text t = Mid(.Text, l, Len(.Text)) pData(CInt(Replace(Replace( _ Mid(.Text, l, Len(.Text)), _ "eliminated", ""), "from the game", "")) _ , 1) = ko ' pData(p,4) = Kill Order ko = ko + 1
' NEED TO ADD IN SOME STATS FORMULAE ' ALSO NEED TO CALCULATE WHICH ROUND AN ELIMINATION TOOK PLACE
End Sub Function UniqueList(Optional ListRange As Range)
If ListRange Is Nothing Then Set ListRange = Selection
Dim List As Variant List = ListRange.Value
With CreateObject("scripting.dictionary") For Each element In List If Not element = Empty Then c00 = .Item(element) Next UniqueList = .keys End With
End Function
You write in the list of game numbers that you wish to interrogate starting in cell A2. You run the routine "SumScores" and get the following data written into your spreadsheet: Across the top you get the number of players in the game; the type of game; map name; list of players' names; whether they won or lost; points gained or lost; number of kills; elimination order and the final round number. There is also a summary of the total points won or lost by each player.
Note that the game types are as follows: "S" = "Standard"; "C" = "Terminator"; "A" = "Assassin"; "P" = "Polymorphic"; "D" = "Doubles"; "T" = "Triples" and "Q"= "Quads".
With many thanks to MrBenn for allowing me to share his complex and powerful code and BGtheBrain for stimulating the creation of a routine that will make TOs' job in summarising tournament points far easier. (I can see complex scoring tournaments coming up!)
#54, Issue 118 - Getting a summary or tournament data into Excel.
Following on from the "Tips" article in the previous newsletter, MrBenn revamped his xml code in order to download a summary of all the games in a single tournament. As TO's will realise, this opens up a whole new world of possible scoring systems and could be used to cut update times for their tournaments. Firstly, the code:
CurPage = 1 nextRow = 2 'Get tournament name from inputbox Deftourney = ActiveSheet.Name 'Deftourney = "7 Man Madness!" tourney = InputBox("Please enter a Conquer Club Tournament Name:", , Deftourney) If tourney = "" Then Exit Sub
Do APIpath = "http://www.conquerclub.com/api.php?mode=gamelist&to=" & tourney _ & "&names=Y&events=Y" If CurPage > 1 Then APIpath = APIpath & "&page=" & CurPage
Set xmlDoc = New MSXML2.DOMDocument
With xmlDoc 'Load the xml from CC API .async = False .validateOnParse = False .Load (APIpath)
'Get number of pages Set xNode = .FirstChild.selectSingleNode("page") With xNode l = InStr(.Text, " ") CurPage = CInt(Left(.Text, l)) l = InStrRev(.Text, " ") MaxPage = CInt(Mid(.Text, l)) End With
'Get number of games in xml Set xNode = .FirstChild.selectSingleNode("games") MaxGames = xNode.Attributes.getNamedItem("total").Text FileGames = xNode.childNodes.Length
Select Case gData(5) 'Game Type Case "S" gData(5) = "Standard" Case "C" gData(5) = "Terminator" Case "A" gData(5) = "Assassin" Case "P" gData(5) = "Polymorphic" Case "D" gData(5) = "Doubles" Case "T" gData(5) = "Triples" Case "Q" gData(5) = "Quads" Case Else 'Unknown Game Type End Select
Select Case gData(7) 'game state (W)aiting, (A)ctive or (F)inished Case "W" gData(7) = "Waiting" Case "A" gData(7) = "Active" Case "F" gData(7) = "Finished" Case Else 'Unknown Game State End Select
ReDim pData(1 To gData(4), 0 To 5) '(playerNo, (0=name, 1=Elim Order, 2=Kills, 3=Points, 4=Result, 5=Round) )
For p = 1 To gData(4) With .selectSingleNode("players").childNodes(p - 1) pData(p, 0) = .Text pData(p, 4) = .Attributes.getNamedItem("state").nodeValue End With 'Players pData(p, 5) = .selectSingleNode("round").Text Next p
ko = 1 For e = 1 To .selectSingleNode("events").childNodes.Length With .selectSingleNode("events").childNodes(e - 1) 'GameXML.childNodes(19).childNodes(e - 1)
If Right(.Text, 7) = " points" Then l = InStr(.Text, " ") p = CInt(Left(.Text, l)) pData(p, 3) = pData(p, 3) + _ CInt(Replace(Replace(Replace( _ Mid(.Text, l, Len(.Text)), _ "loses", "-"), "gains", "+"), "points", "")) ElseIf Right(.Text, 14) = " from the game" Then l = InStr(.Text, " ") p = CInt(Left(.Text, l)) If Not p = 0 Then 'not eliminated by neutral pData(p, 2) = pData(p, 2) + 1 End If t = .Text t = Mid(.Text, l, Len(.Text)) pData(CInt(Replace(Replace( _ Mid(.Text, l, Len(.Text)), _ "eliminated", ""), "from the game", "")) _ , 1) = ko ' pData(p,4) = Kill Order ko = ko + 1
' NEED TO ADD IN SOME STATS FORMULAE ' ALSO NEED TO CALCULATE WHICH ROUND AN ELIMINATION TOOK PLACE
End Sub
Function UniqueList(Optional ListRange As Range)
If ListRange Is Nothing Then Set ListRange = Selection
Dim List As Variant List = ListRange.Value
With CreateObject("scripting.dictionary") For Each element In List If Not element = Empty Then c00 = .Item(element) Next UniqueList = .keys End With
End Function
When you run this macro from an Excel page it asks for the tournament name must be entered exactly as on the tournament list. You then get the following summary of the tournament.
Click image to enlarge.
You have game number that is linked directly to the game; Tournament Name; Map; Number of Players; Game Type; Round Limit; Game Status; A list of the Players' Names; The Order of Elimination for each Player; Number of Kills for each Player; Points and Result; Final Round Number, with a summary of the total number of games.
From this summary you can link to other pages with macros designed to provide the data that your particular tournament requires - and this is something I will be writing about as soon as I get the opportunity for a large-scale and complex tournament! Many thanks MrBenn and Very Well Done!
#55, Issue 119 - Getting a summary or tournament data into Excel.
This article resolves (I hope!) a problem I recently had keeping up with our clan's war games. We are in a couple of individual wars and three clan tournaments and I have been slow to update our game threads with the ongoing results.
Building on last edition's article of getting tournament data into a spreadsheet, I have added code to replicate the information about a clan war that I can use to update the thread. The best way of describing it is from the output:
Click image to enlarge.
On the columns following MrBenn's programme output (see Issue #118) is the clan winning the game, then a list our my clan's members; then a list of all the players with their clan and number of wins and loses. Finally is a list of code that is required to be pasted into the war thread. The output from this list is as follows:
Click image to enlarge.
OK, so the sequence is as follows: The macro is run and you input the war game heading. This must be entered exactly; the best way to do a search and copy the title directly. The data is transferred to the spreadsheet and you are asked for the mnemonic of the opposing clan (unfortunately, this cannot be derived from the title). You can then copy the data and paste directly into the war thread.
To customise the macro for your clan, you need to enter your clan mnemonic and list the clan members. The code for the macro is as follows and the positions of the required changes for your clan are easily identifiable,
CurPage = 1 nextRow = 2 'Get tournament name from inputbox Deftourney = ActiveSheet.Name 'Deftourney = "7 Man Madness!" tourney = InputBox("Please enter a Conquer Club Tournament Name:", , Deftourney) If tourney = "" Then Exit Sub
Do APIpath = "http://www.conquerclub.com/api.php?mode=gamelist&to=" & tourney _ & "&names=Y&events=Y" If CurPage > 1 Then APIpath = APIpath & "&page=" & CurPage
Set xmlDoc = New MSXML2.DOMDocument
With xmlDoc 'Load the xml from CC API .async = False .validateOnParse = False .Load (APIpath)
'Get number of pages Set xNode = .FirstChild.SelectSingleNode("page") With xNode l = InStr(.Text, " ") CurPage = CInt(Left(.Text, l)) l = InStrRev(.Text, " ") MaxPage = CInt(Mid(.Text, l)) End With
'Get number of games in xml Set xNode = .FirstChild.SelectSingleNode("games") MaxGames = xNode.Attributes.getNamedItem("total").Text FileGames = xNode.ChildNodes.Length
Select Case gData(5) 'Game Type Case "S" gData(5) = "Standard" Case "C" gData(5) = "Terminator" Case "A" gData(5) = "Assassin" Case "P" gData(5) = "Polymorphic" Case "D" gData(5) = "Doubles" Case "T" gData(5) = "Triples" Case "Q" gData(5) = "Quads" Case Else 'Unknown Game Type End Select
Select Case gData(7) 'game state (W)aiting, (A)ctive or (F)inished Case "W" gData(7) = "Waiting" Case "A" gData(7) = "Active" Case "F" gData(7) = "Finished" Case Else 'Unknown Game State End Select
ReDim pData(1 To gData(4), 0 To 5) '(playerNo, (0=name, 1=Elim Order, 2=Kills, 3=Points, 4=Result, 5=Round) )
For p = 1 To gData(4) With .SelectSingleNode("players").ChildNodes(p - 1) pData(p, 0) = .Text pData(p, 4) = .Attributes.getNamedItem("state").NodeValue End With 'Players pData(p, 5) = .SelectSingleNode("round").Text Next p
ko = 1 For e = 1 To .SelectSingleNode("events").ChildNodes.Length With .SelectSingleNode("events").ChildNodes(e - 1) 'GameXML.childNodes(19).childNodes(e - 1)
If Right(.Text, 7) = " points" Then l = InStr(.Text, " ") p = CInt(Left(.Text, l)) pData(p, 3) = pData(p, 3) + _ CInt(Replace(Replace(Replace( _ Mid(.Text, l, Len(.Text)), _ "loses", "-"), "gains", "+"), "points", "")) ElseIf Right(.Text, 14) = " from the game" Then l = InStr(.Text, " ") p = CInt(Left(.Text, l)) If Not p = 0 Then 'not eliminated by neutral pData(p, 2) = pData(p, 2) + 1 End If t = .Text t = Mid(.Text, l, Len(.Text)) pData(CInt(Replace(Replace( _ Mid(.Text, l, Len(.Text)), _ "eliminated", ""), "from the game", "")) _ , 1) = ko ' pData(p,4) = Kill Order ko = ko + 1
' NEED TO CALCULATE WHICH ROUND AN ELIMINATION TOOK PLACE
Opponents = InputBox("Please enter Your Opponents Mnemonic:")
i = 2 While Cells(i, 12).Value <> "" 'Game Status k = 2 l = 0 While Cells(k, 15).Value <> "" 'Team Name
If Cells(k, 15).Value = Cells(i, 8).Value Then
l = 1
'CHANGE THIS TO REPRESENT YOUR CLAN MNEMONIC clan = "KNT" clan1 = clan Cells(i, 14).Value = clan End If k = k + 1 Wend
If l = 0 Then Cells(i, 14).Value = Opponents clan1 = Opponents End If
j = 2 While Cells(j, 16).Value <> "" 'Player List If Cells(j, 16).Value = Cells(i, 8).Value Then Select Case Cells(i, 12).Value 'game status Case "Won" Cells(j, 17).Value = Cells(j, 17).Value + 1 Case "Lost" Cells(j, 18).Value = Cells(j, 18).Value + 1 Case Else
End Select Cells(j, 19).Value = clan1 End If j = j + 1
Remember that you still need to occasionally post progress notes onto your tournament thread, as updating Challonge does not affect the thread directly.
and the second is the one that I'll explain below - as it is a little easier!
_____________
which links players to Number of wins and losses and any active games:
which links game numbers to winning round and players;
which brings player data pertaining to the labels that the TO adds when creating the games.
and many thanks to agentcom for showing me the light 
)
[/url]
[/url]
