Find top ten occurence numbers in a matrix

[sealpino]

Matrix like A1:D100

Each cell will be filled with any number several times randomly.
Some numbers will occur more than others.
Empty cells not to be considered


At any time I want to see in:
E1 Most common number =MODE(A1:D100) the easy part
E2 2nd most common number
E3 3rd
E4 4th
etc
etc
E10 10th most common number

How can I simply create this?

[icestationzbra]

with your data in A1:D100, enter a text header in cell E1 (for e.g., MODE), then put this CSE formula in E2:

{=MODE(IF(COUNTIF($E$1:$E1,$A$1:$D$100)=0,$A$1:$D$100))}

then, drag-fill down until you please, or there are no more numbers to show!

mind you, there should NOT be a number in cell E1.

this solution is courtesy a gentleman named T Valko / Biff, whose posts i used to follow on usenet.

[sealpino]

Thanks So much!
It works great when using the coordinates and figures that was given in my thread!
I like the way it fills #N/A when there are no more figures to count.

btw, U remind me about a good book "Polarstation Zebra" also good movie.

Though when I put in my sheet

Actually matrix is B9 : P21
Lot of numbers are filled in!

E1 is actually R9

this is made in cell R10
=MODE(IF(COUNTIF($R$9:$R$9,$B$9:$P$21)=0,$B$9:$P$21))

shift ctrl enter of course

but I get 0 (zero)

What Do I Do wrong?

[icestationzbra]

drop a $:

=MODE(IF(COUNTIF($R$9:$R9,$B$9:$P$21)=0,$B$9:$P$21))

EDIT:

similar solution provided by @daddylonglegs in the following thread, which takes care of problems with blank cells:

http://www.excelforum.com/excel-gene...y-of-data.html


EDIT:

see attached file for solution on blanks, numeric, horizontal / vertical layout, etc.

[sealpino]

Hi,
Messed it up from previous msg.

Have to find out the most common occurence numbers in a matrix.

Matrix is: B7:P19

Then I want a row with first place to tenth place of these most common numbers!
first place = C25 Second place= D25 Third place = E25 .... Tenth Place = L25

Please help me out!

[sealpino]

Thanks I did the version that was with your formula based on Daddylongs and with adding $ strings on all.

=MODE(IF(COUNTIF($C$25:C25,$B$7:$P$19)=0,IF($B$7:$P$19<>"",$B$7:$P$19)))

So it works now great!

Thanks to both of my contributors.