How to find out the 3 highest occurrence of number from a list?

[Eric]

Does anyone have any suggestions on how to find out the first, second, and
third highest occurrence of number from a list?

There is a sorted list of numbers in ascending order under column A,

3,3,3,3,3,4,4,5,5,5,6,6,6,6,6,7,7,9,9,9,10,10,10,10,10,11,11,11,11,11,
12,12,12,12,12,12

Since there is 6 occurrence for 12, and there are 5 occurrence for 3,6,10
Then it should return the 12 in cell B1, and 3 in cell C1, and 6 in cell D1,
and ignore 10, because I would like to select the 3 numbers only from a list.
Does anyone have any suggesitons?
Thank in advance for any suggestions
Eric

 

[Evan Weiner]

Use the LARGE function after reducing the list to unique terms with the
advanced filter.

 

[Bob Phillips]

=MAX(A1:A20)

=LARGE(IF(MATCH($A$1:$A$20,$A$1:$A$20,0)=ROW($A$1:$A$20)-CELL("Row",$A$1:$A$20)+1,$A$1:$A$20),2)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

and

=LARGE(IF(MATCH($A$1:$A$20,$A$1:$A$20,0)=ROW($A$1:$A$20)-CELL("Row",$A$1:$A$20)+1,$A$1:$A$20),3)

also an array formula

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Ron Coderre]

With your posted data list in A1:A36

Try this ARRAY FORMULA (committed with Ctrl+Shift+Enter, instead of Enter):

C1:
=INDEX($A$1:$A$40,MATCH(LARGE(IF(($A$1:$A$40<>""),IF(MATCH($A$1:$A$40,$A$1:$A$40,0)=ROW($A$1:$A$40),COUNTIF($A$1:$A$40,$A$1:$A$40)+1/ROW($A$1:$A$40)/1000)),ROW()),IF(($A$1:$A$40<>""),IF(MATCH($A$1:$A$40,$A$1:$A$40,0)=ROW($A$1:$A$40),COUNTIF($A$1:$A$40,$A$1:$A$40)+1/ROW($A$1:$A$40)/1000)),0))

Note_1: There are NO spaces in that formula
Note_2: I had that formula references A1:A40 so that it includes blank cells
to ensure that they don't trip up its calculation

Note_3: If that formula returns the values you're looking for....we can see
about making it more elegant.

Does that help?
***********
Regards,
Ron

XL2003, WinXP

 

[Ron Coderre]

Addendum to the instructions:

Copy C1 and paste into C2 and down as far as you need

***********
Regards,
Ron

XL2003, WinXP

 

[Ron Coderre]

Another "one more thing" : \

I just noticed that you wanted the values to list ACROSS....not Down.

C1:
=INDEX($A$1:$A$40,MATCH(LARGE(IF(($A$1:$A$40<>""),IF(MATCH($A$1:$A$40,$A$1:$A$40,0)=ROW($A$1:$A$40),COUNTIF($A$1:$A$40,$A$1:$A$40)+1/ROW($A$1:$A$40)/1000)),COLUMNS($B:B)),IF(($A$1:$A$40<>""),IF(MATCH($A$1:$A$40,$A$1:$A$40,0)=ROW($A$1:$A$40),COUNTIF($A$1:$A$40,$A$1:$A$40)+1/ROW($A$1:$A$40)/1000)),0))

Remember to commit with Ctrl+Shift+Enter

Copy C1 in to D1 and E1

Using your posted data....
C1 returns 12
D1 returns 3
E1 returns 6

I hope that helps.
***********
Regards,
Ron

XL2003, WinXP