eliminate repeat values from a list of ascending order

[norumbegan]

Hi,

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.

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?

Thanks,
norumbegan

 

[Barb Reinhardt]

I can't think of a solution to the first part off the top of my head.

What were you thinking about for conditions of the MIN or MAX?

 

[norumbegan]

Say I have the same column of numbers, A1:A10. What I want to do is se
a condition such that certain values are disregarded when calculatin
the maximum. For example, say I want the maximum cell value from th
column such that the value is less than 4.0. As before, the order an
range of the column changes, as does the desired maximum value.

The same question goes for finding the minimum value from the list suc
that it's greater than a given number -- I figure the formulas will b
very similar.

-norumbegan

 

[JBoulton]

Have you tried data/filter/advanced with unique values?

 

[Max]

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>

:

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

 

[Max]

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

The formula should register & appear like this in the formula bar,
with CSE correctly done:

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

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