Made a program in excel using macro (visual basic) to iterate through players list (and opponents) against matches table.
The goal is to fill players matrix to see head to head (like below)
The pseudocode looks like below:
> loop through players list in sheet "matrix"
>> loop through opponent list in sheet "matrix"
>>> loop through matches in sheet "matches"
>>>> if found matches between them mark "W" in "matrix" sheet
The macro in excel works unlike database query, so you have to move around the sheets and cells. Very tedious work.
=======================================================
Sub findmatch()
Dim p1, p2, m1, m2 As String
Dim tc, tr, tor, toc As Integer
Dim c1, c2, c3, c4 As Integer
'get player
Application.Goto Reference:=Worksheets("matrix").Range("B3")
p1 = ActiveCell.Value
c1 = 0
While p1 <> ""
p1 = ActiveCell.Value
MsgBox p1
'get opponent
Cells(2, 3).Activate
p2 = ActiveCell.Value
While p2 <> ""
'MsgBox p1 + " vs " + p2
'do work
Application.Goto Reference:=Worksheets("matches").Range("C4")
c3 = 0
m1 = ActiveCell.Value
While m1 <> ""
'MsgBox m1
If p1 = m1 Then
'MsgBox m1
ActiveCell.Offset(0, 1).Select
c3 = c3 + 1
m2 = ActiveCell.Value
'MsgBox p1 + " vs " + p2 + " CHECK " + m1 + " : " + m2
If p2 = m2 Then
'MsgBox m2
'MsgBox c1
'MsgBox c2
Application.Goto Reference:=Worksheets("matrix").Range("b3")
ActiveCell.Offset(c1, c2 + 1).Select
ActiveCell = "W"
Application.Goto Reference:=Worksheets("matches").Range("C4")
ActiveCell.Offset(c3, 0).Select
End If
'return to winner col
ActiveCell.Offset(0, -1).Select
End If
'next row
ActiveCell.Offset(1, 0).Select
m1 = ActiveCell.Value
Wend
Application.Goto Reference:=Worksheets("matrix").Range("C2")
c2 = c2 + 1
ActiveCell.Offset(0, c2).Select
p2 = ActiveCell.Value
Wend
c1 = c1 + 1
c2 = 0
Application.Goto Reference:=Worksheets("matrix").Range("B3")
ActiveCell.Offset(c1, 0).Select
Wend
End Sub