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!

 

[confuxion]

I'm sorry, Peter, do you mean a link offsite (Google Drive) to the sample file? I've got that set up, but I want to make sure that's what you're asking for. The sample file has 3 sheets that represent each team, with a 4th sheet ("LEADERS") that the macro runs from to list all players stats from those first 3 sheets.

 

[Peter_SSs]

do you mean a link offsite (Google Drive) to the sample file?

Yes. The file should demonstrate the problem that exists with the code from post #35 and I want you to explain in relation to that sample file what the correct result should be compared to the result that the post 35 code produces.

 

[confuxion]

Okay. Here is the link: macro-test.xlsm

In the sample file, there are 3 sheets with a total of 45 players between them (all unique names in the Player column). When I run the macro on the LEADERS sheet, I get a total of 43 players (2 are left out).

 

[Peter_SSs]

Okay. Here is the link: macro-test.xlsm

In the sample file, there are 3 sheets with a total of 45 players between them (all unique names in the Player column). When I run the macro on the LEADERS sheet, I get a total of 43 players (2 are left out).

The reason is this. You macro has this line

 a = ws.Range("A4", ws.Range("A" & Rows.Count).End(xlUp).Offset(-2)).Resize(, 10).Value

This reads in the values from row 4 down to the bottom row minus two. This appears to be to allow for the data ending two rows above the 'TOTALS' row. However, the sheet 2021 does not have a 'TOTALS' row and therefore for that sheet the data ends two rows above the last row, not two rows above the 'TOTALS' row.

The data in all the sheets probably needs to have a consistent layout. Why doesn't sheet '2021' have a 'TOTALS' row like the other sheets?

 

[confuxion]

I simply forgot to copy that TOTALS row in from my working file for that 2021 sheet. I think fixing my working file is a matter of simply making sure each sheet has that TOTALS row at the bottom. Question, though: in the macro code, is there any construct that controls how many columns of data get compiled into the the LEADERS sheet?

 

[Peter_SSs]

Question, though: in the macro code, is there any construct that controls how many columns of data get compiled into the the LEADERS sheet?

Yes, but it seems a bit odd to me. The code reads 10 column from each of the sheets and compiles them into 10 columns of results. However, when those results are written into the 'LEADERS' sheets, only 5 columns are written. This seems to me to me that reading in columns 6-10 was a waste of time.

In your sample file the 'year' sheets only have 5 columns of data. Is that the situation with your real sheets?
If so, the code would be best to just read those 5 columns in.
If not, how many columns of data do the year sheets have and which ones need to get transferred into 'LEADERS'?

 

[confuxion]

Yes, just those 5 columns of data need to get read into LEADERS. The reason the code reads 10 is that it was originally created by you to read in the 10 columns of data that were present in the all-time scoring leaders workbook I originally posted about. I used that same macro for this LEADERS sheet, not knowing enough about the code to modify it to only read those 5 columns. I think I know where in the code to make the necessary changes so that it only reads 5 columns, so I'll make those changes and see what the results yield. I'll post back whether or not it works!

 

[confuxion]

Well, it turns out I didn't know where to make the necessary changes. I made some changes to the code, then added some data to the columns beyond the 5 that I thought it was limited to reading (thinking it would choke on the data in those columns beyond the first 5), but it seemed to have no effect on the outcome. So I simply changed everything back to the way it was, and it still seems to work. It does what I need it to do, so I'm not going to mess with it, even though it's not technically correct. I would love to know what each line of code in the macro does so I can learn from it, but I'm not going to bother you with that. You've already been so helpful. Thanks again!

 

[Peter_SSs]

The code below should just deal with 5 columns. I have changed quite a few lines in the code.
I also note with the earlier code that the points values (col E) are written to the LEADERS sheet and then they are replaced with a formula and then they are again replaced with the values instead of formulas. I have not trawled back through the thread to see how we got to that but the code below simply writes the values once.
Another thing that I have notices is that with the original sample data, all the columns in the 'year' sheets were numerical except for column A but that is no longer the case with text values in column B as well. It may be that this could cause some issue with the code at some stage depending on what is possible with your data (that I am not familiar with)

Sub State_Stats_Leaders_v3()
  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
  
  Const Cols As Long = 5  '<- How many columns of data
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  ReDim b(1 To Rows.Count, 1 To Cols)
  For Each ws In Worksheets
    If Len(ws.Name) < 6 Then
      a = ws.Range("A4", ws.Range("A" & Rows.Count).End(xlUp).Offset(-2)).Resize(, Cols).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 Cols
          b(k, j) = b(k, j) + a(i, j)
        Next j
      Next i
    End If
  Next ws
  Application.ScreenUpdating = False
  With Sheets("LEADERS")
    .AutoFilterMode = False
    .UsedRange.Offset(3).EntireRow.Delete
    With .Range("A4").Resize(d.Count, Cols)
      .Value = b
      .Sort Key1:=.Columns(Cols), Order1:=xlDescending, Header:=xlNo
      .Offset(-1).Resize(d.Count + 1).AutoFilter
    End With
    .UsedRange.Columns.AutoFit
  End With
  Application.ScreenUpdating = True
End Sub

 

[confuxion]

This is great, thank you so much! Works like a charm, and I'm guessing isolating that columns variable in the code will allow me to expand upon the stats in the future if I decide to add more columnar data beyond G, A, and Pts. This is a great resource that has already caught on among the high school hockey community where I live, as nobody has attempted to keep track of the state scoring stats until now. Thanks again for everything you have done!