How to determine the value from a list?

E

Eric

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
 
B

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

Eric

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
 
B

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

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$1:D1))+1),"")

Biff
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top