Excel Formula to determine winning teams

[gary2014]

Hi Team,

Please help with a command to achieve the following:
1) I want the "

Winning Team" to populate in that column depending on scores of Team A & Team B.

Match No	Team A		Team B	Score Team A	Score Team B	Winning Team
1	Kings College	Vs	Royal College	45	20	Plz help me with the code
2	AV College	VS	Royal College	19	56	Plz help me with the code
3	Kings College	VS	AV College	35	30	Plz help me with the code

2) I want the "

Winner and Runner" to populate in two different columns depending on cumulative scores.

***	Kings College	Royal College	AV College	Cumulative Score		Winner Team	Runner Up Team
Kings College	****	45	35	80		--??--	--??--
Royal College	20	****	56	76		xxxxxxx	xxxxxxxx
AV College	30	19	****	49		xxxxxxx	xxxxxxxx

[MINCUS1308]

1)
The logic is simple, just use the If() formula.
to help explain i will assign numbers to the cells in the table you provided.
the cell in your table with a "1" will be cell 1; the cell in your table with a "45" will be cell 5 and so on...
i will use these numbers to indicate the cell to reference
the formula is:
in cell 7 type the following replacing numbers with the cell reference: =If(5>6, 2, 4)

[MINCUS1308]

I need more information about what you are trying to do for 2) to solve it

[MINCUS1308]

ifhelponline.xls

here is #1. i dont know why i didnt just do this. it was faster to solve than it was to actually explain

[Bob Phillips]

Public Sub ResultsTable()
Dim wsResults As Worksheet
Dim lastrow As Long
Dim nextrow As Long
Dim nextcol As Long
Dim matchrow As Long
Dim matchcol As Long
Dim i As Long

    Application.DisplayAlerts = False
    On Error Resume Next
    Worksheets("Results Table").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    
    With ActiveSheet
    
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set wsResults = Worksheets.Add(after:=Worksheets(Worksheets.Count))
        wsResults.Name = "Results Table"
        
        nextrow = 2: nextcol = 2
        For i = 2 To lastrow
        
            If IsError(Application.Match(.Cells(i, "B").Value, wsResults.Columns(1), 0)) Then
            
                wsResults.Cells(nextrow, "A").Value = .Cells(i, "B").Value
                nextrow = nextrow + 1
            End If
        
            If IsError(Application.Match(.Cells(i, "D").Value, wsResults.Columns(1), 0)) Then
            
                wsResults.Cells(nextrow, "A").Value = .Cells(i, "D").Value
                nextrow = nextrow + 1
            End If
            
            If IsError(Application.Match(.Cells(i, "B").Value, wsResults.Rows(1), 0)) Then
            
                wsResults.Cells(1, nextcol).Value = .Cells(i, "B").Value
                nextcol = nextcol + 1
            End If
            
            If IsError(Application.Match(.Cells(i, "D").Value, wsResults.Rows(1), 0)) Then
            
                wsResults.Cells(1, nextcol).Value = .Cells(i, "D").Value
                nextcol = nextcol + 1
            End If
        Next i
        
        For i = 2 To lastrow
        
            matchrow = Application.Match(.Cells(i, "B").Value, wsResults.Columns(1), 0)
            matchcol = Application.Match(.Cells(i, "D").Value, wsResults.Rows(1), 0)
            wsResults.Cells(matchrow, matchcol).Value = .Cells(i, "E").Value
        
            matchrow = Application.Match(.Cells(i, "D").Value, wsResults.Columns(1), 0)
            matchcol = Application.Match(.Cells(i, "B").Value, wsResults.Rows(1), 0)
            wsResults.Cells(matchrow, matchcol).Value = .Cells(i, "F").Value
        Next i
    End With
    
    With wsResults
    
        .Cells(2, nextcol).Resize(nextrow - 2).FormulaR1C1 = "=SUM(RC2:RC[-1])"
        .Cells(2, nextcol + 1).FormulaR1C1 = "=INDEX(R2C1:R" & nextrow & "C1,MATCH(MAX(R2C5:R" & nextrow & "C5),R2C5:R" & nextrow & "C5,0))"
        .Cells(2, nextcol + 2).FormulaR1C1 = "=INDEX(R2C1:R" & nextrow & "C1,MATCH(LARGE(R2C5:R" & nextrow & "C5,2),R2C5:R" & nextrow & "C5,0))"
        .Cells(1, nextcol).Resize(, 3).Value = Array("Cumulative Score", "Winners", "Runners-Up")
        .Columns(1).Resize(, nextcol + 2).ColumnWidth = 16
    End With
End Sub

[MINCUS1308]

i must have missed something...?