Using the small function, is there a way with one formula to get a
list of unique small values.
If I had the following:
1
1
2
4
4
6
9
11
11
13

If I look up =small(a1:10,1) it gives me 1
If I look up =small(a1:10,2) it gives me 1

What I am looking for is a formula that will give me
1
2
4
6
9
11
13

Any help would be appreciated! Thanks in advance!

Hi,

Put this in b1 =MIN(A1:A10)

then put this in b2 and enter as an array (see below for array instructions)
and drag down until the formula produces zero. Important you must array enter
BEFORE dragging down.

=MIN(IF($A$1:$A$10B1,$A$1:$A$10))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.


Mike

"Basketball" wrote:

Using the small function, is there a way with one formula to get a
list of unique small values.
If I had the following:
1
1
2
4
4
6
9
11
11
13

If I look up =small(a1:10,1) it gives me 1
If I look up =small(a1:10,2) it gives me 1

What I am looking for is a formula that will give me
1
2
4
6
9
11
13

Any help would be appreciated! Thanks in advance!

Hi,

Select the range and then go to Data Filter Advanced Filter. IN the
list range, select the range, leave the criteria blank. In the copy to box,
give a cell reference and then check the box for unique records only. This
will extract all the unique records.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Basketball" wrote in message
...
Using the small function, is there a way with one formula to get a
list of unique small values.
If I had the following:
1
1
2
4
4
6
9
11
11
13

If I look up =small(a1:10,1) it gives me 1
If I look up =small(a1:10,2) it gives me 1

What I am looking for is a formula that will give me
1
2
4
6
9
11
13

Any help would be appreciated! Thanks in advance!

XL-2007

=IFERROR(SMALL(IF(FREQUENCY(data,data)0,data),ROW S($1:1)),"")

ctrl+shift+enter, not just enter
copy down


Prior to XL-2007:

=IF(SUMPRODUCT(1/COUNTIF(data,data))=ROWS($1:1),SMALL(IF(FREQUENCY (data,data)0,data),ROWS($1:1)),"")

ctrl+shift+enter, not just enter
copy down


"Basketball" wrote:

Using the small function, is there a way with one formula to get a
list of unique small values.
If I had the following:
1
1
2
4
4
6
9
11
11
13

If I look up =small(a1:10,1) it gives me 1
If I look up =small(a1:10,2) it gives me 1

What I am looking for is a formula that will give me
1
2
4
6
9
11
13

Any help would be appreciated! Thanks in advance!

Here's another one...

Data in the range A1:A10

Enter this formula in C1:

=MIN(A1:A10)

Enter this array formula** in C2 and copy down until you get blanks:

=IF(OR(C$1:C1=MAX(A$1:A$10)),"",SMALL(A$1:A$10,SUM (COUNTIF(A$1:A$10,C$1:C1))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Basketball" wrote in message
...
Using the small function, is there a way with one formula to get a
list of unique small values.
If I had the following:
1
1
2
4
4
6
9
11
11
13

If I look up =small(a1:10,1) it gives me 1
If I look up =small(a1:10,2) it gives me 1

What I am looking for is a formula that will give me
1
2
4
6
9
11
13

Any help would be appreciated! Thanks in advance!

On Sep 10, 2:07*pm, "T. Valko" wrote:
Here's another one...

Data in the range A1:A10

Enter this formula in C1:

=MIN(A1:A10)

Enter this array formula** in C2 and copy down until you get blanks:

=IF(OR(C$1:C1=MAX(A$1:A$10)),"",SMALL(A$1:A$10,SUM (COUNTIF(A$1:A$10,C$1:C1)*)+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP

"Basketball" wrote in message

...



Using the small function, is there a way with one formula to get a
list of unique small values.
If I had the following:
1
1
2
4
4
6
9
11
11
13

If I look up =small(a1:10,1) it gives me 1
If I look up =small(a1:10,2) it gives me 1

What I am looking for is a formula that will give me
1
2
4
6
9
11
13

Any help would be appreciated! Thanks in advance!- Hide quoted text -

- Show quoted text -

Thank you all for your help. I greatly appreciate it!

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Basketball" wrote in message
...
On Sep 10, 2:07 pm, "T. Valko" wrote:
Here's another one...

Data in the range A1:A10

Enter this formula in C1:

=MIN(A1:A10)

Enter this array formula** in C2 and copy down until you get blanks:

=IF(OR(C$1:C1=MAX(A$1:A$10)),"",SMALL(A$1:A$10,SUM (COUNTIF(A$1:A$10,C$1:C1)*)+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP

"Basketball" wrote in message

...



Using the small function, is there a way with one formula to get a
list of unique small values.
If I had the following:
1
1
2
4
4
6
9
11
11
13

If I look up =small(a1:10,1) it gives me 1
If I look up =small(a1:10,2) it gives me 1

What I am looking for is a formula that will give me
1
2
4
6
9
11
13

Any help would be appreciated! Thanks in advance!- Hide quoted text -

- Show quoted text -

Thank you all for your help. I greatly appreciate it!