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

[norumbegan]

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

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?

>

[JBoulton]

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

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

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