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]

Forgot to add: If you need help with how to implement my code, just ask.

 

[confuxion]

Peter, that is crazy! I'm flummoxed! I did what you said and put those 4 sheets in a separate test workbook and added the macro. It ran perfectly! I even added a new player to one of the sheets, along with his stats, and boom - ran the macro and he showed up in the totals. I feel selfish asking for more, but yes, to answer your question, I do need ordering. Sorted by PTS (points) is how the finished product should present, although, in a perfect world, the Career data gets turned into a table by which any column is then sortable.

Also, the stats I used for this example is about half of the total that each year comes with. Does anything need to be done to the code to extend out more columns for additional data? Incredible work!

 

[Peter_SSs]

Does anything need to be done to the code to extend out more columns for additional data?

Yes, if there are more columns involved then changes will be required and it may be that a different approach would be best. Lets get the details of how many columns there actually is and whether or not every column needs to be included in the results sheet.

We will deal with any sorting/table creation once the above issue is resolved.

 

[confuxion]

Sorry, this is probably a dumb question to ask, given what I read in the rules section, but would this in any way be easier if I sent you the actual workbook? Totally understand if that's not how you roll, or how I should even suggest rolling.

 

[Peter_SSs]

No, as a public forum #4 of the rules means that all communication should remain in the forum. It may well be that a better solution that mine might be offered and people need to all have equal access to the information and sample data so they can participate if they want. Couldn't you just post a few rows from one of the year sheets with XL2BB and explain what information from that needs to get included in the results sheet?

 

[confuxion]

Gotcha, my bad. Yes, absolutely, I can post an expansion of my data here. One minute...

 

[confuxion]

This is the full extent of the stats that have been tracked (although it's worth noting, some of the years I have are missing stats for some of the categories, but all the years have Games, G, A, PTS at the very least). Note that the PTS, Sh% (shooting %), and FO% (face-off %) are the only 3 columns that are formulaic.

2023-2024-u32-varsity-hockey-stats.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	2023 TOTALS
2	Scoring
3	Player	Ga	G	A	PTS	PIM	+ / -	Shots	FOW	FOL	Sh%	FO%
4	Wayne Gretzky	19	5	5	10	2	1	55	6	7	9.1%	46.2%
5	Martin St. Louis	20	16	7	23	12	0	67	14	25	23.9%	35.9%
6	John LeClair	15	4	23	27	6	-8	108	103	62	3.7%	62.4%
7	Mark Messier	19	4	9	13	7	17	200	5	7	2.0%	41.7%
8	Connor McDavid	20	3	3	6	15	-22	52	2	11	5.8%	15.4%
9	Sidney Crosby	20	12	18	30	6	5	28	17	34	42.9%	33.3%
10	Connor Bedard	18	7	16	23	7	7	115	215	85	6.1%	71.7%
11	Mack Celebrini	16	4	8	12	5	34	85	110	105	4.7%	51.2%
12
13	TOTALS	20	55	89	144	60	34	710	472	336	7.7%	58.4%
2023

Cell Formulas
Range	Formula
K13,K4:K11	K4	=IF(ISERROR(C4/H4),0,C4/H4)
L13,L4:L11	L4	=IF(ISERROR(I4/I4+J4),0,I4/(I4+J4))
E4:E11	E4	=C4+D4
C13:J13	C13	=SUM(C4:C11)

 

[Peter_SSs]

So columns C, D, F, G, H, I & J need to get accumulated onto the results sheet and the formulas in E, K & L could be re-created on the results sheet like I did with col E before?

 

[confuxion]

Yes, exactly. Column B needs to get accumulated onto the results sheet as well. I think you meant to put that.

 

[alex78]

Confuxion,
for a try with Power Q:

• Create table for each year data as bellow for 2023 – name table: t_2023.

Player	Ga	G	A	PTS	PIM	+ / -	Shots	FOW	FOL	Sh%	FO%
Wayne Gretzky	19	5	5	=[@G]+[@A]	2	1	55	6	7	=[@G]/[@Shots]	=[@FOW]/([@FOW]+[@FOL])
Martin St. Louis	20	16	7	=[@G]+[@A]	12	0	67	14	25	=[@G]/[@Shots]	=[@FOW]/([@FOW]+[@FOL])
John LeClair	15	4	23	=[@G]+[@A]	6	-8	108	103	62	=[@G]/[@Shots]	=[@FOW]/([@FOW]+[@FOL])
Mark Messier	19	4	9	=[@G]+[@A]	7	17	200	5	7	=[@G]/[@Shots]	=[@FOW]/([@FOW]+[@FOL])
Connor McDavid	20	3	3	=[@G]+[@A]	15	-22	52	2	11	=[@G]/[@Shots]	=[@FOW]/([@FOW]+[@FOL])
Sidney Crosby	20	12	18	=[@G]+[@A]	6	5	28	17	34	=[@G]/[@Shots]	=[@FOW]/([@FOW]+[@FOL])
Connor Bedard	18	7	16	=[@G]+[@A]	7	7	115	215	85	=[@G]/[@Shots]	=[@FOW]/([@FOW]+[@FOL])
Mack Celebrini	16	4	8	=[@G]+[@A]	5	34	85	110	105	=[@G]/[@Shots]	=[@FOW]/([@FOW]+[@FOL])
Total	=SUBTOTAL(104,[Ga])	=SUBTOTAL(109,[G])	=SUBTOTAL(109,[A])	=SUBTOTAL(109,[PTS])	=SUBTOTAL(109,[PIM])	=SUBTOTAL(109,[+ / -])	=SUBTOTAL(109,[Shots])	=SUBTOTAL(109,[FOW])	=SUBTOTAL(109,[FOL])	=t_2023[[#Totals],[G]]/t_2023[[#Totals],[Shots]]	=t_2023[[#Totals],[FOW]]/(t_2023[[#Totals],[FOW]]+t_2023[[#Totals],[FOL]])

• Repeat for each year data.
• Then Data> Get Data > From Other Sources > Blank Query > Home > Advanced Editor and remove code still present and copy/past the following code and press Done.

let
    Source = Table.SelectRows(Excel.CurrentWorkbook() , each Text.StartsWith([Name], "t"))[Content],
    All_Data = Table.Combine(List.Transform(Source, (x)=> Table.RemoveLastN(x,1))),
    cols = List.RemoveLastN(List.Skip(Table.ColumnNames(All_Data),1),2),
    #"Grouped Rows" = Table.ExpandTableColumn(Table.Group(All_Data, {"Player"}, {{"x",
                      each Table.FromRows({List.Accumulate(cols, {}, (s,c)=> s & {List.Sum(Table.Column(_,c))})}, cols)}}), "x", cols),
    #"Added Sh% column" = Table.AddColumn(#"Grouped Rows", "Sh%", each [G]/[Shots]),
    #"Added FO% column" = Table.AddColumn(#"Added Sh% column", "FO%", each [FOW] / ([FOW]+[FOL]))
in
    #"Added FO% column"

• Then, load and Close To > Table and select your output sheet.
• Change columns K and L to percentage and add total – Table Design – Total Row and update Total as your year table.
• To update Query: Data > Refresh All.

Regards,