Dave's Tournament Tips Archive

[DaveH]

Spoiler

#1, Issue 58 - Beginner's guide to starting a tournament + links to CC'c tournament reference guides.

getting started

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:

Night Strike’s excellent “How to Host a Tournament” guide covers all the procedural stuff you need to begin:
http://www.conquerclub.com/forum/viewto ... 6#p2141686

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/viewto ... 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.

randomising your player list

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:-

Code: Select all

=RIGHT(C3,LEN(C3)-FIND(" ",C3))

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.

Inviting Your Players

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.

The code in column E is:

Code: Select all

="[to]"&D4&" "&$D$2&"[/to]"

This creates the code for a game invitation which can be copied into a pm sent (by BCC perhaps) to Player4 and Player2;

View Game 1234567 (Auto-Join Game)

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:

Code: Select all

=G$3&"[list][to]"&D4&" "&$D$2&"[/to][/list]"

Note that the

• 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):

  Code: Select all

  ="[to]"&D4&" "&$D$2&"[/to]"&"; "&"[to]"&E4&" "&$D$2&"[/to]"&"; "&"[to]"&F4&" "&$D$2&"[/to]"

  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;
  
  View Game 1234567 (Auto-Join Game); View Game 1234566 (Auto-Join Game); View Game 1234565 (Auto-Join Game)
  
  As before, you can add a greeting. Also, to tidy up the formatting, you can add the
  • tag;

    Code: Select all

    ="[list][to]"&D4&" "&$D$2&"[/to][/list][list][to]"&E4&" "&$D$2&"[/to][/list][list][to]"&F4&" "&$D$2&"[/to][/list]"

    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.

updating 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:

Code: Select all

="[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 1234565 Player3 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:

Click image to enlarge.

Code: Select all

="[game]"&D3&"[/game]  "&IF(E3="",B3&" vs "&C3,IF(E3=B3,B3&" vs [s]"&C3&"[/s]",IF(E3=C3,"[s]"&B3&"[/s]  vs "&C3,"Typing Error!")))

Game 1234567 Player4 vs Player2
Game 1234566 Player5 vs Player1
Game 1234565 Player3 vs Player7
Game 1234564 Typing Error!

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:

Code: Select all

="[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.

updating your results

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.

Code: Select all

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:

Code: Select all

Range(Cells(47, 29), Cells(46 + itotal, 30)).Select
Selection.Sort Key1:=Range("AC47"), Order1:=xlAscending

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:

Code: Select all

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:

Code: Select all

Range(Cells(47, 29), Cells(46 + itotal, 30)).Select
Selection.Sort Key1:=Range("AD47"), Order1:=xlDescending

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.

Round Robin Scoring

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:

Code: Select all

=1+IF(homewinner=awaywinner,1,0)

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" http://www.conquerclub.com/forum/viewto ... =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: http://www.conquerclub.com/forum/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.

Single Elimination Games

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 ... n_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!)

#8, Issue 64 - Double elimination tournament formating.

Double Eliminations Tournaments

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.

Double, Triple and Quad Elimination Game Design

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.

Excel Macro to Summarise 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:-

Code: Select all

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:-

Code: Select all

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:-

Code: Select all

Range(Cells(3, 5), Cells(4 + itotal, 6)).Select
Selection.Sort Key1:=Range("E3"), Order1:=xlAscending

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:-

Code: Select all

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:-

Code: Select all

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

[/list][/list]

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!)

[DaveH]

#11, Issue 67 - Using an Excel lookup table to simplify tournament game match-ups.

Using an Excel Lookup Table

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.

4-player games - playing 15 opponents once

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/viewto ... 4#p3417398

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.

Partnering Your Opponents

I came across this really useful site by Julian Wiseman:

http://www.jdawiseman.com/papers/tourna ... links.html

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:

Code: Select all

=VLOOKUP(Players!F5,Table,2,FALSE)

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.

Inviting Teams

When you want to invite your players to participate in your tournament games, the tournament handbook,
https://www.conquerclub.com/forum/viewt ... 8#p2141708

defines the TO tag to use in your pm for inviting individuals as;

Code: Select all

[to]1000000 Password[/to]

which shows up on your pm to the invited player as;

View Game 1000000 (Auto-Join Game)

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 in column E is:

Code: Select all

="[to]"&D4&" "&$D$2&"[/to]"

This creates the code for a game invitation which can be copied into a pm sent (by BCC perhaps) to Player4 and Player2;

View Game 1234567 (Auto-Join Game)

However, the tag for inviting teams into games is the TOT tag;

Code: Select all

[tot]1000000 password team#[/tot]

which appears in your pm as;

Game 1000000 (Auto-Join Game on Team 1)

This is the same as the TO tag with and additional number to define the team number. One possible Excel spreadsheet layout is as follows:



This has the two sets of team players in columns P and Q and the game numbers in column R. The code for the first team (T5) is as follows:

Code: Select all

="[tot]"&R5&" "&$R$3&" 1[/tot]"

which appears on the pm as;

Game 9551851 (Auto-Join Game on Team 1)

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.

Updating Team Games Results

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:

Code: Select all

="[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 1234565 Player3 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.

The coding for the results column is:

Code: Select all

="[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 1234565 Player 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.

List of CC 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:

http://www.conquerclub.com/forum/viewto ... 58&t=79067

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:

Code: Select all

=LEFT(B3,FIND("(",B3,1)-2)

(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.

Updating winners

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:-

Code: Select all

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).

Code: Select all

' 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

It is now sorted by winner's name:-

Code: Select all

Range(Cells(3, 1), Cells(2 + itotal, 3)).Select
    Selection.Sort Key1:=Range("B3"), Order1:=xlAscending

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:-

Code: Select all

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

This is then sorted:-

Code: Select all

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:-

Code: Select all

Wend

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:-

Code: Select all

    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:-

Code: Select all

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

Customising Excel Menus

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;

Code: Select all

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.

Excel coding to set up a tournament - 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);

Code: Select all

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 ... KIcons.asp:

The instructions for copying this set of codes into Excel are copied from the last article as follows:

Code: Select all

* 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.

Excel coding to set up a tournament - 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".

Code: Select all

    ' 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.

Code: Select all

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

This makes the final set of routines as follows:

Code: Select all

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:

Code: Select all

* 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)

[DaveH]

#21, Issue 77 - Excel code that creates a tournament spreadsheet template - Part 3; Adding the main menu code.

Excel coding to set up a tournament - Part 3

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:

Code: Select all

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.

The next code is as follows:

Code: Select all

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.

Code: Select all

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.

Code: Select all

         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.

Code: Select all

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.

The full code is as follows:

Code: Select all

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.

Excel coding to set up a tournament - Part 4

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:

Code: Select all

 ' 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:


The complete randomise subroutine is as follows:

Code: Select all

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 and paste the table
            Range(Cells(irow, icol - 1), Cells(irow + j - 1, icol + k)).Copy
            Cells(irow + j + 1, icol - 1).Select
            ActiveSheet.Paste
        
            '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?

Code: Select all

    A = Cells(irow, icol).Value 'First Heading
    If A = "" Then
        MsgBox "You need to select 'Create Template' first."
        Exit Sub
    Else

Is the table full?

Code: Select all

        '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

Has the list already been made random?

Code: Select all

        '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

If all these conditions are Ok then we copy the table and paste it below the existing table.

Code: Select all

            'copy and paste the table
            Range(Cells(irow, icol - 1), Cells(irow + j - 1, icol + k)).Copy
            Cells(irow + j + 1, icol - 1).Select
            ActiveSheet.Paste

Then we insert the Random Excel function.

Code: Select all

            'copy in a random number
            For i = 1 To j - 1
                Cells(irow + j + i + 1, icol + k + 1).Value = "=RAND()"
            Next i

Then we sort by the random number and then "tidy up" by deleting the random numbers.

Code: Select all

            '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

Finally we insert text that the list is now random - which also prevents the operation being carried out again.

Code: Select all

            '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!)

Code: Select all

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 and paste the table
            Range(Cells(irow, icol - 1), Cells(irow + j - 1, icol + k)).Copy
            Cells(irow + j + 1, icol - 1).Select
            ActiveSheet.Paste
        
            '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.

Use of VLookup function for assigning scores.

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;

Code: Select all

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:

Code: Select all

    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:

Code: Select all

    '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

Now scan down the list of players' positions:

Code: Select all

 '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.

Code: Select all

 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:

Code: Select all

         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.

The total code is as follows:

Code: Select all

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.

TPA2 Scoring 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:

Code: Select all

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:

Code: Select all

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:

Code: Select all

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.

Code: Select all

    '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.

Now we need to get the players names.

Code: Select all

 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.

Code: Select all

    Sheets("Summary").Select
    Cells(irow + iplayers, icol).Select
    ActiveSheet.Paste   'paste list of players below last one on master

It is a simple matter to paste in the copied list of players into the master sheet below the set of names already there.

Code: Select all

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.

Code: Select all

    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

    Cells(irow + iplayers, icol + 3 + j).Select
    ActiveSheet.Paste   'paste

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.

Code: Select all

    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.

Code: Select all

 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

The new number of players is found by calling a subroutine and the whole set of rows copies and sorted by players names.

Code: Select all

  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

            Cells(irow + k - 1, icol + j).Value = Cells(irow + k - 1, icol + j).Value + Cells(irow + k, icol + j).Value
            Cells(irow + k - 1, icol + j + 3).Value = Cells(irow + k - 1, icol + 3 + j).Value + Cells(irow + k, icol + 3 + j).Value

            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.

Code: Select all

 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

    Rows(irow & ":" & irow + iplayers - 1).Select
    Application.CutCopyMode = False
    Selection.Sort Key1:=Range("I" & icol), Order1:=xlDescending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

The number of players is then recalculated and the whole set of data resorted into score order.

Code: Select all

 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.

The complete subroutines are as follows:

Code: Select all

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
    
    irow = 7    'starting row position
    icol = 2    'starting column position
    iplayer = 0
    itourneys = 52
    isheet = 3
    aplayer = ""
    addsheet = True
    
    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

    Cells(irow + iplayers, icol + 3 + j).Select
    ActiveSheet.Paste   'paste

    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

            Cells(irow + k - 1, icol + j).Value = Cells(irow + k - 1, icol + j).Value + Cells(irow + k, icol + j).Value
            Cells(irow + k - 1, icol + j + 3).Value = Cells(irow + k - 1, icol + 3 + j).Value + Cells(irow + k, icol + 3 + j).Value

            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

    Rows(irow & ":" & irow + iplayers - 1).Select
    Application.CutCopyMode = False
    Selection.Sort Key1:=Range("I" & icol), Order1:=xlDescending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    
    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.

Using Buttons

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!):



Where X is the address of the destination url and Y is the url of the picture.

Let's perform a search now!

Find All Round 2 Games

Clicking on the text performs a search of games. Again the code is quite straight forward;

text

"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:

http://www.conquerclub.com/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=1

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

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.

The code is as follows:

Code: Select all

"Round "&CELL_WITH_ROUND_NUMBER&"; "&PLAYER1_POSITION&" vs "PLAYER2_POSITION"&" [to]"GAME_NUMBER_POSITION&" PASSWORD&"[/to]"

which creates the following when pm'ed to the players;

Code: Select all

 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)

This looks like the following:

Code: Select all

=IF(I73="",G73&H73&" has no game this round","Round "&B$48&"; "&G73&" vs "&H73&" [to]"&I73&" R"&B$48&"[/to]")

I also have a set of diferent codes to paste a table of match-ups into the tournament thread which is:

Code: Select all

Player 1 vs Player 2 [game]11702170[/game]

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.

Automating 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:

Code: Select all

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.

Terminator Tournaments

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.

Code: Select all

21	31	JJ41375 		morleyjoe 
21	32	redbugal 		callmecommander 
21	34	GreenBaize 		redbugal 
21	34	GreenBaize 		koontz1973 
21	34	GreenBaize 		JJ41375 
 

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.

Code: Select all

21	31	JJ41375 	1	morleyjoe 	1
21	32	redbugal 	1	callmecommander 	2
21	34	GreenBaize 	1	redbugal 	3
21	34	GreenBaize 	1	koontz1973 	4
21	34	GreenBaize 	9	JJ41375 	6

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):

Code: Select all

Sub Terminator()

Dim ListEnd As Integer
Dim ListStart As Integer

'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.

The Total code is:

Code: Select all

Sub Total()

Dim ListEnd As Integer
Dim ListStart As Integer

ListEnd = 3
ListStart = 3

'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

'Sort by score
Range(Cells(ListStart, 12), Cells(ListEnd + ListEnd + 1, 13)).Select
    Selection.Sort Key1:=Range("M3"), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

'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

'Sort by score
Range(Cells(ListStart, 2), Cells(ListEnd + ListEnd + 1, 3)).Select
    Selection.Sort Key1:=Range("C2"), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

'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.

TPA2 Coding to Modify the Results 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);

Code: Select all

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;

Code: Select all

                '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!

Code: Select all

'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.

Secrets of Game Finder

If you want to insert a link to a selection of games, you use the code that I detailed in Issue 81.

For instance:
Find All Round 2 Games

This code looks like:

Code: Select all

[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)

[DaveH]

#31, Issue 87 - Translating Visual Basic Code into CC Code via Excel.

Translating VBE into CC Code via Excel

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:

Code: Select all

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.

Analysis of termination Information

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.

There is additional explanation in the code:

Code: Select all

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.

League Tournaments - part 1

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.

League Tournaments - part 2

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.

League Tournaments - part 3

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:

Code: Select all

Sub AddScores()
'
' Macro recorded 14/12/2012 by DaveH
'
    Range("H3:H72").Select
    Selection.Copy
    
    Range("C74").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Range("G3:G72").Select
    Selection.Copy
    
    Range("D74").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Range("I3:I72").Select
    Selection.Copy

    Range("C144").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Range("J3:J72").Select
    Selection.Copy
    
    Range("D144").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Range("C74:D213").Select
    Application.CutCopyMode = False
    Selection.Sort Key1:=Range("C74"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
       
    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.

League Tournaments - part 4

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.

Code: Select all

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.

Creating tables from Excel

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!

Further explanations in the following routine:

Code: Select all

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)

#38. Issue 94 - Tables Revisited

Creating Tables - Revisited

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:

Code: Select all

[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:

Code: Select all

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: 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.

Code: Select all

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. 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.

More VLookup Uses

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.

Terminator Scoring

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:

Code: Select all

             Sub TerminateData()
            '
            ' Subroutine to analyse terminator data from game chat
            '

            iStartRow = 3   '1st line of game log text
            iStartCol = 3   'column used for game log
            iPlaceRow = 5   'start line for putting summarised text
           
            'array to hold points awarded
            iScoreIndex = 1
            Dim iScore(8, 2)
            iScore(1, 1) = 20
            iScore(2, 1) = 18
            iScore(3, 1) = 16
            iScore(4, 1) = 14
            iScore(5, 1) = 13
            iScore(6, 1) = 12
            iScore(7, 1) = 11
            iScore(1, 2) = 1
            iScore(2, 2) = 2
            iScore(3, 2) = 3
            iScore(4, 2) = 4
            iScore(5, 2) = 5
            iScore(6, 2) = 6
            iScore(7, 2) = 7

            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 array pointer
                        iScoreIndex = iScoreIndex + 1

                        '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)

[DaveH]

#41, Issue 97 - The second part of extracting terminator data - macro details.

Terminator Scoring - Part 2

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:

Code: Select all

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:

Code: Select all

    '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:

Code: Select all

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:

Code: Select all

    '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

    Selection.Sort Key1:=Range("L5"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

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

The code for this is

Code: Select all

        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.

Code: Select all

Sub Total()
    istart = 5
    icolumn = 7
    i = istart
    ilast = istart + 1
    While Cells(ilast, icolumn).Value <> ""
        ilast = ilast + 1
    Wend
    ilast = ilast - 1
    
    '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.

Amazing Race

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:

Code: Select all

FIND(",",H3,1)

This then means that we can get the left text up to one position less than the position of the comma. For instance:

Code: Select all

LEFT(H3,FIND(",",H3,1)-1)

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:

Code: Select all

=IF(ISERROR(FIND(",",H3,1)),H3,LEFT(H3,FIND(",",H3,1)-1))

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.

Seven Up

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:

Code: Select all

="[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.

Seven Up - Part 2

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;

Code: Select all

="[b]"&G152&" & "&H152 &" & "&I152&" & "&J152&"[/b]: [tot]"& R152&" R"&B$150 &" 1[/tot]"

for the teams in position 1 and

Code: Select all

="[b]"&L152&" & "&M152 &" & "&N152&" & "&O152&"[/b]: [tot]"& R152&" R"&B$150 &" 2[/tot]"

for the 2nd teams. These appear as Bigroo4601 & aaronvollrath & seechster & patrickaa317: Game 12579144 (Auto-Join Game on Team 1)
and DaveH & arno30 & Will Lee & aalii: Game 12579144 (Auto-Join Game on Team 2) for the two teams respectively.

Similarly for the trebles, doubles and singles, the codes are:

Code: Select all

="[b]"&G164&" & "&H164 &" & "&I164&"[/b]: [tot]"& R164&" R"&B$150 &" 1[/tot]" (Team 1 Triples)
="[b]"&L164&" & "&M164 &" & "&N164&"[/b]: [tot]"& R164&" R"&B$150 &" 2[/tot]" (Team 2 Triples)
="[b]"&G176&" & "&H176 &"[/b]: [tot]"& R176&" R"&B$150 &" 1[/tot]" (Team 1 Doubles)
="[b]"&L176&" & "&M176 &"[/b]: [tot]"& R176&" R"&B$150 &" 2[/tot]" (Team 2 Doubles)
="[b]"&G212&" vs "&L212& ":[/b] [to]"&R212&" R"&B$150&"[/to] [to]"&U212&" R"&B$150&"[/to] [to]"&X212&" R"&B$150&"[/to] [to]"&AA212&" R"&B$150&"[/to] [to]"&AD212&" R"&B$150&"[/to]" (Singles)

These look like:
tintrumpet & spitemalice & shoop76: Game 12579135 (Auto-Join Game on Team 1) (Triples Team 1)
greenoaks & Bolt Sarakai & knowman: Game 12579135 (Auto-Join Game on Team 2) (Triples Team 2)
Bigroo4601 & aaronvollrath: Game 12579124 (Auto-Join Game on Team 1) (Doubles Team 1)
DaveH & arno30: Game 12579124 (Auto-Join Game on Team 2) (Doubles Team 2)
shoop76 vs knowman: View Game 12579095 (Auto-Join Game) View Game 12579086 (Auto-Join Game) View Game 12579077 (Auto-Join Game) View Game 12579068 (Auto-Join Game) View Game 12579059 (Auto-Join Game) (Singles)

That's almost it; though rather than copy in the next set of round data I use the following code to automate it:

Code: Select all

Sub AddRound()

    irowdepth = 71   'depth of list of players
    
    ' 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

Poker 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:

Code: Select all

Sub FormLines1()

'
'
' 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 1 parameters
iRowStart = 2
iRowFinish = 15
iColStart = 2
iColFinish = 7

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:

Code: Select all

 ="[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

Noughts and Crosses Tournament

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: The images are held in a lookup table which I called "Maps";

Code: Select all

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:

Code: Select all

=VLOOKUP(Table reference position,Maps,2,FALSE)

Finally, on my Excel copy of the main tournament thread I reference these 9 images in 3 lines as follows:

Code: Select all

=Games!L29&Games!M29&Games!N29
=Games!L30&Games!M30&Games!N30
=Games!L31&Games!M31&Games!N31

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.

Unique 5-Player Games

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 :

Player 1 & Player2 & Player3 & Player4 & Player5
View Game 1234567 (Auto-Join Game)

Player6 & Player7 & Player8 & Player9 & Player10
View Game 1234566 (Auto-Join Game)

Player11 & Player12 & Player13 & Player14 & Player15
View Game 1234565 (Auto-Join Game)

Player16 & Player17 & Player18 & Player19 & Player20
View Game 1234564 (Auto-Join Game)

Player21 & Player22 & Player23 & Player24 & Player25
View Game 1234563 (Auto-Join Game)

Alternatively you can send each set of invites to the 5 players it applies to.
The code for the first Round 1 game is as follows:

Code: Select all

=VLOOKUP(E5,Player,2,FALSE)&" & "&VLOOKUP(E6,Player,2,FALSE)&" & "&VLOOKUP(E7,Player,2,FALSE)&" & "&VLOOKUP(E8,Player,2,FALSE)&" & "&VLOOKUP(E9,Player,2,FALSE)
="[to]"&L8&" "&$M$2&"[/to]"

Note that the password is at Cell M2.

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.

Unique Terminator Kills

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:

Code: Select all

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:

Code: Select all

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:

Code: Select all

freyme 	Nicho 
Ishihara 	mcshanester29 
dowian2 	freyme 
dowian2 	mviola 
Ishihara 	dowian2

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:

Code: Select all

freyme 	1
Ishihara 	1
dowian2 	1
dowian2 	1
Ishihara 	9
Nicho 	1
mcshanester29 	2
freyme 	3
mviola 	4
dowian2 	5

and the eliminations:

Code: Select all

freyme 	1
Ishihara 	1
dowian2 	1
dowian2 	1
Ishihara 	1

For "unique" terminations I concatenate the eliminations such as:

Code: Select all

freyme Nicho 
Ishihara mcshanester29 
dowian2 freyme 
dowian2 mviola 
Ishihara dowian2 

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.

Alternative to Challonge

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.

Round Robin Code

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

Code: Select all

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)

Code: Select all

If Int(PlayerNo / 2) <> Int((PlayerNo + 1) / 2) Then
    PlayerNo = PlayerNo + 1
End If

Now we put the set of numbers for the first round into the spreadsheet:

Code: Select all

For i = 1 To PlayerNo / 2 
    Cells(3 + i, 2).Value = i
    Cells(3 + i, 3).Value = PlayerNo + 1 - i
Next i

The final part is to replicate the process of copying the previous set of numbers into the next set with all numbers "rotated" apart from 1.

Code: Select all

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

The full routine is as follows:

Code: Select all

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)

[Dukasaur]

As originally promised here, we now make the Dave's Tips archive available!

[chapcrap]

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/viewto ... 4#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?

EDIT: Here is what I had from my files

Click image to enlarge.

Click image to enlarge.

[DaveH]

#51, Issue 109 - Getting in touch with missing players using a macro to summarise the "Find" data.

Finding Missing Players

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.

The macro, with explanations, is as follows:

Code: Select all

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.

updating the FIND instruction

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;

Code: Select all

http://www.conquerclub.com/player.php?gn=&hr=N&to=A+Non-Classic+Nelson+Of+Maps+[TPA4]&lb=Round+20&p1=&p2=&p3=&p4=&order=D&submit=Search&uid=378050&mode=find

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

Replacing these gives:

Code: Select all

 http://www.conquerclub.com/player.php?gn=&hr=N&to=A+Non-Classic+Nelson+Of+Maps+%5BTPA4%5D&lb=Round+20&p1=&p2=&p3=&p4=&order=D&submit=Search&uid=378050&mode=find

This will work and I can use the formula TITLE to write a find instruction into my thread. This is

Code: Select all

[url=http://www.conquerclub.com/player.php?gn=&hr=N&to=A+Non-Classic+Nelson+Of+Maps+%5BTPA4%5D&lb=Round+20&p1=&p2=&p3=&p4=&order=D&submit=Search&uid=378050&mode=find]Round 20 Games[/url]

or Round 20 Games

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.

getting game data

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!

The Excel macro routine is as follows:

Code: Select all

Sub SumScores()
'
' Game Nos titles in cell(1A)
' List of game numbers in column A from cell(2A)
'
Call get_cc_gamedata(R)

Range(Cells(2, 5), Cells(R, 5)).Select

Selection.Copy
Cells(2, 12).Select
ActiveSheet.Paste

Range(Cells(2, 7), Cells(R, 7)).Select
Selection.Copy
Cells(2, 13).Select
ActiveSheet.Paste

Range(Cells(2, 12), Cells(R, 13)).Select
    Selection.Sort Key1:=Range(Cells(2, 12), Cells(R, 13)), Order1:=xlAscending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

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

    Next i

    Range(Cells(2, 12), Cells(R, 13)).Select
    Selection.Sort Key1:=Range(Cells(2, 12), Cells(R, 13)), Order1:=xlAscending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

Wend
    
    Range(Cells(2, 12), Cells(R, 13)).Select
        Selection.Borders(xlEdgeTop).LineStyle = xlNone
        Selection.Borders(xlEdgeBottom).LineStyle = xlNone
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Selection.Sort Key1:=Range(Cells(2, 13), Cells(R, 13)), Order1:=xlDescending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

        
End Sub
    Sub Get_cc_tournament()

    Dim tourney As String
    Dim APIpath As String
    Dim xmlDoc As MSXML2.DOMDocument
    Dim xNode As MSXML2.IXMLDOMNode

    Dim gData()
    Dim pData()

    Dim pRange As Range
    Dim pStats As Range

    Dim headers(1 To 2)
    headers(1) = Array("Game No", "Tournament Label", "Map", "Players", "Game Type", "Round Limit", "Status")
    headers(2) = Array("Player Name", "Elim Order", "Kills", "Points", "Result", "Round")

    'Cells(nextRow, UBound(gData) + 1).Resize(UBound(pData, 1) - LBound(pData, 1) + 1, UBound(pData, 2) - LBound(pData, 2) + 1).Value = pData
    Col = UBound(headers(1)) - LBound(headers(1)) + 1
    Cells(1, 1).Resize(1, Col).Value = headers(1)

    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
           
            For g = 0 To (FileGames - 1) Step 1
               
                With xNode.ChildNodes(g)
       
                    ReDim gData(1 To 7)
                    gData(1) = .SelectSingleNode("game_number").Text
                    gData(1) = "=HYPERLINK(""http://www.conquerclub.com/game.php?game=" & _
                                        gData(1) & """,""" & gData(1) & """)"
                    gData(2) = .SelectSingleNode("tournament").Text
                    gData(3) = .SelectSingleNode("map").Text
                    gData(4) = .SelectSingleNode("players").ChildNodes.Length
                    gData(5) = .SelectSingleNode("game_type").Text
                    gData(6) = .SelectSingleNode("round_limit").Text
                    gData(7) = .SelectSingleNode("game_state").Text
                   
                   
                    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
                   
                   
                    'Range("A1:A" & UBound(GData)) = WorksheetFunction.Transpose(GData)
                    Range(Cells(nextRow, 1), Cells(nextRow, UBound(gData))) = gData
                    'nextRow = nextRow + 1
                       
                    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
                               
                            End If
                        End With
                    Next e
                               
                    Cells(nextRow, UBound(gData) + 1).Resize(UBound(pData, 1) - LBound(pData, 1) + 1, UBound(pData, 2) - LBound(pData, 2) + 1).Value = pData
                    nextRow = nextRow + gData(4)
                   
                    With Rows(nextRow).EntireRow.Borders(xlEdgeTop)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                        .ColorIndex = xlAutomatic
                    End With
                   
                End With 'Game(g) 'xNode.childNodes(g)
                   
            Next g
           
        End With 'xmlDoc

    CurPage = CurPage + 1
    Loop While CurPage <= MaxPage

    '#### All Game Data has now been imported (will error if too many rows for spreadsheet)
    Cells(1, Col + 1).Value = CStr(MaxGames) & " Games"

    Set pRange = Range(Cells(2, 8), Cells(nextRow - 1, 8))
    uPlayers = UniqueList(pRange)

    StatCol = 16   'Col P
    Set pStats = Range("P2:P" & CStr(UBound(uPlayers) + 2))

    Cells(1, StatCol).Value = "Player Name"
    pStats = WorksheetFunction.Transpose(uPlayers)

    Set pStats = pRange.Offset(0, 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.

Downloading tournament game 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:

Code: Select all

    Sub Get_cc_tournament()

    Dim tourney As String
    Dim APIpath As String
    Dim xmlDoc As MSXML2.DOMDocument
    Dim xNode As MSXML2.IXMLDOMNode

    Dim gData()
    Dim pData()

    Dim pRange As Range
    Dim pStats As Range

    Dim headers(1 To 2)
    headers(1) = Array("Game No", "Tournament Label", "Map", "Players", "Game Type", "Round Limit", "Status")
    headers(2) = Array("Player Name", "Elim Order", "Kills", "Points", "Result", "Round")

    'Cells(nextRow, UBound(gData) + 1).Resize(UBound(pData, 1) - LBound(pData, 1) + 1, UBound(pData, 2) - LBound(pData, 2) + 1).Value = pData
    Col = UBound(headers(1)) - LBound(headers(1)) + 1
    Cells(1, 1).Resize(1, Col).Value = headers(1)
    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
           
            For g = 0 To (FileGames - 1) Step 1
               
                With xNode.childNodes(g)
       
                    ReDim gData(1 To 7)
                    gData(1) = .selectSingleNode("game_number").Text
                    gData(1) = "=HYPERLINK(""http://www.conquerclub.com/game.php?game=" & _
                                        gData(1) & """,""" & gData(1) & """)"
                    gData(2) = .selectSingleNode("tournament").Text
                    gData(3) = .selectSingleNode("map").Text
                    gData(4) = .selectSingleNode("players").childNodes.Length
                    gData(5) = .selectSingleNode("game_type").Text
                    gData(6) = .selectSingleNode("round_limit").Text
                    gData(7) = .selectSingleNode("game_state").Text
                   
                   
                    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
                   
                   
                    'Range("A1:A" & UBound(GData)) = WorksheetFunction.Transpose(GData)
                    Range(Cells(nextRow, 1), Cells(nextRow, UBound(gData))) = gData
                    'nextRow = nextRow + 1
                       
                    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
                               
                            End If
                        End With
                    Next e
                               
                    Cells(nextRow, UBound(gData) + 1).Resize(UBound(pData, 1) - LBound(pData, 1) + 1, UBound(pData, 2) - LBound(pData, 2) + 1).Value = pData
                    nextRow = nextRow + gData(4)
                   
                    With Rows(nextRow).EntireRow.Borders(xlEdgeTop)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                        .ColorIndex = xlAutomatic
                    End With
                   
                End With 'Game(g) 'xNode.childNodes(g)
                   
            Next g
           
        End With 'xmlDoc

    CurPage = CurPage + 1
    Loop While CurPage <= MaxPage

    '#### All Game Data has now been imported (will error if too many rows for spreadsheet)
    Cells(1, Col + 1).Value = CStr(MaxGames) & " Games"

    Set pRange = Range(Cells(2, 8), Cells(nextRow - 1, 8))
    uPlayers = UniqueList(pRange)

    StatCol = 16   'Col P
    Set pStats = Range("P2:P" & CStr(UBound(uPlayers) + 2))

    Cells(1, StatCol).Value = "Player Name"
    pStats = WorksheetFunction.Transpose(uPlayers)

    Set pStats = pRange.Offset(0, 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.

War Game Summariser

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,

Code: Select all

Sub Get_cc_tournament() 'by MrBenn

    Cells.Select
    Selection.ClearFormats
    Selection.Clear

    Dim tourney As String
    Dim APIpath As String
    Dim xmlDoc As MSXML2.DOMDocument
    Dim xNode As MSXML2.IXMLDOMNode

    Dim gData()
    Dim pData()

    Dim pRange As Range
    Dim pStats As Range

    Dim headers(1 To 2)
    
    headers(1) = Array("Game No", "Tournament Label", "Map", "Players", "Game Type", "Round Limit", "Status")
    headers(2) = Array("Player Name", "Elim Order", "Kills", "Points", "Result", "Round", "Team", "KNT Members", "Player Name", "Won", "Lost", "Team")
 
    'Cells(nextRow, UBound(gData) + 1).Resize(UBound(pData, 1) - LBound(pData, 1) + 1, UBound(pData, 2) - LBound(pData, 2) + 1).Value = pData
    Col = UBound(headers(1)) - LBound(headers(1)) + 1
    Cells(1, 1).Resize(1, Col).Value = headers(1)

    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

    Dim members(1 To 2) 'Clan Member Lists
    
    'CHANGE THESE HEADERS TO YOUR CLAN MEMBERS
    members(1) = Array("andypl0nd0n", "BarkingAjax", "bdb", "brentros", "Bullet Justice", "curiousone1", "DaveH", "explor31", "farinelli", "franciscopatitz", "generalmighty")
    members(2) = Array("jaxdadio", "K-Pak", "MarktheMenace", "mikeymike55", "mjc7890", "phanton", "Ratrin", "Rockiesman", "Sotan", "Tank21", "tbott", "templeyard", "zhibby")

    Row1 = UBound(members(1)) - LBound(members(1)) + 1
    
    Cells(2, 16).Resize(1, Row1).Value = members(1) 'copy horizontaly
    Range(Cells(2, 16), Cells(2, 16 + Row1)).Copy
    Cells(2, 15).Select
    Selection.PasteSpecial Transpose:=True  'transpose
    Range(Cells(2, 16), Cells(2, 16 + Row1)).Delete 'delete horizontal list

    Row2 = UBound(members(2)) - LBound(members(2)) + 1
    Cells(2, 16).Resize(1, Row2).Value = members(2)
    Range(Cells(2, 16), Cells(2, 16 + Row2)).Copy

    Cells(Row2, 15).Select
    Selection.PasteSpecial Transpose:=True
    Range(Cells(2, 16), Cells(2, 16 + Row2)).Delete
    Range(Cells(1, 15), Cells(Row1 + Row2 + 1, 15)).BorderAround _
        ColorIndex:=3, Weight:=xlThick
   
   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
           
            For g = 0 To (FileGames - 1) Step 1
               
                With xNode.ChildNodes(g)
       
                    ReDim gData(1 To 7)
                    gData(1) = .SelectSingleNode("game_number").Text
                    gData(1) = "=HYPERLINK(""http://www.conquerclub.com/game.php?game=" & _
                                        gData(1) & """,""" & gData(1) & """)"
                    gData(2) = .SelectSingleNode("tournament").Text
                    gData(3) = .SelectSingleNode("map").Text
                    gData(4) = .SelectSingleNode("players").ChildNodes.Length
                    gData(5) = .SelectSingleNode("game_type").Text
                    gData(6) = .SelectSingleNode("round_limit").Text
                    gData(7) = .SelectSingleNode("game_state").Text
                   
                   
                    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
                   
                   
                    'Range("A1:A" & UBound(GData)) = WorksheetFunction.Transpose(GData)
                    Range(Cells(nextRow, 1), Cells(nextRow, UBound(gData))) = gData
                    'nextRow = nextRow + 1
                       
                    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
                               
                            End If
                        End With
                    Next e
                               
                    Cells(nextRow, UBound(gData) + 1).Resize(UBound(pData, 1) - LBound(pData, 1) + 1, UBound(pData, 2) - LBound(pData, 2) + 1).Value = pData
                    nextRow = nextRow + gData(4)
                   
                    With Rows(nextRow).EntireRow.Borders(xlEdgeTop)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                        .ColorIndex = xlAutomatic
                    End With
                 
                End With 'Game(g) 'xNode.childNodes(g)
                   
            Next g
           
        End With 'xmlDoc

    CurPage = CurPage + 1
    Loop While CurPage <= MaxPage

    '#### All Game Data has now been imported (will error if too many rows for spreadsheet)
    Cells(1, Col + 1).Value = CStr(MaxGames) & " Games"

    Set pRange = Range(Cells(2, 8), Cells(nextRow - 1, 8))
    uPlayers = UniqueList(pRange)

    StatCol = 16   'Col P
    Set pStats = Range("P2:P" & CStr(UBound(uPlayers) + 2))

    Cells(1, StatCol).Value = "Player Name"
    pStats = WorksheetFunction.Transpose(uPlayers)

    Set pStats = pRange.Offset(0, 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

        Wend
        i = i + 1

    Wend
    Range(Cells(2, 16), Cells(j - 1, 19)).Select
    Selection.Sort key1:=Range(Cells(2, 19), Cells(j - 1, 19)), _
        key2:=Range(Cells(2, 16), Cells(j - 1, 16)), order1:=xlAscending, order2:=xlAscending, Header:=xlNo

    Range(Cells(1, 16), Cells(j - 1, 19)).Select
    Selection.BorderAround ColorIndex:=4, Weight:=xlThick
    
    GamesNumber = Cells(1, 20).Value
    GamesNumber = Left(GamesNumber, Len(GamesNumber) - 6)
    Cells(1, 22).Value = 0
    Cells(1, 23).Value = 0
    i = 2
    j = 2
    While GamesNumber > 0
        While Cells(i, 1).Value = ""
            i = i + 1
        Wend
        
        A = "[game]" & Cells(i, 1) & "[/game] (" & Cells(i, 5) & ") " & Cells(i, 3)
        If Cells(i, 12).Value = "Won" Then
            If Cells(i, 14).Value = "KNT" Then
                Cells(1, 22).Value = Cells(1, 22).Value + 1
                B = " [color=#FF0000]"
            Else
                Cells(1, 23).Value = Cells(1, 23).Value + 1
                B = " [color=#0000FF]"
            End If
            A = A & B & Cells(i, 14) & " Won[/color]"
        ElseIf Cells(i, 12).Value = "Lost" Then
            i = i + Cells(i, 4).Value / 2
            If Cells(i, 14).Value = "KNT" Then
                Cells(1, 22).Value = Cells(1, 22).Value + 1
                B = " [color=#FF0000]"
            Else
                Cells(1, 23).Value = Cells(1, 23).Value + 1
                B = " [color=#0000FF]"
            End If
            A = A & B & Cells(i, 14) & " Won[/color]"
        End If
        Cells(j, 21).Value = A
        j = j + 1
        i = i + 1
        GamesNumber = GamesNumber - 1
    Wend
    Cells(1, 21).Value = tourney & "  (" & clan & " " & Cells(1, 22) & " - " & Opponents & " " & Cells(1, 23) & ")"
    Cells(1, 22).Value = ""
    Cells(1, 23).Value = ""
    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

Enjoy!