extract unique numbers

[louis128]

Hi guys, i need a formula which checks the box =E4:J13 and extracts only the unique numbers (appear only ones).Thanks so much!

[Ron Coderre]

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....

Please Login or Register  to view this content.

Otherwise, use this regular formula:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

Is that something you can work with?

[Glenn Kennedy]

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)),"")

[Ron Coderre]

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)

[XOR LX]

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

[Ron Coderre]

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

[Glenn Kennedy]

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

[louis128]

Great thanks so much my friends!!!