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!