From smallest to largest without repeating

[vacho]

Hello,

I have data consisting of 20 numbers from 1 to 63. Within 20 some numbers can appear twice or even more. I want excel to sort this numbers in smallest to largest format ignoring repeating numbers.

Thank you

 

[mirabeau]

Thank mirabeau, I will give it a try this evening. The reason why I don't want macros is, I have to activate it each time numbers change. Formulas change automatically

Yeah. That's quite Ok. Thanks for the feedback.
I wasn't quite sure what you wanted, and that macro was easy and fun to write anyway.

 

[T. Valko]

Thanks, I moved the formula in an empty cell and also added -1 after ROWS(V$24:V25) in If statement as shown below and now it works just fine. I am using 2010 version.

=IF(ROWS(V$24:V25)-1>U$24,"",SMALL((D$22:H$22,D$29:H$29,D$36:H$36,D$43:H$43),FREQUENCY((D$22:H$22,D$29:H$29,D$36:H$36,D$43:H$43),V24)+1))

there is no way I could have thought of this solution, so thanks again

You shouldn't have to do that (subtract 1).

Here's a sample file that demonstrates this.

vacho.xlsx

I added a 2nd version of the formula that doesn't need to refer to a count cell.