extract unique numbers

Using your posted workbook....
This regular formula, copied down as far as you need, lists the unique numbers in E4:J13 (in descending order)

If you are using Excel 2010 or greater....

M2: =IFERROR(AGGREGATE(14,6,INDEX($E$4:$J$13/($E$4:$J$13<>"")
/(COUNTIFS($E$4:$J$13,$E$4:$J$13)=1),0,0),ROWS($2:2)),"")

Otherwise, use this regular formula:

M2: =IF(ROWS($2:2)<=SUMPRODUCT(--(COUNTIF($E$4:$J$13,$E$4:$J$13&"")=1))
,LARGE(INDEX(($E$4:$J$13+0)+(($E$4:$J$13="")*-99^99)+(COUNTIFS($E$4:$J$13,$E$4:$J$13)<>1)*-99^99,0,0)
,ROWS($2:2)),"")

Note: the formulas count rows beginning in Row_2 (the location of the first formula). If you put the formula in a different row, the ROWS($2:2) section must be adjusted.

With your data, these are the results:

M2:	32
M3:	26
M4:	23
M5:	19
M6:	14
M7:	7
M8:	6
M9:	5
M10:	2
M11:	1
M12:	
M13:

Is that something you can work with?

Or maybe this (a bit shorter, if nothing else!!), if you are still using Excel 2007:

=IFERROR(SMALL(IF(COUNTIF($A$1:$I$4,$A$1:$I$4)=1,$A$1:$I$4),ROWS(M$2:M2)),"")

This is an array formula.
Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.


Don't type the curly brackets yourself - it won't work...

If you do have Excel 2010 or later a) please amend your profile and b) use Ron's first suggestion, or this (just enter):

=IFERROR(AGGREGATE(15,6,$E$4:$J$13/(COUNTIF($E$4:$J$13,$E$4:$J$13)=1),ROWS(M$2:M2)),"")

Glenn:

My biggest challenge on this thread was accommodating a unique zero, if they're a posibility.
(I can see that I got a bit aggressive with the unnecessary INDEX(ref,0,0) in my AGGREGATE option, though)

Originally Posted by Ron Coderre

My biggest challenge on this thread was accommodating a unique zero, if they're a posibility.

Good observation, Ron. Using Glenn's first formula, we can replace the part:

COUNTIF($E$4:$J$13,$E$4:$J$13)

with

COUNTIF($E$4:$J$13,$E$4:$J$13&"")

to circumvent this issue, I believe.

Regards

Yes....That fixes it.
Interesting when a thread becomes collaborative to the benefit of all.

Great! Thanks for that, a) Ron for pointing it out and b) XOR LX for the fix.