Identify & List unique values from a list using functions/formulas

[Guest]

I have a list that is going to constantly be changing. One time the list may
have 185 records & next time the list might have 18,212 records. I need to
evaluate the values in a column & return a list of only the unique values in
the column !!!without using filters, pivot tables or any menu items - it has
to be formulas or arrays ONLY because my users wouldn't know how to handle
the pivot tables or follow directions from the menu!!!

For example...

3.03
3.03
3.5
3.57
3.99
3.99
3.99
4.0
4.0
4.1
4.3
4.33

The result would be:

3.03
3.5
3.57
3.99
4.0
4.1
4.3
4.33

 

[Guest]

In B1 enter:
=A1
In B2 enter:
=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)))

as an array formula (CNTRL-SHIFT-ENTER0

copy B2 down as far as you need

 

[Gord Dibben]

How about you set up some macro and all your users need to know is how to click
on a button?


Gord Dibben MS Excel MVP

 

[T. Valko]

Since the data is numeric and is in ascending order here's a non-array
formula method.

Assume the numbers are in a named range called rng that starts in A2. You
want to extract the uniques starting in cell C2. Cell C1 is a header (or can
be empty, just can't be a number).

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

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

Or, use another cell to get the count of uniques and then refer to that cell
(will help with calc time):

B2:

=COUNT(1/FREQUENCY(rng,rng))

C2:

=IF(ROWS($1:1)<=B$2,SMALL(rng,SUMPRODUCT(COUNTIF(rng,C$1:C1))+1),"")

Biff