How to determine the value from a list?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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)
 
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
 
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)
 
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
 
Back
Top