A formula to sort, without duplicating?

[stir-crazy]

Hi there,

I'm trying to figure out how I can post many values in one column, and then have a formula in an adjacent column return the values to me, sorted and without duplicating values.

So, if I pasted as many as 200 random, numbers in column A, some of which are duplicates (1, 5355, 5, 5, 204, 5385, 5355, etc.), can I have column B return the values, sorted and without repeating (1, 5, 204, 5355, 5385)?

I've been playing with AND, FALSE and COUNTIF commands, but I haven't been doing anything well except to confuse myself. AAAAAAA!

Thanks so much in advance!

 

[bosco_yip]

Or, trying……

Using Yogi's table, in B2 enter and copied down

1] Non array formula :

=IF(MAX($A$2:$A$8)=MAX(B$1:B1),"",SMALL($A$2:$A$8,COUNTIF($A$2:$A$8,"<="&B1)+1))

2] Array formula :

2.1] {=IF(MAX($A$2:$A$8)=MAX(B$1:B1),"",SMALL(IF(MATCH($A$2:$A$8,$A$2:$A$8,0)=ROW($A$2:$A$8)-ROW(A$1),$A$2:$A$8),ROW(1:1)))}

2.2] {=IF(MAX($A$2:$A$8)=MAX(B$1:B1),"",SMALL(IF(FREQUENCY($A$2:$A$8,$A$2:$A$8),$A$2:$A$8),ROW(1:1)))}

2.3] {=IF(MAX($A$2:$A$8)=MAX(B$1:B1),"",MIN(IF(COUNTIF(E$1:E1,$A$2:$A$8)=0,$A$2:$A$8)))}

Regards
Bosco