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]

Welcome to the MrExcel board!

This is probably best as a macro job rather than formulas. Would that be acceptable?
If so, could you make up say 3 small dummy "season" worksheets and then manually fill out the results sheet that you would like produced from that data. Post all 4 small sheets with the tool mentioned below.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

(If you have trouble with XL2BB review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of theXL2BB Instructions page linked above.)

 

[confuxion]

2023-2024-u32-varsity-hockey-stats.xlsx
	A	B	C	D	E	F
1			2021 TOTALS
2	Scoring
3	Player	Ga	G	A	PTS	PIM
4	Wayne Gretzky	20	5	5	10	2
5	Bobby Orr	18	16	7	23	12
6	Mario Lemieux	19	4	23	27	6
7	Martin St. Louis	20	4	9	13	7
8	John LeClair	17	3	3	6	15
9
10	TOTALS	20	32	47	79	42
2021

Cell Formulas
Range	Formula
E4:E8	E4	=C4+D4
C10:F10	C10	=SUM(C4:C8)

2023-2024-u32-varsity-hockey-stats.xlsx
	A	B	C	D	E	F
1			2022 TOTALS
2	Scoring
3	Player	Ga	G	A	PTS	PIM
4	Wayne Gretzky	19	15	25	40	4
5	Bobby Orr	12	7	10	17	6
6	Mario Lemieux	15	14	14	28	8
7	Martin St. Louis	20	8	26	34	2
8	John LeClair	18	10	8	18	25
9	Jarri Kurri	20	7	14	21	6
10	Mark Messier	19	15	11	26	45
11
12	TOTALS	20	76	108	184	96
2022

Cell Formulas
Range	Formula
E4:E10	E4	=C4+D4
C12:F12	C12	=SUM(C4:C10)

2023-2024-u32-varsity-hockey-stats.xlsx
	A	B	C	D	E	F
1			2023 TOTALS
2	Scoring
3	Player	Ga	G	A	PTS	PIM
4	Wayne Gretzky	19	5	5	10	2
5	Martin St. Louis	20	16	7	23	12
6	John LeClair	15	4	23	27	6
7	Mark Messier	19	4	9	13	7
8	Connor McDavid	20	3	3	6	15
9	Sidney Crosby	20	12	18	30	6
10	Connor Bedard	18	7	16	23	7
11	Mack Celebrini	16	4	8	12	5
12
13	TOTALS	20	55	89	144	60
2023

Cell Formulas
Range	Formula
E4:E11	E4	=C4+D4
C13:F13	C13	=SUM(C4:C11)

2023-2024-u32-varsity-hockey-stats.xlsx
	A	B	C	D	E	F
1			CAREER LEADERS
2	Scoring
3	Player	Ga	G	A	PTS	PIM
4	Wayne Gretzky				0
5	Martin St. Louis				0
6	John LeClair				0
7	Mark Messier				0
8	Connor McDavid				0
9	Sidney Crosby				0
10	Connor Bedard				0
11	Mack Celebrini				0
12	Bobby Orr				0
13	Mario Lemieux				0
14	Jari Kurri				0
15	Mark Messier				0
16
17	TOTALS		0	0	0	0
CAREER

Cell Formulas
Range	Formula
E4:E15	E4	=C4+D4
C17:F17	C17	=SUM(C4:C11)

 

[confuxion]

I think I did that correctly.

 

[Peter_SSs]

I think I did that correctly.

You have used XL2BB correctly.

However, you have not manually filled out the results sheet with the results that you would want. Can you do that on the CAREER sheet and post that one again, adding any explanation about how you got those results so that helpers who know nothing about the sport might still be able to assist?

 

[confuxion]

I manually added the formulas in for the "Ga" (Games) column for the first 3 players to give you an idea of what I'm doing. But what that involved was hitting the equals button, then manually going through each sheet and selecting the amount of "Ga" for the corresponding player, adding a + symbol after each instance, then hitting Enter to achieve the total. Imagine doing this process for each player, maybe 200 players total, over 20 sheets, each representing a given season. It's doable, given that no player will have appeared in more than 4 sheets (seasons), but I just wondered if there was a better way. For example, something that:

1) Loops through all 20 sheets, grabs all the unique names, then dumps them on the Career sheet, to start...
2) Then, for each player listed, a formula/macro looks through all 20 sheets to find that player's corresponding stats and totals them on the Career sheet.

2023-2024-u32-varsity-hockey-stats.xlsx
	A	B	C	D	E	F
1			CAREER LEADERS
2	Scoring
3	Player	Ga	G	A	PTS	PIM
4	Wayne Gretzky	58	25	35	60
5	Martin St. Louis	60	28	42	70
6	John LeClair	50	17	34	51
7	Mark Messier				0
8	Connor McDavid				0
9	Sidney Crosby				0
10	Connor Bedard				0
11	Mack Celebrini				0
12	Bobby Orr				0
13	Mario Lemieux				0
14	Jari Kurri				0
15	Mark Messier				0
16
17	TOTALS		70	111	181	0
CAREER

Cell Formulas
Range	Formula
B4:D4	B4	='2021'!B4+'2022'!B4+'2023'!B4
B5:D6	B5	='2021'!B7+'2022'!B7+'2023'!B5
E4:E15	E4	=C4+D4
C17:F17	C17	=SUM(C4:C11)

 

[confuxion]

I may just have to do it the way that I described in my last post. It will take some time and carpal-tunnel syndrome to do for 200+ players over the course of 20 seasons. Was just looking for something smarter than I'm capable of.

 

[alex78]

Hi confuxion,

Using Power Query is the easiest way in my opinion to do this.

let
    Source = Excel.Workbook(File.Contents("C:\Users\Book1.xlsx"), null, true), // add your file location
    #"Filtered" = Table.SelectRows(Source, each ([Kind] = "Sheet") and ([Item] <> "CAREER"))[Data],
    All_Data = Table.Combine(List.Transform(#"Filtered", (x)=> Table.PromoteHeaders(Table.Skip(x,2)))),
    Removed_null_Totals = Table.SelectRows(All_Data, each ([Player] <> null and [Player] <> "TOTALS")),
    cols = List.Skip(Table.ColumnNames(Removed_null_Totals),1),
    #"Grouped Rows" = Table.Group(Removed_null_Totals, {"Player"}, {{"x", each Table.FromRows({List.Accumulate(cols, {}, (s,c)=> s & {List.Sum(Table.Column(_,c))})}, cols)}}),
    Result = Table.ExpandTableColumn(#"Grouped Rows", "x", cols)
in
   Result

Converting your data to tables is easier to manipulate - if so, the M code should be updated.

Regards

 

[confuxion]

Alex78, thanks for your reply. Just so I'm clear, is the macro code you included responsible for totaling each players career stats from the 3 sheets, or is that just the code to convert the data on the Career sheet to a table? Also, do you recommend converting the data in each sheet to tables, not just the Career sheet? Sorry, this is a bit above my pay-grade.

 

[Peter_SSs]

Give this macro a try with a copy of your workbook. I have assumed that the 'CAREER' sheet has been created and has those headings shown in rows 1:3.
No need for formal tables for this code.
If another sheet is added in the future just run the code again.

Sub Career_Stats()
  Dim d As Object
  Dim a As Variant, Bits As Variant
  Dim ws As Worksheet
  Dim i As Long
 
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  For Each ws In Worksheets
    If ws.Name Like "####" Then
      a = ws.Range("A4", ws.Range("A" & Rows.Count).End(xlUp).Offset(-2)).Resize(, 4).Value
      For i = 1 To UBound(a)
        If d.exists(a(i, 1)) Then
          Bits = Split(d(a(i, 1)))
          d(a(i, 1)) = Bits(0) + a(i, 2) & " " & Bits(1) + a(i, 3) & " " & Bits(2) + a(i, 4)
        Else
          d(a(i, 1)) = a(i, 2) & " " & a(i, 3) & " " & a(i, 4)
        End If
      Next i
    End If
  Next ws
  Application.ScreenUpdating = False
  With Sheets("CAREER")
    .UsedRange.Offset(3).EntireRow.Delete
    With .Range("A4").Resize(d.Count, 2)
      .Value = Application.Transpose(Array(d.Keys, d.Items))
      .Columns(2).TextToColumns DataType:=xlDelimited, Space:=True, Other:=False
      .Offset(, 4).Resize(, 1).FormulaR1C1 = "=RC[-2]+RC[-1]"
    End With
    With .Range("A" & Rows.Count).End(xlUp).Offset(2)
      .Value = "TOTALS"
      .Offset(, 2).Resize(, 4).FormulaR1C1 = "=SUM(R4C:R[-1]C)"
      .EntireRow.Font.Bold = True
    End With
    .UsedRange.Columns.AutoFit
  End With
  Application.ScreenUpdating = True
End Sub

Here is my 'CAREER' sheet after running the above code with your 3 year sheets. If the results need ordering in some way then give details.

confuxion.xlsm
	A	B	C	D	E	F
1			CAREER LEADERS
2	Scoring
3	Player	Ga	G	A	PTS	PIM
4	Wayne Gretzky	58	25	35	60
5	Bobby Orr	30	23	17	40
6	Mario Lemieux	34	18	37	55
7	Martin St. Louis	60	28	42	70
8	John LeClair	50	17	34	51
9	Jarri Kurri	20	7	14	21
10	Mark Messier	38	19	20	39
11	Connor McDavid	20	3	3	6
12	Sidney Crosby	20	12	18	30
13	Connor Bedard	18	7	16	23
14	Mack Celebrini	16	4	8	12
15
16	TOTALS		163	244	407	0
CAREER

Cell Formulas
Range	Formula
E4:E14	E4	=C4+D4
C16:F16	C16	=SUM(C$4:C15)