Welcome Comrades to the 81st edition of the Dispatch
Well, it's lonely here. Everyone is out having fun or doing exams or both. Just me and Duke to hold the fort. Nevermind! The Foundry and Tourney divisions are brimming with articles so there's plenty to keep you occupied. And I've popped in a few teasers in the other divisions as well. At least they can all yell at me when they get back. Speaking of which, isn't it time that YOU got on board and helped us out? Would be great to hear from you!
[player]thehippo8[/player] Foundry Executive
If you would like to apply to join the news team, please visit the thread below for more details!
Hello all to the mess hall. And boy is it messy today! There was a party and no-one has cleaned up. Leehar is off and Saf is having an extra helping of exams! So I've waded in and tried to clean up some of this mess. Underneath a shop mannequin I found The Scum Insider but it had gotten wet and I've had to rewrite it from scratch. Goodness only knows what they've done with the Interview so we'll just have to do without that for this issue!
Hello again my friends, and welcome to your favorite part of the Newsletter, "War Games", where you can find information about CC Tournaments!
Thehippo8 not only is the Publisher of the Dispatch this time around, but he has given us an excellent article about his impressions of the recently-completed Chatters Tournament. Thank you hippo!
Unstoppable workhorse DaveH weighs in with his two series. The different strands of his work have now come completely together, as his rescue of the TPA series provides new material for his Tournament Tips, and vice versa.
Tournament Tipsby DaveH [spoiler=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:[bigimg]http://img600.imageshack.us/img600/3257/tpa2.jpg[/bigimg] This is sheet 1 of the spreadsheet. Sheets 2 to 48 hold the individual tournament results and sheet 49 is the Scoring sheet that I referred to last week and holds the table of possible scores for the three different types of tournament.
We start, as usual, defining a few variables and assigning them values:
Sub AddTourney() 'name of this routine Dim irow As Integer 'row number for start Dim icol As Integer 'column number for start Dim isheet As Integer 'sheet stating number Dim iplayers As Integer 'number of players Dim itourneys As Integer 'number of tournaments Dim aplayer As String 'player's name Dim asheet As String 'sheet number
irow = 7 'starting row position icol = 2 'starting column position iplayer = 0 itourneys = 52 '52 maximum tournaments isheet = 3 aplayer = "" addsheet = True 'this is a flag to erase a tournament score
Then we have a call to a subroutine to count the number of players:
Call FindNumber(irow, icol, iplayers) 'find number of players
This subroutine takes the starting row and column position of the player list and also the starting number of players to the routine and is is coded as follows:
Sub FindNumber(irow, icol, iplayers) 'Find number of players on master sheet Sheets("Summary").Activate 'ensure you at the master sheet (called "Summary") iplayers = 0 'number of players already aplayer = Cells(irow + iplayers, icol).Value While aplayer <> "" 'do this loop until we find a blank player name iplayers = iplayers + 1 'increment number of players aplayer = Cells(irow + iplayers, icol).Value 'get next player name Cells(irow + iplayers, icol).Value = StrConv(aplayer, vbLowerCase) ' convert to lower case Wend Cells(irow - 1, icol).Value = iplayers & " Players" End Sub
The conversion to lower case is to minimise the chance of mistyping players names. We now want to find out which tournament is to be added to the master score sheet.
'Add a sheet For i = 1 To iplayers + 100 Cells(irow + i - 1, 1).Value = i 'put ranking order in Next i ' which sheet? B = InputBox("Add a tournament's scores? Enter week number (or 0 to exit) Enter a negative week number to subtract the scores.") If B < 0 Then addsheet = False 'subtract that sheet B = -B End If If B <> "0" And B <> "" And B < itourneys Then 'b=week number asheet = "Week " & B 'go to Sheets(asheet).Activate Else Exit Sub 'invalid sheet number End If
The ranking order is copied to the first column as this was found to change as the spreadsheet grew - without doing anything to it. Perhaps Excel 2007 gets over this? You enter the week number of the tournament to enter and you can also enter a negative number to erase a sheet's scores. Entering zero or a sheet higher than 52 or pressing cancel will exit the routine. The sheet is then activated so that you can get the information you need.
i = 0 aplayer = Cells(i + 2, 1).Value 'player name
If aplayer = "" Then 'no players' names in MsgBox ("Week " & B & " is not completed yet.") Sheets("Summary").Activate Exit Sub ElseIf Cells(8, 1).Value = "Entered" Then MsgBox ("Week " & B & "'s results have already been entered.") Sheets("Summary").Activate Exit Sub End If While aplayer <> "" i = i + 1 'i=number of players on the individual tourney sheet aplayer = Cells(i + 2, 1).Value If addsheet = False Then Cells(i + 1, 4).Value = -Cells(i + 1, 4).Value Wend
Range(Cells(2, 1), Cells(2 + i - 1, 1)).Select Selection.Copy 'copy list of players atype = Cells(1, 5).Value 'type of tournament
The sheet is checked to see if there is a players name in the first position. If not then there is an error message. If the cell in position (8, 1) has "Entered" in it then an error message is given and you return to the main spreadsheet screen. Otherwise the list of players is counted and copied as is the type of tournament. Note that if the scores are to be subtracted from the master spreadsheet than the scores are all changed to negative scores.
Select Case atype Case Is = "Major" j = 1 Case Is = "Premier" j = 2 Case Is = "Standard" j = 3 Case Else MsgBox (atype & " is not permitted") Exit Sub End Select
l = 1 If addsheet = False Then l = -1 For k = 1 To i Cells(irow + iplayers + k - 1, icol + j).Value = Cells(irow + iplayers + k - 1, icol + j).Value + l 'increment number of Tournaments Next k
The tournament type is checked to be a valid one of the three types and there is a "1" or a "-1" put into the tournament number next to all the new players' names under the correct column for the tournament type.
Back to the tournament page to copy the score. Returning to the master sheet this is pasted under the correct column for the tournament itself and also the column for the type of tournament.
If addsheet = False Then Sheets(asheet).Activate 'back to individual sheet Cells(8, 1).Value = "Entered" For k = 1 To i Cells(k + 1, 4).Value = -Cells(k + 1, 4).Value Next k Sheets("Summary").Select End If
If the scores were negative than the routine returns to the individual sheet and turns them positive. "Entered" is copied to the sheet to inhibit repeated entry.
For k = 1 To iplayers - 1 aplayer = Cells(irow + k - 1, icol).Value aplayer2 = Cells(irow + k, icol).Value If aplayer = "" Then ElseIf aplayer = aplayer2 Then
For l = 10 To 10 + itourneys - 1 Cells(irow + k - 1, l).Value = Cells(irow + k - 1, l).Value + Cells(irow + k, l).Value If Cells(irow + k - 1, l).Value = 0 Then Cells(irow + k - 1, l).Value = "" Next l
ActiveWindow.Panes(1).Activate Rows((irow + k) & ":" & (irow + k)).Select Selection.Delete Shift:=xlUp End If Next k
The next block of code is to scan down the list of players' names and, if it finds two identical ones then each column value is added to the top row and then the bottom row deleted.
Call FindNumber(irow, icol, iplayers) 'find number of players For i = 1 To iplayers Cells(irow + i - 1, 1).Value = i 'put ranking order in Next i Range("F4").Select ActiveCell.FormulaR1C1 = "=SUM(R[3]C:R[" & iplayers + 10 & "]C)" Range("F4:I4").Select Selection.FillRight Range("I6").Select ActiveCell.FormulaR1C1 = "=SUM(R[1]C:R[" & iplayers + 10 & "]C)" Range("I6:BD6").Select Selection.FillRight End Sub
To finish off the niumber of players is copied down and put in a heading box and then a couple of formulas put in to act as checksums.
Sub AddTourney() Dim irow As Integer 'row number for start Dim icol As Integer 'column number for start Dim isheet As Integer 'sheet stating number Dim iplayers As Integer 'number of players Dim itourneys As Integer 'number of tournaments Dim aplayer As String Dim asheet As String 'sheet number
Call FindNumber(irow, icol, iplayers) 'find number of players
'Add a sheet For i = 1 To iplayers + 100 Cells(irow + i - 1, 1).Value = i 'put ranking order in Next i ' which sheet? B = InputBox("Add a tournament's scores? Enter week number (or 0 to exit) Enter a negative week number to subtract the scores.") If B < 0 Then addsheet = False 'subtract that sheet B = -B End If If B <> "0" And B <> "" And B < itourneys Then 'b=week number asheet = "Week " & B 'go to Sheets(asheet).Activate Else Exit Sub 'invalid sheet number End If
i = 0 aplayer = Cells(i + 2, 1).Value 'player name
If aplayer = "" Then MsgBox ("Week " & B & " is not completed yet.") Sheets("Summary").Activate Exit Sub ElseIf Cells(8, 1).Value = "Entered" Then MsgBox ("Week " & B & "'s results have already been entered.") Sheets("Summary").Activate Exit Sub End If While aplayer <> "" i = i + 1 'i=number of players on the individual tourney sheet aplayer = Cells(i + 2, 1).Value If addsheet = False Then Cells(i + 1, 4).Value = -Cells(i + 1, 4).Value Wend
Range(Cells(2, 1), Cells(2 + i - 1, 1)).Select Selection.Copy 'copy list of players atype = Cells(1, 5).Value 'type of tournament
Sheets("Summary").Select Cells(irow + iplayers, icol).Select ActiveSheet.Paste 'paste list of players below last one on master
Select Case atype Case Is = "Major" j = 1 Case Is = "Premier" j = 2 Case Is = "Standard" j = 3 Case Else MsgBox (atype & " is not permitted") Exit Sub End Select
l = 1 If addsheet = False Then l = -1 For k = 1 To i Cells(irow + iplayers + k - 1, icol + j).Value = Cells(irow + iplayers + k - 1, icol + j).Value + l 'increment number of Tournaments Next k
Sheets(asheet).Activate 'back to individual sheet Range(Cells(2, 4), Cells(1 + i, 4)).Select Selection.Copy 'copy score list
Sheets("Summary").Select Cells(irow + iplayers, Val(B) + 9).Select ActiveSheet.Paste 'paste in next column
If addsheet = False Then Sheets(asheet).Activate 'back to individual sheet Cells(8, 1).Value = "Entered" For k = 1 To i Cells(k + 1, 4).Value = -Cells(k + 1, 4).Value Next k Sheets("Summary").Select End If
Call FindNumber(irow, icol, iplayers) 'find number of players Rows(irow & ":" & irow + iplayers - 1).Select 'select the players rows
Application.CutCopyMode = False Selection.Sort Key1:=Range("B" & icol), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal 'sort into alpha order
For k = 1 To iplayers - 1 aplayer = Cells(irow + k - 1, icol).Value aplayer2 = Cells(irow + k, icol).Value If aplayer = "" Then ElseIf aplayer = aplayer2 Then
For l = 10 To 10 + itourneys - 1 Cells(irow + k - 1, l).Value = Cells(irow + k - 1, l).Value + Cells(irow + k, l).Value If Cells(irow + k - 1, l).Value = 0 Then Cells(irow + k - 1, l).Value = "" Next l
ActiveWindow.Panes(1).Activate Rows((irow + k) & ":" & (irow + k)).Select Selection.Delete Shift:=xlUp End If Next k
Call FindNumber(irow, icol, iplayers) 'find number of players
For k = 1 To iplayers Cells(irow + k - 1, 9).Value = Cells(irow + k - 1, 8).Value + Cells(irow + k - 1, 7).Value + Cells(irow + k - 1, 6).Value Next k
Call FindNumber(irow, icol, iplayers) 'find number of players For i = 1 To iplayers Cells(irow + i - 1, 1).Value = i 'put ranking order in Next i Range("F4").Select ActiveCell.FormulaR1C1 = "=SUM(R[3]C:R[" & iplayers + 10 & "]C)" Range("F4:I4").Select Selection.FillRight Range("I6").Select ActiveCell.FormulaR1C1 = "=SUM(R[1]C:R[" & iplayers + 10 & "]C)" Range("I6:BD6").Select Selection.FillRight End Sub Sub FindNumber(irow, icol, iplayers) 'Find number of players on master sheet Sheets("Summary").Activate iplayers = 0 'number of players already aplayer = Cells(irow + iplayers, icol).Value While aplayer <> "" iplayers = iplayers + 1 aplayer = Cells(irow + iplayers, icol).Value Cells(irow + iplayers, icol).Value = StrConv(aplayer, vbLowerCase) Wend Cells(irow - 1, icol).Value = iplayers & " Players" End Sub
Phew!
(These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to! Please note that I use Excel 2003, so there may be variation in the menu instructions if you have Excel 2007)[/spoiler] TPA Wrapby DaveH
There is such a thing as a blue moon. According to Bridie Smith, as quoted in Wikipedia:
The term blue moon is the name for the third full moon in a season that has four full moons, instead of the usual three. A blue moon occurs only every two or three years, and the term blue moon is used colloquially to mean a rare event, as in the phrase "once in a blue moon".
Well, depending on how you calculate that, we have had two, possibly three, blue moons pass us by in the time that [player]cairnswk[/player] has prepared for us 30 (YES THIRTY) full maps that have passed through the Foundry, through the Beta processes and into CC proper. What a fantastic achievement!! And to commemorate this momentous occasion, [player]koontz1973[/player] brings us two articles featuring [player]cairnswk[/player]. The first covers those 30 maps and the second covers maps that are out of Beta, where cairns features again. We all owe an incredible debt of gratitude to [player]cairnswk[/player] so let's be upstanding and sing his favourite song ...
Done that? Good! Onto the rest of the issue then. [player]Qyu[/player] brings us a fascinating look at that favourite map of yours Arms Race! while I take a look at a new map being developed and some interesting issues it throws up Ring of Fire. [player]koontz1973[/player] gives an in-depth insight into the working of the Foundry and how that may change for the better. We are looking forward to your input (good CC community) to this article. And finally, [player]koontz1973[/player] tickles your dendrites with this issue's comp, a crossword puzzle. Who will be the winner for this issue? And on that note, I leave you with a Turkish proverb:
He who sleeps with a blind man will wake up cross-eyed.
I'm sorry to say, but the Clan locker was completely cleaned out by thieves. I could have stopped them but it simply isn't my job! So when they had gone I have put what little I could glean from the covers of the books they stole.
Stay Tuned for the next issue of the ConquerClub Dispatch for an interrogation of [player]interviewee[/player]. Post any questions you may have for them here --> Interrogation with Masli
Submitting Your Own Articles
If you have a story you think the ConquerClub Community would find interesting, you can submit your articles to [player]Victor Sullivan[/player] and you may just see your article published in the Newsletter!
Subscription
Did you enjoy this Issue? Have you subscribed to the Newsletter yet?
If you want each and every issue of the ConquerClub Dispatch delivered right into your Inbox, then Subscribe Here!
Across. 3.A map you have to flush with. (8) Waterloo 6.Athens and Sparta get bloody. (13,3) Peloponnesian War 7.One of the last maps made by the dreaded map maker. (7) Eurasia 8.The terminator state. (10) California 9.Go sight seeing in beta land. (10) Copenhagen Down. 1.Hiding under the desks really works. (4,3,5) Duck And Cover 2.Pixar made a film about this map. (8) Monsters 4."Top of the world ma" (10) Antarctica 5.Nelson's brown trouser time. (9) Trafalgar 8.Number one. (7) Classic
I don't imagine newsletter volunteers will ever be eligible for the contest. Since we have access to the article before it's published, it would be a monstrously unfair advantage. I wonder, though, if at some point you might develop the contest in secret, and insert it only at the moment of publication, so that we could compete? Perhaps not every time, but maybe 1 in 5 times or something like that.
“Life is a shipwreck, but we must not forget to sing in the lifeboats.” ― Voltaire
I agree Duke, why shouldn't Team CC be able to play the Dispatch comp! So, yes, from now on they will be promulgated in secret and dispersed on publishing. The only ones in the know will be the Foundry team and the publisher.
30 Maps koontz1973
