Sports competition top scorer list (round by round)

[valmir]

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
	A	B	C	D
1	Round	Club	Player	Goals
2	1	ATN	James	2
3	1	ACA	Michael	1
4	1	ASA	Robert	1
5	1	BHU	John	1
6	1	BLD	David	1
7	1	PET	Joseph	1
8	1	PET	Richard	3
9	1	PET	Thomas	1
10	1	PET	William	1
11	1	PRO	Christopher	1
12	1	PRO	Charles	1
13	1	SAG	Daniel	1
14	1
15	1
16	1
17	1
18	1
19	1
20	1
21	1
22	1
23	1
24	1
25	1
26	2	ASA	Robert	1
27	2	BHU	Anthony	1
28	2	FCC	Mark	1
29	2	PHU	Donald	1
30	2	PHU	Steven	1
31	2	PRM	Andrew	1
32	2	SAG	Daniel	2
33	2	SAG	Paul	1
34	2
35	2
36	2
37	2
38	2
39	2
40	2
41	2
42	2
43	2
44	2
45	2
46	2
47	2
48	2
49	2
50	3	ACA	Claude	2
51	3	ACA	Martin	1
52	3	ATN	James	1
53	3	BLD	David	1
54	3	FCC	Joshua	1
55	3	FCC	Mark	1
56	3	KAB	Kevin	1
57	3	KAB	Bryan	1
58	3	PET	Richard	2
59	3	PET	William	1
60	3	PHU	Donald	2
61	3	PHU	George	1
62	3	PRI	Jason	1
63	3	PRI	Malcom	1
64	3	PRM	Donald	1
65	3	PRO	Ben	1
66	3	SAG	Daniel
67	3
68	3
69	3
70	3
71	3
72	3
73	3
Scorers

 

[mumps]

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".

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

 

[valmir]

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".

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

Thanks for your reply. I ran that macro and nothing happens. Am I doing something wrong?

 

[mumps]

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?

Club	Player	Total Goals
PET	Richard	5
PHU	Donald	4
ATN	James	3
SAG	Daniel	3
ASA	Robert	2
BLD	David	2
PET	William	2
FCC	Mark	2
ACA	Claude	2
ACA	Michael	1
BHU	John	1
PET	Joseph	1
PET	Thomas	1
PRO	Christopher	1
PRO	Charles	1
BHU	Anthony	1
PHU	Steven	1
PRM	Andrew	1
SAG	Paul	1
ACA	Martin	1
FCC	Joshua	1
KAB	Kevin	1
KAB	Bryan	1
PHU	George	1
PRI	Jason	1
PRI	Malcom	1
PRO	Ben	1

 

[valmir]

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?

Club	Player	Total Goals
PET	Richard	5
PHU	Donald	4
ATN	James	3
SAG	Daniel	3
ASA	Robert	2
BLD	David	2
PET	William	2
FCC	Mark	2
ACA	Claude	2
ACA	Michael	1
BHU	John	1
PET	Joseph	1
PET	Thomas	1
PRO	Christopher	1
PRO	Charles	1
BHU	Anthony	1
PHU	Steven	1
PRM	Andrew	1
SAG	Paul	1
ACA	Martin	1
FCC	Joshua	1
KAB	Kevin	1
KAB	Bryan	1
PHU	George	1
PRI	Jason	1
PRI	Malcom	1
PRO	Ben	1

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!

 

[valmir]

I know that this is very complicated. I'm also trying another approach. Let's see what happens!

 

[mumps]

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.

 

[valmir]

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.

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!

 

[Kaper]

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:

=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:

Donald

PHU

1

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

Donald

PHU; PRM

4

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:

=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))

 

[Kaper]

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:

    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