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...?
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.