One way to play with ..

Assuming the sample data is in A1:A9

Put in:

B1:
=IF(COUNTIF($A$1:A1,A1)1,"",ROW())

C1:
=IF(ISERROR(SMALL(B:B,ROWS($A$1:A1))),"",INDEX(A:A ,MATCH(SMALL(B:B,ROWS($A$1
:A1)),B:B,0)))

D1:
=COUNTIF(A:A,C1)

E1:
=IF(D1=0,"",D1-ROW()/10^10)

F1:
=IF(ISERROR(LARGE($E:$E,ROWS($A$1:A1))),"",INDEX(C :C,MATCH(LARGE($E:$E,ROWS(
$A$1:A1)),$E:$E,0)))

Copy F1 across to G1

Select B1:G1, fill down to G9

Cols F & G will return the desired results:

4 3
5 3
6 2
7 1

whe

col F = unique numbers within source range A1:A9
col G = occurence count of the unique numbers

The listing in col F will be sorted in descending order by the counts in col
G. Ties, if any, will be listed in the same relative order that the unique
numbers appear in the source range.

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"DArinello" wrote
in message ...

I need to figure something out on my sheet. The “mode” function tells
you witch value repeats the most. I want to see if there is another
value that repeats just as much. Also, I want to see what value
repeats the second most.

Ex:
4
6
4
7
5
6
4
5
5

If I use the function: =mode(list). It will return, “4”. By using
=countif((list),mode(list)) I see that that repeated 3 times.

I want a way to see that “5” also repeated 3 times and then that 6 is
repeated 2 times.


--
DArinello
------------------------------------------------------------------------
DArinello's Profile:
http://www.excelforum.com/member.php...o&userid=25589
View this thread: http://www.excelforum.com/showthread...hreadid=390089