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


--
norumbegan
------------------------------------------------------------------------
norumbegan's Profile: http://www.excelforum.com/member.php...o&userid=37495
View this thread: http://www.excelforum.com/showthread...hreadid=571497

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" wrote:


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


--
norumbegan
------------------------------------------------------------------------
norumbegan's Profile: http://www.excelforum.com/member.php...o&userid=37495
View this thread: http://www.excelforum.com/showthread...hreadid=571497

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.

-norumbegan

Barb Reinhardt Wrote:
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" wrote:


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?




--
norumbegan
------------------------------------------------------------------------
norumbegan's Profile: http://www.excelforum.com/member.php...o&userid=37495
View this thread: http://www.excelforum.com/showthread...hreadid=571497

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

"norumbegan" wrote:


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


--
norumbegan
------------------------------------------------------------------------
norumbegan's Profile: http://www.excelforum.com/member.php...o&userid=37495
View this thread: http://www.excelforum.com/showthread...hreadid=571497

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:A101,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.

.. 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:A101,A1:A10))}
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---