Count and Rank

[psrs0810]

I have a large data set where I am trying to count our top 10 codes. The top 10 codes today may be different come the next month. So as the quantity changes, I need to count the latest 10 highest volumes.
I have tried using countif and rank, but I am not getting the result I need.

EDIT:
I'll add a little more context.
one of the codes is 6402. This code shows up 81x's (countif). when I go to rank my top 10, I get the 81 listed for all 10 top rankings. How do I change the formula to get the 2nd, 3rd, etc. place?

 

[mrshl9898]

Something like this?

list		codes	count of codes	top codes
a		t	2​	a
a		a	3​	t
a		m	1​	m
t
t
m

count of codes (D2) =COUNTIF(A:A,C2)

top codes (E2) =INDEX($C$2:$C$4,MATCH(LARGE($D$2:$D$4,ROW(A2)-1),$D$2:$D$4,0),1)

 

[Eric W]

Assuming your codes are all numeric:

Book1
	A	B	C	D
1	List		Top 10	Count
2	6524		6429	13
3	6458		6586	13
4	6460		6558	12
5	6500		6446	11
6	6581		6467	10
7	6467		6474	9
8	6582		6572	9
9	6420		6520	9
10	6432		6473	9
11	6429		6465	9
12	6409
13	6540
14	6474
15	6537
16	6489
17	6439
18	6595
19	6572
20	6485
21	6543
Sheet3

Cell Formulas
Range	Formula
C2:C11	C2	=MODE(IF(COUNTIF(C$1:C1,A$2:A$1001)=0,A$2:A$1001*{1,1}))
D2:D11	D2	=COUNTIF(A:A,C2)
Press CTRL+SHIFT+ENTER to enter array formulas.

If they're not numeric, we can adjust the formula.

 

[psrs0810]

Something like this?

list		codes	count of codes	top codes
a		t	2​	a
a		a	3​	t
a		m	1​	m
t
t
m

count of codes (D2) =COUNTIF(A:A,C2)

top codes (E2) =INDEX($C$2:$C$4,MATCH(LARGE($D$2:$D$4,ROW(A2)-1),$D$2:$D$4,0),1)

The challenge I have with this, I have probably a 1000+ codes. I really don't want to list them out.
Thanks

 

[psrs0810]

Assuming your codes are all numeric:

Book1
	A	B	C	D
1	List		Top 10	Count
2	6524		6429	13
3	6458		6586	13
4	6460		6558	12
5	6500		6446	11
6	6581		6467	10
7	6467		6474	9
8	6582		6572	9
9	6420		6520	9
10	6432		6473	9
11	6429		6465	9
12	6409
13	6540
14	6474
15	6537
16	6489
17	6439
18	6595
19	6572
20	6485
21	6543
Sheet3

Cell Formulas
Range	Formula
C2:C11	C2	=MODE(IF(COUNTIF(C$1:C1,A$2:A$1001)=0,A$2:A$1001*{1,1}))
D2:D11	D2	=COUNTIF(A:A,C2)
Press CTRL+SHIFT+ENTER to enter array formulas.

If they're not numeric, we can adjust the formula.

I am having trouble with the first formula. I re-wrote it based on my cell structure and I am getting a #Value! response
in cell R2, I have entered =MODE(IF(COUNTIF(R$1:R1,L:L)=0,L:L*{1,1}))
Also, I am hitting ctrl+shift+enter
Thanks

 

[Eric W]

You're probably getting the #VALUE because you have non-numeric values in the column, perhaps a header in row 1? That's one reason you should use row references instead of a whole column reference:

=MODE(IF(COUNTIF(R$1:R2,L$2:L$5000)=0,L$2:L$5000*{1,1}))

Another reason is that if you have empty cells in the column, you'll get 0 as a possible code. A third reason is performance, whole column array formulas are slow. If you want to make the range longer, you could do something like:

=MODE(IF((COUNTIF(R$1:R1,L$2:L$5000)=0)*(L$2:L$5000>0),L$2:L$5000*{1,1}))

with CSE, and change the 5000 to an appropriately high row, even 1048576, although I wouldn't recommend it. Pick the row you think will be highest you'll ever see and add 10%.

 

[navic]

Another way,
If you can use helper columns.

ARRAY formula in the 'G2' cell, finished with CSE (Group unique)

=IFERROR(INDEX($A$2:$A$39,MATCH(0,COUNTIF($G$1:G1,$A$2:$A$39),0)),"")

Formula in the 'H2' cell (Count unique from the 'G' column)

=IF(G2<>"",COUNTIF($A$2:$A$39,G2),"")

Formula in the 'D2' cell (Count-Large from the 'H' column)

=IFERROR(IF(LARGE($H$2:$H$18,ROW(A1))=0,"",LARGE($H$2:$H$18,ROW(A1))),"")

Formula in the 'C2' cell (Top from the 'G:H' columns)

=INDEX($G$2:H$18,AGGREGATE(15,6,(ROW(H$2:H$18)-ROW(H$2)+1)/($H$2:$H$18=D2),COUNTIF($D$2:$D2,$D2)),1)

You need adjust the range in the formulas. Also, you can hide helper columns.