Sports competition top scorer list (round by round)

V

valmir

Active Member
Joined
Feb 10, 2021
Messages
267
Office Version
  1. 365
Platform
  1. Windows
Hello Everyone!
As the title says, I'm trying to create a top scorer list for a sports competition:
1st round list untouched
2nd round list includes scorers from 1st and 2nd round
3rd round list includes scorers from 1st, 2nd and 3rd round and so forth.
I have three columns as follows:
Column A: round number
Column B: club name
Column C: Player's name
Column D: Goals scored
OBS: Names are unique, which means if same name is found in two teams, he has been transferred, in that case, team column should concatenate both teams
I'm attaching a sample file including first 3 rounds only. Solution should be generic so that I can use it across other files.

Top Scorers.xlsx
ABCD
1RoundClubPlayerGoals
21ATNJames2
31ACAMichael1
41ASARobert1
51BHUJohn1
61BLDDavid1
71PETJoseph1
81PETRichard3
91PETThomas1
101PETWilliam1
111PROChristopher1
121PROCharles1
131SAGDaniel1
141
151
161
171
181
191
201
211
221
231
241
251
262ASARobert1
272BHUAnthony1
282FCCMark1
292PHUDonald1
302PHUSteven1
312PRMAndrew1
322SAGDaniel2
332SAGPaul1
342
352
362
372
382
392
402
412
422
432
442
452
462
472
482
492
503ACAClaude2
513ACAMartin1
523ATNJames1
533BLDDavid1
543FCCJoshua1
553FCCMark1
563KABKevin1
573KABBryan1
583PETRichard2
593PETWilliam1
603PHUDonald2
613PHUGeorge1
623PRIJason1
633PRIMalcom1
643PRMDonald1
653PROBen1
663SAGDaniel
673
683
693
703
713
723
733
Scorers
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
I'm not sure if I understood what you were looking for but try the macro below. Before running the macro, create a sheet named "Top Scorers".
VBA Code: 
Sub TopScorers()
    Application.ScreenUpdating = False
    Dim dic As Object, i As Long, v As Variant, lRow As Long, total As Long, srcWS As Worksheet, desWS As Worksheet
    Set srcWS = Sheets("Scorers")
    Set desWS = Sheets("Top Scorers")
    With srcWS
        lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        v = .Range("B2:B" & lRow).Resize(, 2).Value
    End With
    With desWS
        .UsedRange.Offset(1).ClearContents
        .Range("A1").Resize(, 3).Value = Array("Club", "Player", "Total Goals")
    End With
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v) To UBound(v)
        If Not dic.exists(v(i, 2)) Then
            If v(i, 2) <> "" Then
                dic.Add v(i, 2), Nothing
                srcWS.Range("A1:D" & lRow).AutoFilter Field:=3, Criteria1:=v(i, 2)
                total = WorksheetFunction.Sum(srcWS.Range("D2:D" & lRow).SpecialCells(xlVisible))
                desWS.Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(, 3).Value = Array(v(i, 1), v(i, 2), total)
            End If
        End If
    Next i
    srcWS.Range("A1").AutoFilter
    desWS.Cells(1, 1).Sort Key1:=Columns(3), Order1:=xlDescending, Orientation:=xlTopToBottom, Header:=xlYes
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
mumps said:
I'm not sure if I understood what you were looking for but try the macro below. Before running the macro, create a sheet named "Top Scorers".
VBA Code: 
Sub TopScorers()
    Application.ScreenUpdating = False
    Dim dic As Object, i As Long, v As Variant, lRow As Long, total As Long, srcWS As Worksheet, desWS As Worksheet
    Set srcWS = Sheets("Scorers")
    Set desWS = Sheets("Top Scorers")
    With srcWS
        lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        v = .Range("B2:B" & lRow).Resize(, 2).Value
    End With
    With desWS
        .UsedRange.Offset(1).ClearContents
        .Range("A1").Resize(, 3).Value = Array("Club", "Player", "Total Goals")
    End With
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v) To UBound(v)
        If Not dic.exists(v(i, 2)) Then
            If v(i, 2) <> "" Then
                dic.Add v(i, 2), Nothing
                srcWS.Range("A1:D" & lRow).AutoFilter Field:=3, Criteria1:=v(i, 2)
                total = WorksheetFunction.Sum(srcWS.Range("D2:D" & lRow).SpecialCells(xlVisible))
                desWS.Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(, 3).Value = Array(v(i, 1), v(i, 2), total)
            End If
        End If
    Next i
    srcWS.Range("A1").AutoFilter
    desWS.Cells(1, 1).Sort Key1:=Columns(3), Order1:=xlDescending, Orientation:=xlTopToBottom, Header:=xlYes
    Application.ScreenUpdating = True
End Sub
Click to expand...
Thanks for your reply. I ran that macro and nothing happens. Am I doing something wrong?
 
Upvote 0
I tested the macro using the data you posted and got this on a sheet named "Top Scorers". Did you place the macro in a regular module?

ClubPlayerTotal Goals
PETRichard5
PHUDonald4
ATNJames3
SAGDaniel3
ASARobert2
BLDDavid2
PETWilliam2
FCCMark2
ACAClaude2
ACAMichael1
BHUJohn1
PETJoseph1
PETThomas1
PROChristopher1
PROCharles1
BHUAnthony1
PHUSteven1
PRMAndrew1
SAGPaul1
ACAMartin1
FCCJoshua1
KABKevin1
KABBryan1
PHUGeorge1
PRIJason1
PRIMalcom1
PROBen1
 
Upvote 0
mumps said:
I tested the macro using the data you posted and got this on a sheet named "Top Scorers". Did you place the macro in a regular module?

ClubPlayerTotal Goals
PETRichard5
PHUDonald4
ATNJames3
SAGDaniel3
ASARobert2
BLDDavid2
PETWilliam2
FCCMark2
ACAClaude2
ACAMichael1
BHUJohn1
PETJoseph1
PETThomas1
PROChristopher1
PROCharles1
BHUAnthony1
PHUSteven1
PRMAndrew1
SAGPaul1
ACAMartin1
FCCJoshua1
KABKevin1
KABBryan1
PHUGeorge1
PRIJason1
PRIMalcom1
PROBen1
Click to expand...
I'm not so familiar with macros so let me have some time to try it out. However, this table refers to the results of round 3 (round 1+round 2+round 3). However, I'd also need the results from round 2 (round 3 hasn't been played yet). When round 2 is played, the table should reflect the sum of data from round 1 and 2 only. This is the idea! When round 4 is played, data should be from (round 1+round 2+round 3+round 4). I hope you get the idea!
 
Upvote 0
I know that this is very complicated. I'm also trying another approach. Let's see what happens!
 
Upvote 0
The macro will process the data for all the rounds that are placed in the Scorers sheet. So if you have inserted rounds 1 and 2, you will get the top scorers for rounds 1 and 2. If you inserted data for rounds 1 to 5, you will get the top scorers for those rounds.

If you want to try the macro, do the following:
-create a sheet named "Top Scorers"
-make sure that macros are enabled in Excel
-hold down the ALT key and press the F11 key to open the Visual Basic Editor
-click 'Insert' in the menu at the top
-click 'Module'
-copy/paste the macro into the empty window that appears
-press the F5 key to run the macro
-close the Visual Basic Editor to return to your sheet
There are easier ways to run macros such as clicking a button on your sheet or a letter of your choice on the keyboard. If you are interested in this approach, please let me know.
 
Upvote 0
mumps said:
The macro will process the data for all the rounds that are placed in the Scorers sheet. So if you have inserted rounds 1 and 2, you will get the top scorers for rounds 1 and 2. If you inserted data for rounds 1 to 5, you will get the top scorers for those rounds.

If you want to try the macro, do the following:
-create a sheet named "Top Scorers"
-make sure that macros are enabled in Excel
-hold down the ALT key and press the F11 key to open the Visual Basic Editor
-click 'Insert' in the menu at the top
-click 'Module'
-copy/paste the macro into the empty window that appears
-press the F5 key to run the macro
-close the Visual Basic Editor to return to your sheet
There are easier ways to run macros such as clicking a button on your sheet or a letter of your choice on the keyboard. If you are interested in this approach, please let me know.
Click to expand...
Thanks for the explanation. Yes, macros are enabled because I've been executing some from recordings that I do. I've done all the steps that you're describing and when I ran the macro... nothing happened. I'll try it tomorrow more carefully and see what I'm missing. I'll keep you posted. Thanks a lot for helping out!
 
Upvote 0
Write in F1:I1 headers: Round Player Club Goals
In F2 write the round after which you want to see the result (even when you know results of x round you can go back to the past and see what was ranking after round x-1).
And in G2 write the formula:
Excel Formula: 
=LET(a,FILTER(B:D,(A:A<=F2)*(C:C<>""),SORT(HSTACK(GROUPBY(CHOOSECOLS(a,2),CHOOSECOLS(a,1),LAMBDA(b,ARRAYTOTEXT(UNIQUE(b))),,0),CHOOSECOLS(GROUPBY(CHOOSECOLS(a,2),CHOOSECOLS(a,3),SUM,,0),2)),3,-1))
Note that for F2=2 Donald was:

DonaldPHU1

and dor F2=3 you will see properly see team change (in round 3 Donald is listed twice for PHU with 2 goals and for PRM with 1

DonaldPHU; PRM4

If you dont want this "history" feature remove headers and use in G1 (as a matter of fact - enywhere in empty cell) a bit shorter formula:
Excel Formula: 
=LET(a,FILTER(B:D,C:C<>""),SORT(HSTACK(GROUPBY(CHOOSECOLS(a,2),CHOOSECOLS(a,1),LAMBDA(b,ARRAYTOTEXT(UNIQUE(b))),3,0),CHOOSECOLS(GROUPBY(CHOOSECOLS(a,2),CHOOSECOLS(a,3),SUM,3,0),2)),3,-1))
 
Last edited:
Upvote 0
BTW. I tried macro by @mumps and it works (always after a minor change*), but does not take into account team changes like in Donald case.

*) this minor change was: If I run macro with Scorers sheet active, it failed on sorting. I forced the Top Scorers to be active while sorting and it helped. So last few lines could be:
VBA Code: 
    srcWS.Range("A1").AutoFilter
    desWS.Activate
    desWS.Cells(1, 1).Sort Key1:=Range("C1"), Order1:=xlDescending, Orientation:=xlTopToBottom, Header:=xlYes
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
Excel Spreadsheet - PGA Fedex Cup tracking
Replies
1
Views
372
Snakehips
Snakehips
S
  • Question Question
Pulling Tournament History across rounds
Replies
0
Views
466
spyrious
S
H
  • Question Question
Making a top 10 list with possible duplicate values
Replies
1
Views
537
AhoyNC
A
F
  • Question Question
Excel Macro Assistance Needed for Staff Roster and Training Allocation
Replies
0
Views
438
FajnaAli
F
T
  • Question Question
VBA macro for Replace cell value from a list THEN Print Multiple Tabs with Value
Replies
0
Views
682
tristaniceph
T

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top