Generate Top 10 Using 3 Columns Specific To Location

[ExcelNoob_111]

Good Day.

I need assistance generating a Top 10 ranking using 3 columns specific to location. I would like for the results to show the Top 10 for each location after a data refresh on a daily basis. At this time, the data table (Name/Location/Total Points) which is located on another tab of a large workbook is refreshed using a macro. The points are accumulated on this tab after manual entry of activities performed on another tab. Once I refresh the data, this list will perform an update and order all of the performers from highest to lowest (adding all of their activity points). I would like to take this list and break it down by location, instead of everyone in one list. I'm at a loss as to how to develop a formula to look at the location column to develop a list using the points and name. If a dropdown feature is possible to create a top 10 list for "X" location, that would be suffice. But having a standard top 10 list based on location will do (maybe through a VLOOKUP).

Also, is there a way to avoid duplication of points in one location that one name would be ignored, but to bump it down one spot (either alphabetically, or by some other criteria)? I've encountered issues with duplicated points but only one name was chosen and the other ignored, not even listed.

Mind you, this list is only a sample size, there are close to 200 names, and possibly growing, but I can just change the range if I were to get a working formula.

If possible, the end result should be a separate Top 10 for Main, Tier 1, Tier 2, and Urban.

Or is this too complicated for a common formula and a macro may be needed?

Book1
	B	C	D
3	Name	Location	Total Points
4	Wayne	Urban	7
5	B.T.	Main	52
6	Oleksei	Urban	891
7	Wade	Main	86
8	Vlad	Main	568
9	Jeff	Urban	219
10	Victory	Main	21
11	Yoshii	Tier 2	598
12	Brian	Main	659
13	T.K.	Main	512
14	Takeshi	Tier 1	9
15	Kurt	Main	51
16	Valentino	Main	14
17	Abdul	Urban	96
18	Mike	Urban	27
19	Sonny	Main	36
20	Tommy	Main	25
21	Paul	Urban	83
22	Pete	Main	47
23	Robert	Urban	55
24	Dennis	Main	43
25	Sean	Tier 2	10
26	Jean	Urban	8
27	Archie	Main	137
28	Kenny	Main	26
29	Paco	Tier 2	55
30	T.M.G.	Tier 2	7
31	Simone	Main	533
32	Rosita	Tier 1	91
33	Mia	Tier 2	25
34	Sonya	Tier 1	846
35	Pierre	Tier 2	219
36	Terri	Tier 2	81
37	Ryan	Urban	51
38	Prisha	Main	63
39	Sven	Tier 2	46
40	Acura	Tier 1	91
Sheet1

Thanks to any and all whom may be of assistance.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[ExcelNoob_111]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Updated profile

Office 365, Windows

 

[Fluff]

Thanks for that.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2
3		Name	Location	Total Points			Main				Urban
4		Wayne	Urban	7			Brian	659			Main
5		B.T.	Main	52			Vlad	568			Tier 2
6		Oleksei	Urban	891			Simone	533			Tier 1
7		Wade	Main	86			T.K.	512
8		Vlad	Main	568			Archie	137
9		Jeff	Urban	219			Wade	86
10		Victory	Main	21			Prisha	63
11		Yoshii	Tier 2	598			B.T.	52
12		Brian	Main	659			Kurt	51
13		T.K.	Main	512			Pete	47
14		Takeshi	Tier 1	9
15		Kurt	Main	51
16		Valentino	Main	14
17		Abdul	Urban	96
18		Mike	Urban	27
19		Sonny	Main	36
20		Tommy	Main	25
21		Paul	Urban	83
22		Pete	Main	47
23		Robert	Urban	55
24		Dennis	Main	43
25		Sean	Tier 2	10
26		Jean	Urban	8
27		Archie	Main	137
28		Kenny	Main	26
29		Paco	Tier 2	55
30		T.M.G.	Tier 2	7
31		Simone	Main	533
32		Rosita	Tier 1	91
33		Mia	Tier 2	25
34		Sonya	Tier 1	846
35		Pierre	Tier 2	219
36		Terri	Tier 2	81
37		Ryan	Urban	51
38		Prisha	Main	63
39		Sven	Tier 2	46
40		Acura	Tier 1	91
Main

Cell Formulas
Range	Formula
K3:K6	K3	=UNIQUE(C4:C40)
G4:H13	G4	=LET(s,SORT(FILTER(B4:D40,C4:C40=G3),3,-1),INDEX(s,SEQUENCE(MIN(ROWS(s),10)),{1,3}))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
G3	List	=K3#

 

[ExcelNoob_111]

Thanks for that.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2
3		Name	Location	Total Points			Main				Urban
4		Wayne	Urban	7			Brian	659			Main
5		B.T.	Main	52			Vlad	568			Tier 2
6		Oleksei	Urban	891			Simone	533			Tier 1
7		Wade	Main	86			T.K.	512
8		Vlad	Main	568			Archie	137
9		Jeff	Urban	219			Wade	86
10		Victory	Main	21			Prisha	63
11		Yoshii	Tier 2	598			B.T.	52
12		Brian	Main	659			Kurt	51
13		T.K.	Main	512			Pete	47
14		Takeshi	Tier 1	9
15		Kurt	Main	51
16		Valentino	Main	14
17		Abdul	Urban	96
18		Mike	Urban	27
19		Sonny	Main	36
20		Tommy	Main	25
21		Paul	Urban	83
22		Pete	Main	47
23		Robert	Urban	55
24		Dennis	Main	43
25		Sean	Tier 2	10
26		Jean	Urban	8
27		Archie	Main	137
28		Kenny	Main	26
29		Paco	Tier 2	55
30		T.M.G.	Tier 2	7
31		Simone	Main	533
32		Rosita	Tier 1	91
33		Mia	Tier 2	25
34		Sonya	Tier 1	846
35		Pierre	Tier 2	219
36		Terri	Tier 2	81
37		Ryan	Urban	51
38		Prisha	Main	63
39		Sven	Tier 2	46
40		Acura	Tier 1	91
Main

Cell Formulas
Range	Formula
K3:K6	K3	=UNIQUE(C4:C40)
G4:H13	G4	=LET(s,SORT(FILTER(B4:D40,C4:C40=G3),3,-1),INDEX(s,SEQUENCE(MIN(ROWS(s),10)),{1,3}))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
G3	List	=K3#

Thanks for this start. I am receiving a #SPILL! result in range G4:H13 when I complete the formulas. Cell H4 is blank. When I toggle the dropdown, all of the locations return the same #SPILL! response.

 

[Fluff]

Clear the cells in G4:H13 with the exception of G4

 

[ExcelNoob_111]

Clear the cells in G4:H13 with the exception of G4

Thank you. This formula works perfect, but then I discovered that I came up short of data in the table, and forgot to include weight class and sex.

So the dropdown feature worked perfect for what you initially provided, I thank you for this start. But I need the same feature to include weight and sex.

I've been experimenting with the formula in cell G4 to include multiple criteria. I attempted to enter another array for weight and another for sex, both pointing to their own Data Validation cells, basically mirroring cell K3, but I receive the pop error message that, "there are too few arguments for the function".

Is there a way to update the formula that goes into cell G4 to include these additional options?

So if I wish for my results to show all NAMES in the LIGHT WEIGHT CLASS as the MALE SEX in the MAIN LOCATION, my Top list should show but by way of the dropdown method as done for location, see example below:

Sample Top 10.xlsx
	B	C	D	E	F
3	Name	Weight Class	Sex	Location	Total Points
7	Brian	Light	Male	Main	659
10	Archie	Light	Male	Main	137
12	Wade	Light	Male	Main	86
15	Kurt	Light	Male	Main	51
19	Pete	Light	Male	Main	47
22	Sonny	Light	Male	Main	36
27	Kenny	Light	Male	Main	26
28	Victory	Light	Male	Main	21
Sheet3

Here is the data for the new table with the additional columns:

Sample Top 10.xlsx
	B	C	D	E	F
3	Name	Weight Class	Sex	Location	Total Points
4	Wayne	Heavy	Male	Urban	7
5	B.T.	Heavy	Male	Main	52
6	Oleksei	Heavy	Male	Urban	891
7	Wade	Light	Male	Main	86
8	Vlad	Heavy	Male	Main	568
9	Jeff	Heavy	Male	Urban	219
10	Victory	Light	Male	Main	21
11	Yoshii	Heavy	Male	Tier 2	598
12	Brian	Light	Male	Main	659
13	T.K.	Heavy	Male	Main	512
14	Takeshi	Heavy	Male	Tier 1	9
15	Kurt	Light	Male	Main	51
16	Valentino	Heavy	Male	Main	14
17	Abdul	Light	Male	Urban	96
18	Mike	Heavy	Male	Urban	27
19	Sonny	Light	Male	Main	36
20	Tommy	Heavy	Male	Main	25
21	Paul	Light	Male	Urban	83
22	Pete	Light	Male	Main	47
23	Robert	Heavy	Male	Urban	55
24	Dennis	Heavy	Male	Main	43
25	Sean	Light	Male	Tier 2	10
26	Jean	Light	Female	Urban	8
27	Archie	Light	Male	Main	137
28	Kenny	Light	Male	Main	26
29	Paco	Light	Male	Tier 2	55
30	T.M.G.	Heavy	Male	Tier 2	7
31	Simone	Light	Female	Main	533
32	Rosita	Light	Female	Tier 1	91
33	Mia	Heavy	Female	Tier 2	25
34	Sonya	Light	Female	Tier 1	846
35	Pierre	Heavy	Male	Tier 2	219
36	Terri	Light	Female	Tier 2	81
37	Ryan	Light	Male	Urban	51
38	Prisha	Light	Female	Main	63
39	Sven	Heavy	Male	Tier 2	46
40	Acura	Heavy	Male	Tier 1	91
Sheet3

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2
3		Name	Weight Class	Sex	Location	Total Points		Light	Male	Main				Heavy	Male	Urban
4		Wayne	Heavy	Male	Urban	7		Brian	Light	Male	Main	659		Light	Female	Main
5		B.T.	Heavy	Male	Main	52		Archie	Light	Male	Main	137				Tier 2
6		Oleksei	Heavy	Male	Urban	891		Wade	Light	Male	Main	86				Tier 1
7		Wade	Light	Male	Main	86		Kurt	Light	Male	Main	51
8		Vlad	Heavy	Male	Main	568		Pete	Light	Male	Main	47
9		Jeff	Heavy	Male	Urban	219		Sonny	Light	Male	Main	36
10		Victory	Light	Male	Main	21		Kenny	Light	Male	Main	26
11		Yoshii	Heavy	Male	Tier 2	598		Victory	Light	Male	Main	21
12		Brian	Light	Male	Main	659
13		T.K.	Heavy	Male	Main	512
14		Takeshi	Heavy	Male	Tier 1	9
15		Kurt	Light	Male	Main	51
16		Valentino	Heavy	Male	Main	14
17		Abdul	Light	Male	Urban	96
18		Mike	Heavy	Male	Urban	27
19		Sonny	Light	Male	Main	36
20		Tommy	Heavy	Male	Main	25
21		Paul	Light	Male	Urban	83
22		Pete	Light	Male	Main	47
23		Robert	Heavy	Male	Urban	55
24		Dennis	Heavy	Male	Main	43
25		Sean	Light	Male	Tier 2	10
26		Jean	Light	Female	Urban	8
27		Archie	Light	Male	Main	137
28		Kenny	Light	Male	Main	26
29		Paco	Light	Male	Tier 2	55
30		T.M.G.	Heavy	Male	Tier 2	7
31		Simone	Light	Female	Main	533
32		Rosita	Light	Female	Tier 1	91
33		Mia	Heavy	Female	Tier 2	25
34		Sonya	Light	Female	Tier 1	846
35		Pierre	Heavy	Male	Tier 2	219
36		Terri	Light	Female	Tier 2	81
37		Ryan	Light	Male	Urban	51
38		Prisha	Light	Female	Main	63
39		Sven	Heavy	Male	Tier 2	46
40		Acura	Heavy	Male	Tier 1	91
Main

Cell Formulas
Range	Formula
P3:P6,N3:O4	N3	=UNIQUE(C4:C40)
H4:L11	H4	=LET(s,SORT(FILTER(B4:F40,(IF(H3="",1,C4:C40=H3))*(IF(I3="",1,D4:D40=I3))*(IF(J3="",1,E4:E40=J3)),""),5,-1),INDEX(s,SEQUENCE(MIN(ROWS(s),10)),SEQUENCE(,5)))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
H3:J3	List	=N3#

 

[ExcelNoob_111]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2
3		Name	Weight Class	Sex	Location	Total Points		Light	Male	Main				Heavy	Male	Urban
4		Wayne	Heavy	Male	Urban	7		Brian	Light	Male	Main	659		Light	Female	Main
5		B.T.	Heavy	Male	Main	52		Archie	Light	Male	Main	137				Tier 2
6		Oleksei	Heavy	Male	Urban	891		Wade	Light	Male	Main	86				Tier 1
7		Wade	Light	Male	Main	86		Kurt	Light	Male	Main	51
8		Vlad	Heavy	Male	Main	568		Pete	Light	Male	Main	47
9		Jeff	Heavy	Male	Urban	219		Sonny	Light	Male	Main	36
10		Victory	Light	Male	Main	21		Kenny	Light	Male	Main	26
11		Yoshii	Heavy	Male	Tier 2	598		Victory	Light	Male	Main	21
12		Brian	Light	Male	Main	659
13		T.K.	Heavy	Male	Main	512
14		Takeshi	Heavy	Male	Tier 1	9
15		Kurt	Light	Male	Main	51
16		Valentino	Heavy	Male	Main	14
17		Abdul	Light	Male	Urban	96
18		Mike	Heavy	Male	Urban	27
19		Sonny	Light	Male	Main	36
20		Tommy	Heavy	Male	Main	25
21		Paul	Light	Male	Urban	83
22		Pete	Light	Male	Main	47
23		Robert	Heavy	Male	Urban	55
24		Dennis	Heavy	Male	Main	43
25		Sean	Light	Male	Tier 2	10
26		Jean	Light	Female	Urban	8
27		Archie	Light	Male	Main	137
28		Kenny	Light	Male	Main	26
29		Paco	Light	Male	Tier 2	55
30		T.M.G.	Heavy	Male	Tier 2	7
31		Simone	Light	Female	Main	533
32		Rosita	Light	Female	Tier 1	91
33		Mia	Heavy	Female	Tier 2	25
34		Sonya	Light	Female	Tier 1	846
35		Pierre	Heavy	Male	Tier 2	219
36		Terri	Light	Female	Tier 2	81
37		Ryan	Light	Male	Urban	51
38		Prisha	Light	Female	Main	63
39		Sven	Heavy	Male	Tier 2	46
40		Acura	Heavy	Male	Tier 1	91
Main

Cell Formulas
Range	Formula
P3:P6,N3:O4	N3	=UNIQUE(C4:C40)
H4:L11	H4	=LET(s,SORT(FILTER(B4:F40,(IF(H3="",1,C4:C40=H3))*(IF(I3="",1,D4:D40=I3))*(IF(J3="",1,E4:E40=J3)),""),5,-1),INDEX(s,SEQUENCE(MIN(ROWS(s),10)),SEQUENCE(,5)))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
H3:J3	List	=N3#

This is great. It works. Yet, the results show all 5 columns. I only need the name and points to show.

Is there a tweak to the formula that would show just columns 1 and 5 after selecting the dropdown options?

 

[Fluff]

I only need the name and points to show.

Then why didn't you say so, rather posting a sheet showing all the columns?

Are you going to be making any other changes, or is this it?