How to determine the value from a list?

[Guest]

Does anyone have any suggestions on how to determine the value from a list?

22,22,25,27,27,28

I would like to determine the second smallest value, but skip checking the
repeated one, such as in this case, I should select 25 and skip 22 because
there are repeated 22 from the list.
Does anyone have any suggestions?
Thank you in advance
Eric

 

[Lori]

Try array-entered (ctrl+shift+enter to evaluate):

=MIN(IF(A1:A6>MIN(A1:A6),A1:A6))

 

[Bob Phillips]

Assuming the numbers are in M1:M6

=MIN(IF(M2:M6<>M1:M5,M2:M6))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thank everyone for suggestions

22,22,25,27,27,28,28,31,34,34,37

My intention is to list out all the numbers in ascending order without
duplication.
Could anyone give me more suggestions please?
Thank everyone
Eric

 

[Bob Phillips]

That's completely different to what you asked.

Assuming the numbers are in A1:A10.

In B1: =A1

In B2 enter this formula and copy down

=IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""),0)),"",
INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""),0)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thank everyone very much for suggestions
Best Regards
Eric

 

[T. Valko]

Here's an alternative. This will extract the unique numbers in ascending
order.

Numbers in the named range RNG. RNG = A2:A11
D1 = column header (or empty or just not a number that appears in RNG)

Entered in D2 and copied down until you get blanks:

=IF(ROWS($1:1)<=COUNT(1/FREQUENCY(rng,rng)),SMALL(rng,SUMPRODUCT(COUNTIF(rng,D$11))+1),"")

Biff