Excel score sheet

Question

So, I haven't figured out how to do this.

Basically, I want something like this:

P1    P2    P3                TOTAL SCORE
--    --    --                P1   P2   P3
21    /     13                 1    2    0
/     17    10
6      7    /

So, the three columns must compare to one-another (the "/" means that the player didn't play that game, but it doesn't have to be printed), the greatest among the three gets a +1 value in the TOTAL SCORE tab.

Plus, is there any easier way to do this than comparing one cell to another cell? I mean, is there a possibility to drag and mark all cells on all of the three columns and make sure that they only compare the cells in the three columns IN THE SAME ROW?

Answer 1

Let us assume that the data appears as in the picture in Sheet1 (Don't change the structure):

1. Open an Excel
2. Press ALT & F11 to open Visual Editor
3. Add a module from > Insert (in the Upper toolbar) - Module ( third option)
4. Paste the below codes & execute Sub Evaluation() (press F5 when your cursor is in Sub Evaluation)
5. To store lastrow in order to continue from the next record i use sheet2 range A1

Try:

Option Explicit

Public Sub Process_Data(ByVal I_Value As Long)

Dim LastRow As Long
Dim i As Long
Dim CA As Integer
Dim CB As Integer
Dim CC As Integer

With Sheet1

    LastRow = .Range("A" & Rows.Count).End(xlUp).Row

    For i = I_Value To LastRow '<= Lets say that the first score is at sheet1 column A row 3.LastRow represent the row of the last data in column A
        CA = 0
        CB = 0 '<= Every time that i change value we zero our variables to get the new value
        CC = 0
        If .Range("A" & i).Value = "/" Then '<= Check if there is a number or "/".if there is "/" we zero variable
            CA = 0
        Else
            CA = .Range("A" & i).Value
        End If
        If .Range("B" & i).Value = "/" Then
            CB = 0
        Else
            CB = .Range("B" & i).Value
        End If
        If .Range("C" & i).Value = "/" Then
            CC = 0
        Else
            CC = .Range("C" & i).Value
        End If

        If CA > CB And CA > CC Then ' <= Check which number is bigger
            .Range("E3").Value = .Range("E3").Value + 1 '<= At one point to each category
        ElseIf CB > CA And CB > CC Then
            .Range("F3").Value = .Range("F3").Value + 1
        ElseIf CC > CA And CC > CB Then
            .Range("G3").Value = .Range("G3").Value + 1
        End If
    Next i
End With

End Sub

Sub Evaluation()

Dim Value As Long
Dim LastRow As Long

LastRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row

If (LastRow = 2) Or (LastRow = Sheet2.Range("A1").Value) Then '<= Check if the table has new data
    Exit Sub
Else
    If Sheet2.Range("A1").Value = "" Then '<=Check which value will adopt be i
        Value = 3
    Else
        Value = Sheet2.Range("A1").Value + 1
    End If
End If

Call Process_Data(I_Value:=Value)

Sheet2.Range("A1").Value = Sheet1.Range("A" & Rows.Count).End(xlUp).Row '<= Record the lastrow processed out

End Sub