Small Function Unique Values

B

Basketball

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

Mike H

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$10>B1,$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
 
A

Ashish Mathur

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
 
T

Teethless mama

XL-2007

=IFERROR(SMALL(IF(FREQUENCY(data,data)>0,data),ROWS($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
 
R

Ron Rosenfeld

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!

You can use a User Defined Function to return an array of unique values, and
then use this formula in some cell.

=IF(COUNT(uniques(rng))<ROWS($1:1),"",SMALL(uniques(rng),ROWS($1:1)))

If you fill down, the formula will adjust to return each unique value, sorted
from lowest to highest, and return blanks when you've "gone too far".

To enter the User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

If you need to omit blanks or zero's, that can be added to the UDF code.

=========================================
Option Explicit

Function uniques(rg As Range) As Variant
Dim c As Range
Dim u As Collection, o As Object
Dim t() As Variant, i As Long

Set u = New Collection
On Error Resume Next
For Each c In rg
u.Add c.Value, CStr(c.Value)
Next c
On Error GoTo 0

ReDim t(1 To u.Count)
For i = 1 To u.Count
t(i) = u(i)
Next i

uniques = t

End Function
=============================
--ron
 
T

T. Valko

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

Basketball

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









- Show quoted text -

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

T. Valko

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


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









- Show quoted text -

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

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