Top 3 and bottom 3 including Stubs

[sksanjeev786]

Hi Team
I need top 3 and Bottom 3 incliuding stubs (DE,DF,DG,DH )

FS_Taiwan.xlsx
	D	E	F	G	H
12	Canada Goose	37	28	26	27
13	Moncler	44	27	28	33
14	Burberry	31	21	16	23
15	The North Face	28	23	24	24
16	Patagonia	28	21	21	21
17	Arc'teryx	15	9	12	13
18	Columbia	41	26	31	32
19	Duvetica	31	22	19	19
20	Mammut	32	18	17	21
21	Aigle	24	16	11	15
22	Herno	24	18	19	18
23	Moose Knuckles	38	27	11	11
24	Parajumpers	28	12	6	6
25	Woolrich	17	15	8	8
Sheet1

 

[Eric W]

You need to include more information with your question. What's a "stub"? How do you determine the top 3? Bottom 3? Do you do it one column at a time, so you get 4 result columns, or are they combined somehow? What if there are ties (Aigle and Herno)? Can you present a mini-sheet where you have manually created the results you want to see?

If you have LET, here's one way to get the top 3/bottom 3 for the first column:

Book1
	D	E	F	G	H	I	J	K
12	Canada Goose	37	28	26	27		Moncler	44
13	Moncler	44	27	28	33		Columbia	41
14	Burberry	31	21	16	23		Moose Knuckles	38
15	The North Face	28	23	24	24		Herno	24
16	Patagonia	28	21	21	21		Woolrich	17
17	Arc'teryx	15	9	12	13		Arc'teryx	15
18	Columbia	41	26	31	32
19	Duvetica	31	22	19	19
20	Mammut	32	18	17	21
21	Aigle	24	16	11	15
22	Herno	24	18	19	18
23	Moose Knuckles	38	27	11	11
24	Parajumpers	28	12	6	6
25	Woolrich	17	15	8	8
Sheet4

Cell Formulas
Range	Formula
J12:K17	J12	=LET(a,SORT(D12:E25,2,-1),r,ROWS(a),s,SEQUENCE(r),FILTER(a,(s<=3)+(s>r-3)))
Dynamic array formulas.

 

[sksanjeev786]

You need to include more information with your question. What's a "stub"? How do you determine the top 3? Bottom 3? Do you do it one column at a time, so you get 4 result columns, or are they combined somehow? What if there are ties (Aigle and Herno)? Can you present a mini-sheet where you have manually created the results you want to see?

If you have LET, here's one way to get the top 3/bottom 3 for the first column:

Book1
	D	E	F	G	H	I	J	K
12	Canada Goose	37	28	26	27		Moncler	44
13	Moncler	44	27	28	33		Columbia	41
14	Burberry	31	21	16	23		Moose Knuckles	38
15	The North Face	28	23	24	24		Herno	24
16	Patagonia	28	21	21	21		Woolrich	17
17	Arc'teryx	15	9	12	13		Arc'teryx	15
18	Columbia	41	26	31	32
19	Duvetica	31	22	19	19
20	Mammut	32	18	17	21
21	Aigle	24	16	11	15
22	Herno	24	18	19	18
23	Moose Knuckles	38	27	11	11
24	Parajumpers	28	12	6	6
25	Woolrich	17	15	8	8
Sheet4

Cell Formulas
Range	Formula
J12:K17	J12	=LET(a,SORT(D12:E25,2,-1),r,ROWS(a),s,SEQUENCE(r),FILTER(a,(s<=3)+(s>r-3)))
Dynamic array formulas.

Hi Eric,

Thanks so much Eric

Sorry for the inconvenience, I meant the stub says "Canada Goose" etc so i need top 3 and bottom 3 for all the columns including "Column A names"


Regards,
Sanjeev

 

[Eric W]

Here are a few options:

Book1
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
12	Canada Goose	37	28	26	27		Moncler	44		Moncler	44		Moncler	44
13	Moncler	44	27	28	33		Columbia	41		Columbia	41		Columbia	41
14	Burberry	31	21	16	23		Moose Knuckles	38		Moose Knuckles	38		Moose Knuckles	38
15	The North Face	28	23	24	24		Herno	24		Aigle	24		Aigle	24
16	Patagonia	28	21	21	21		Woolrich	17		Herno	24		Woolrich	17
17	Arc'teryx	15	9	12	13		Arc'teryx	15		Woolrich	17		Arc'teryx	15
18	Columbia	41	26	31	32					Arc'teryx	15
19	Duvetica	31	22	19	19
20	Mammut	32	18	17	21
21	Aigle	24	16	11	15
22	Herno	24	18	19	18
23	Moose Knuckles	38	27	11	11
24	Parajumpers	28	12	6	6
25	Woolrich	17	15	8	8
26
Sheet1

Cell Formulas
Range	Formula
J12:K17	J12	=LET(stub,D12:D25,value,E12:E25,a,SORT(CHOOSE({1,2},stub,value),2,-1),r,ROWS(a),s,SEQUENCE(r),FILTER(a,(s<=3)+(s>r-3)))
M12:N18	M12	=LET(stub,D12:D25,value,E12:E25,a,SORT(CHOOSE({1,2},stub,value),2,-1),r,ROWS(a),FILTER(a,(INDEX(a,0,2)>=LARGE(value,3))+(INDEX(a,0,2)<=SMALL(value,3))))
P12:P17	P12	=INDEX($D:$D,AGGREGATE(15,6,ROW($E$12:$E$25)/($E$12:$E$25=Q12),COUNTIF($Q$12:$Q12,Q12)))
Q12:Q17	Q12	=IF(ROWS(Q$12:Q12)<=3,LARGE($E$12:$E$25,ROWS(Q$12:Q12)),SMALL($E$12:$E$25,7-ROWS(Q$12:Q12)))
Dynamic array formulas.

Formula J12: If you have LET, this is the easiest. The D12:D25 is the column with the stubs, and the E12:E25 is the column with the values. To get the list for the other columns, just change E12:E25 to F12:F25, G12:G25, etc.

Formula M12: Again if you have LET, this works almost the same as the previous formula, but it will show ties (Aigle and Herno both have 24 which is the third lowest value).

Formulas P12:Q12: If you don't have LET and you want do do this in Excel 2016, these formulas are the equivalent of the J12 formula.

 

[sksanjeev786]

Here are a few options:

Book1
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
12	Canada Goose	37	28	26	27		Moncler	44		Moncler	44		Moncler	44
13	Moncler	44	27	28	33		Columbia	41		Columbia	41		Columbia	41
14	Burberry	31	21	16	23		Moose Knuckles	38		Moose Knuckles	38		Moose Knuckles	38
15	The North Face	28	23	24	24		Herno	24		Aigle	24		Aigle	24
16	Patagonia	28	21	21	21		Woolrich	17		Herno	24		Woolrich	17
17	Arc'teryx	15	9	12	13		Arc'teryx	15		Woolrich	17		Arc'teryx	15
18	Columbia	41	26	31	32					Arc'teryx	15
19	Duvetica	31	22	19	19
20	Mammut	32	18	17	21
21	Aigle	24	16	11	15
22	Herno	24	18	19	18
23	Moose Knuckles	38	27	11	11
24	Parajumpers	28	12	6	6
25	Woolrich	17	15	8	8
26
Sheet1

Cell Formulas
Range	Formula
J12:K17	J12	=LET(stub,D12:D25,value,E12:E25,a,SORT(CHOOSE({1,2},stub,value),2,-1),r,ROWS(a),s,SEQUENCE(r),FILTER(a,(s<=3)+(s>r-3)))
M12:N18	M12	=LET(stub,D12:D25,value,E12:E25,a,SORT(CHOOSE({1,2},stub,value),2,-1),r,ROWS(a),FILTER(a,(INDEX(a,0,2)>=LARGE(value,3))+(INDEX(a,0,2)<=SMALL(value,3))))
P12:P17	P12	=INDEX($D:$D,AGGREGATE(15,6,ROW($E$12:$E$25)/($E$12:$E$25=Q12),COUNTIF($Q$12:$Q12,Q12)))
Q12:Q17	Q12	=IF(ROWS(Q$12:Q12)<=3,LARGE($E$12:$E$25,ROWS(Q$12:Q12)),SMALL($E$12:$E$25,7-ROWS(Q$12:Q12)))
Dynamic array formulas.

Formula J12: If you have LET, this is the easiest. The D12:D25 is the column with the stubs, and the E12:E25 is the column with the values. To get the list for the other columns, just change E12:E25 to F12:F25, G12:G25, etc.

Formula M12: Again if you have LET, this works almost the same as the previous formula, but it will show ties (Aigle and Herno both have 24 which is the third lowest value).

Formulas P12:Q12: If you don't have LET and you want do do this in Excel 2016, these formulas are the equivalent of the J12 formula.

Thanks you so much Eric