Best way to make all-time leading scorers sheet from multiple sheets

[confuxion]

So I've gone through the arduous process of collecting (in Excel) 20 season's worth of individual scoring stats for the HS hockey team my son plays for. Every season is contained in it's own sheet, with individual stats for each player. I'm looking for the best approach to creating an additional sheet that compiles an all-time leading scorers list among those 20 seasons; one that will keep the manual data-entry to a minimum. Given that high school is 4 years, many players appear on upwards of 4 different sheets. This is more about the approach in trying to collect every player and their associated career stats onto one sheet. My initial thought is to use VLOOKUPS on player names across all 20 sheets, but the best way to do this escapes me. Maybe this is simpler than I think. Thanks in advance for any tips/ideas!

 

[Peter_SSs]

Sorry about that. I corrected it. Fully viewable/editable now.

Thanks.

The first obvious difference is that your earlier sample sheets they were named '2022' or '2023' etc. Now they are named '2021-22' or '2022-23' etc.
So the first thing to try would be to allow for that difference. Try v4 ..

Sub Career_Stats_v4()
  Dim d As Object
  Dim a As Variant, b As Variant
  Dim ws As Worksheet
  Dim i As Long, j As Long, k As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  ReDim b(1 To Rows.Count, 1 To 10)
  For Each ws In Worksheets
    If ws.Name Like "####-##" Then
      a = ws.Range("A4", ws.Range("A" & Rows.Count).End(xlUp).Offset(-2)).Resize(, 10).Value
      For i = 1 To UBound(a)
        If Not d.exists(a(i, 1)) Then
          d(a(i, 1)) = d.Count + 1
          b(d.Count, 1) = a(i, 1)
        End If
        k = d(a(i, 1))
        For j = 2 To 10
          b(k, j) = b(k, j) + a(i, j)
        Next j
      Next i
    End If
  Next ws
  Application.ScreenUpdating = False
  With Sheets("CAREER")
    .AutoFilterMode = False
    .UsedRange.Offset(3).EntireRow.Delete
    With .Range("A4").Resize(d.Count, 10)
      .Value = b
      .Columns(5).Formula = "=C4+D4"
      .Columns(11).Formula = "=IFERROR(ROUND(C4/H4,3),0)"
      .Columns(12).Formula = "=IFERROR(ROUND(I4/(I4+J4),3),0)"
      .Sort Key1:=.Columns(5), Order1:=xlDescending, Header:=xlNo
      .Offset(-1).Resize(d.Count + 1, 12).AutoFilter
    End With
    With .Range("A" & Rows.Count).End(xlUp).Offset(2)
      .Value = "TOTALS"
      .Offset(, 2).Resize(, 8).FormulaR1C1 = "=SUM(R4C:R[-1]C)"
      .Offset(, 10).FormulaR1C1 = "=IFERROR(ROUND(RC[-8]/RC[-3],3),0)"
      .Offset(, 11).FormulaR1C1 = "=IFERROR(ROUND(RC[-3]/(RC[-3]+RC[-2]),3),0)"
      .EntireRow.Font.Bold = True
    End With
    With .UsedRange
      .Columns.AutoFit
      .Value = .Value
    End With
  End With
  Application.ScreenUpdating = True
End Sub

 

[confuxion]

I can't believe I didn't catch that mistake I was making in the sheet-naming process! So I ran the updated macro and it worked, with a few small caveats (I re-uploaded the updated file to GD):

1) For some reason, the first line-item that shows up is a "TOTALS" one. Not sure where it's grabbing that from.
2) About halfway down the list, there is a blank line-item (as in, the name is blank), and all zeros appear in the columns adjacent to it. I checked all the sheets to make sure there wasn't a blank name with zeros listed next to it, so not sure where that's coming from.
3) The "Sh%" and "FO%" columnar data is computing in decimal format instead of percentage (eg. "0.211" instead of "21.1%").

These are hardly mission-critical errors, so I can't thank you enough for your diligent work on this!

 

[Peter_SSs]

1) For some reason, the first line-item that shows up is a "TOTALS" one. Not sure where it's grabbing that from.
2) About halfway down the list, there is a blank line-item (as in, the name is blank), and all zeros appear in the columns adjacent to it. I checked all the sheets to make sure there wasn't a blank name with zeros listed next to it, so not sure where that's coming from.

Both of these relate to the difference between your sample data and your actual worksheets.
In your original sample worksheets the last row in column A was a TOTALS row but in your actual sheets ..

• Some sheets are exactly like that. eg Sheet '2009-10'
• Some sheets have an additional entry in col A on the next row below TOTALS like "3-15-3" in sheet '2007-08'
• Some sheets have an additional entry in col A two rows below TOTALS like "12-8-1" in sheet '2008-09'

3) The "Sh%" and "FO%" columnar data is computing in decimal format instead of percentage (eg. "0.211" instead of "21.1%").

Select those entire columns in the 'CAREER' sheet and format them as Percentage with 1 decimal place.

Now run this updated v5 code

Sub Career_Stats_v5()
  Dim d As Object
  Dim a As Variant, b As Variant
  Dim ws As Worksheet
  Dim i As Long, j As Long, k As Long
 
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  ReDim b(1 To Rows.Count, 1 To 10)
  For Each ws In Worksheets
    If ws.Name Like "####-##" Then
      a = ws.Range("A4", ws.Range("A1").End(xlDown)).Resize(, 10).Value
      For i = 1 To UBound(a)
        If Not d.exists(a(i, 1)) Then
          d(a(i, 1)) = d.Count + 1
          b(d.Count, 1) = a(i, 1)
        End If
        k = d(a(i, 1))
        For j = 2 To 10
          b(k, j) = b(k, j) + a(i, j)
        Next j
      Next i
    End If
  Next ws
  Application.ScreenUpdating = False
  With Sheets("CAREER")
    .AutoFilterMode = False
    .UsedRange.Offset(3).EntireRow.Delete
    With .Range("A4").Resize(d.Count, 10)
      .Value = b
      .Columns(5).Formula = "=C4+D4"
      .Columns(11).Formula = "=IFERROR(ROUND(C4/H4,3),0)"
      .Columns(12).Formula = "=IFERROR(ROUND(I4/(I4+J4),3),0)"
      .Sort Key1:=.Columns(5), Order1:=xlDescending, Header:=xlNo
      .Offset(-1).Resize(d.Count + 1, 12).AutoFilter
    End With
    With .Range("A" & Rows.Count).End(xlUp).Offset(2)
      .Value = "TOTALS"
      .Offset(, 2).Resize(, 8).FormulaR1C1 = "=SUM(R4C:R[-1]C)"
      .Offset(, 10).FormulaR1C1 = "=IFERROR(ROUND(RC[-8]/RC[-3],3),0)"
      .Offset(, 11).FormulaR1C1 = "=IFERROR(ROUND(RC[-3]/(RC[-3]+RC[-2]),3),0)"
      .EntireRow.Font.Bold = True
    End With
    With .UsedRange
      .Columns.AutoFit
      .Value = .Value
      .Font.Size = 10
      .Font.Name = "Calibri"
    End With
  End With
  Application.ScreenUpdating = True
End Sub

 

[felixstraube]

I would give the power query solution Alex's suggested a try, or even simplify it further by entering all the data in one table like this:

Year	Player	Ga	G	A	PTS	PIM
2021	Wayne Gretzky	20	5	5	10	2
2021	Bobby Orr	18	16	7	23	12
2021	Mario Lemieux	19	4	23	27	6
2021	Martin St. Louis	20	4	9	13	7
2021	John LeClair	17	3	3	6	15
2022	Wayne Gretzky	19	15	25	40	4
2022	Bobby Orr	12	7	10	17	6
2022	Mario Lemieux	15	14	14	28	8
2022	Martin St. Louis	20	8	26	34	2
2022	John LeClair	18	10	8	18	25
2022	Jarri Kurri	20	7	14	21	6
2022	Mark Messier	19	15	11	26	45
2023	Wayne Gretzky	19	5	5	10	2
2023	Martin St. Louis	20	16	7	23	12
2023	John LeClair	15	4	23	27	6
2023	Mark Messier	19	4	9	13	7
2023	Connor McDavid	20	3	3	6	15
2023	Sidney Crosby	20	12	18	30	6
2023	Connor Bedard	18	7	16	23	7
2023	Mack Celebrini	16	4	8	12	5

Then you can easily get all sorts of statistics or sum of values with a pivot table (o even pivot chart) like the following, where you could add a slicer for years and select from which year do you want to see the totals:

You also can select which player you want to see the stat for. For example here you see data for year 2022 and 2023 for John LeClair and Mack Celebrini.

If you need some calculations to your result data you could do that too.

Me in your place would try something like this. It is pretty simple and easy to maintain and update imho.
Just my 2 cents. Feel free to ignore it.
But if you are interested in this solution and need any help to set this up, just ask.