PDA

View Full Version : Excel Formula to determine winning teams



gary2014
11-10-2014, 09:32 AM
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
11-14-2014, 11:43 AM
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
11-14-2014, 11:45 AM
I need more information about what you are trying to do for 2) to solve it

MINCUS1308
11-14-2014, 11:48 AM
12499

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
11-14-2014, 12:39 PM
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
11-14-2014, 12:50 PM
i must have missed something...?