For your 1st Q ..

Assume source data in col A, from row1 down

Put in B1:
=IF(ROW(A1)>COUNT(C:C),"",INDEX(A:A,MATCH(SMALL(C:C,ROW(A1)),C:C,0)))

Put in C1:
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)>1,"",A1+ROW()/10^10))

Select B1:C1, copy down to the last row of data in col A

Col B will return the required results, ie only the unique numbers in col A
and sorted in ascending order, all results neatly bunched at the top

For your 2nd Q ..

For conditional maximum, try something like this in say E1. Formula needs to
be array-entered, ie press CTRL+SHIFT+ENTER [CSE] to confirm the formula
(instead of just pressing ENTER):

=MAX(IF(A1:A10<4,A1:A10))

Similarly, for conditional minimum, try array-entered, something like:

=MIN(IF(A1:A10>1,A1:A10))

Note that the array-entering [CSE] needs to be re-done each time should the
formula be edited. In the formula bar, look for the curly braces { } -- which
will be inserted by Excel (we don't type these braces) -- as a visual cue
that it's correctly array-entered. It's all too easy to overlook this CSE bit
in our haste to get things going! <g>
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"norumbegan" wrote:

1st Q ..
> I have several columns of numbers in no particular order. I want to
> arrange them in ascending order, but eliminate all values that are
> repeated. For example, if the original column looks like this:
> 1 - 4.1
> 2 - 1.5
> 3 - 5.9
> 4 - 5.9
> 5 - (blank)
> 6 - 4.1
> 7 - 1.5
> 8 - (blank)
> 9 - (blank)
> 10 - 4.1
>
> I want the final column to look like this:
> 1 - 1.5
> 2 - 4.1
> 3 - 5.9
>
> The values and number of repeats vary in each column, so I need a
> solution that takes this into account.

2nd Q ..
> On a related note, is there a way to return the max or min of a list,
> such that it's below a specific value?
> Say I have the same column of numbers, A1:A10. What I want to do is set
> a condition such that certain values are disregarded when calculating
> the maximum. For example, say I want the maximum cell value from the
> column such that the value is less than 4.0. As before, the order and
> range of the column changes, as does the desired maximum value.
>
> The same question goes for finding the minimum value from the list such
> that it's greater than a given number -- I figure the formulas will be
> very similar.