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

 

[valmir]

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))
[
[/QUOTE]

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

Hi Kaper! Thanks a lot for your help. Regarding the VBA code, could it be because I have different regional settings that it is not working on my side?
Regarding the formulas you provided, for the first one, I replaced commas by semi-colons, due to regional settings, tried the corrected formula as

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

and I got a "the formula is missing an opening or closing parethesis" error message.
Next, I deleted the headers and tried the second formula in G2 and it worked. However, it gave me the intended result for round 3 (round 1+round 2+round 3). What changes do I have to make to the second formula so that I can get the result up until round 2 (round 1 + round 2)? What about round 4? Please explain the logic so that I can change the formula for all the rounds. Besides, can you change the formula so that I get columns in following order: team, name, goals.
This is how I corrected the second formula due to regional settings:

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

The second formula works for a any number of rounds.
with semicolons (by the way it is also my regional setting) it is:

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

the first one with semicolons shall read:

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

and it also calculates properly for any number of rounds

As for the columns sequence, I will do that later (going offline at the moment). Probably I'll move Choosecols between first and second argument of HSTACK and in first I'll choose only second (club) column from groupingby, and in second I'll use whole groupby results - name and sum of goals columns):

=LET(a;FILTER(B:D;C:C<>"");SORT(HSTACK(CHOOSECOLS(GROUPBY(CHOOSECOLS(a;2);CHOOSECOLS(a;1);LAMBDA(b;ARRAYTOTEXT(UNIQUE(b)));3;0);2);GROUPBY(CHOOSECOLS(a;2);CHOOSECOLS(a;3);SUM;3;0));3;-1))

 

[valmir]

Perfect! Tested and working. The second formula seems easier to me. Now, anyway to change columns order to club, name, goals?

 

[Kaper]

Have you seen my last edit (3rd formula above):

=LET(a;FILTER(B:D;C:C<>"");SORT(HSTACK(CHOOSECOLS(GROUPBY(CHOOSECOLS(a;2);CHOOSECOLS(a;1);LAMBDA(b;ARRAYTOTEXT(UNIQUE(b)));3;0);2);GROUPBY(CHOOSECOLS(a;2);CHOOSECOLS(a;3);SUM;3;0));3;-1))

 

[valmir]

Have you seen my last edit (3rd formula above):

=LET(a;FILTER(B:D;C:C<>"");SORT(HSTACK(CHOOSECOLS(GROUPBY(CHOOSECOLS(a;2);CHOOSECOLS(a;1);LAMBDA(b;ARRAYTOTEXT(UNIQUE(b)));3;0);2);GROUPBY(CHOOSECOLS(a;2);CHOOSECOLS(a;3);SUM;3;0));3;-1))

Sorry Kaper. I'm still a bit slow. Thanks a lot (for the patience too). You saved my day!

 

[valmir]

Hi Kaper! One more thing and please don't be angry . The first formula is too complicated for me and the second formula works perfectly. Actually, not only complicated but when I compared the results of both formulas for round 3, formula one had one row short. Anyway, please can you swap the order of columns B and C in formula 2, as you did in formula 1, since formula 2 is the one that is working flawlessly for me? I understand that you swapped the order of columns B and C in the first formula, only! Thanks!

 

[valmir]

Hi Kaper! One more thing and please don't be angry . The first formula is too complicated for me and the second formula works perfectly. Actually, not only complicated but when I compared the results of both formulas for round 3, formula one had one row short. Anyway, please can you swap the order of columns B and C in formula 2, as you did in formula 1, since formula 2 is the one that is working flawlessly for me? I understand that you swapped the order of columns B and C in the first formula, only! Thanks!

I got it!

=LET(a; FILTER(B:D; (A:A<=F2)*(C:C<>""));SORT(CHOOSECOLS(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));2; 1; 3);3; -1))

 

[Kaper]

Glad to hear that, and thanks for marking the solution