Top Ten formula that sorts duplicates & excludes results with "0".

[Parebody]

Looking for guidance. I am trying to set up my formula's in D3:E12 to list only the top ten (or less) quantities from column B, with their associated reject codes from column A. However, I want to include duplicate quantities with different codes, and I want to exclude codes that show no results (zero). I would also like any cells in column's D and E that are less then "1" to appear blank. Any help would be appreciated!

UPDATE: I forgot. Here's the formula in column D cells: =INDEX($A$2:$A$32,MATCH($E$3:$E$12,$B$2:$B$32,0)). And here's the formula in column E cells: {=LARGE(B2:B32,{1;2;3;4;5;6;7;8;9;10}).

 

[Fluff]

What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.

 

[Parebody]

What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.

Microsoft Excel 2016

 

[Ezguy4u]

Let's get the ball rolling. Now I had to make some changes to accommodate if there is a code with the same total. Usually this generates more questions than answers so let the discussion begin.

20-10-29 rank.xlsx
	A	B	C	D	E	F	G	H
1	Reject Code	Total		Top 10 Reject Codes			Number above 0
2	A1	16		Reject Code	Total		8
3	A2	0		16	A1
4	A3	0		12	BG
5	A6	0		10	BR
6	A8	0		10	SA
7	A9	0		6	EY
8	AV	0		4	BV
9	AZ	0		4	BX
10	B2	0		3	DG
11	B3	0
12	B4	0
13	B5	0
14	BC	0
15	BE	0
16	BG	12
17	BI	0
18	BK	0
19	BR	10
20	BS	0
21	BT	0
22	BU	0
23	BV	4
24	BX	4
25	EX	0
26	EY	6
27	DE	0
28	DF	0
29	DG	3
30	SA	10
31	CA	0
32	CB	0
Code top 10

Cell Formulas
Range	Formula
G2	G2	=COUNTIF(B2:B32,">0")
D3:D12	D3	=IF(LARGE($B$2:$B$32,ROWS($C$3:C3))>1,LARGE($B$2:$B$32,ROWS($C$3:C3)),"")
E3:E12	E3	=IF(D3<>"",INDEX($A$2:$A$32,AGGREGATE(15,6,(ROW($C$2:$C$32)-ROW($C$2)+1)/($B$2:$B$32=D3),COUNTIF($D$3:D3,D3))),"")

 

[Fluff]

Microsoft Excel 2016

Thanks for that, please don't forget to update your account details & then scroll down & click save. It saves members having to ask.

Another option

+Fluff v2.xlsm
	A	B	C	D	E
1
2	A1	16
3	A2	0		A6	23
4	A3	0		A19	19
5	A4	8		A1	16
6	A5	7		A7	16
7	A6	23		A10	12
8	A7	16		A4	8
9	A8	0		A5	7
10	A9	0		A14	5
11	A10	12		A12	4
12	A11	0
13	A12	4
14	A13	0
15	A14	5
16	A15	0
17	A16	0
18	A17	0
19	A18	0
20	A19	19
21	A20	0
22
23
Work

Cell Formulas
Range	Formula
A2:A21	A2	="A"&SEQUENCE(20)
D3:D12	D3	=IF(E3="","",INDEX($A$2:$A$32,AGGREGATE(15,6,(ROW($A$2:$A$32)-ROW($A$2)+1)/($B$2:$B$32=E3),COUNTIF(E$3:E3,E3))))
E3:E12	E3	=IFERROR(AGGREGATE(14,6,$B$2:$B$32/($B$2:$B$32>0),ROWS(E$3:E3)),"")
Dynamic array formulas.

 

[Parebody]

ezguy4u, your suggestion worked perfectly! Amazing. I watched a video on this showing how to add the countif formula and the aggregate function but could not get it to work. 2nd option works great too if I need to sequencing. Thank you ezguy4u and Fluff for your insight. This information has been a huge help! Love learning new stuff in Excel!!!