Return unique data from a list

[csong005]

Is there a formula to return a unique data from a list? For example, from
A2:A2000 there are names of States (i.e. CA, TX, NY, NJ, etc) that are
repeated. The list does not contain all 50 States and I need to isolate
which of the 50 states are included in the list. Thanks.

 

[Gary''s Student]

Try:

Data > Filter > AdvancedFilter > Unique records only

 

[csong005]

I know how to obtain the desired results using pivot tables and Filter, but I
looking for a formula that would accomplish the task. Thanks.

 

[Gary''s Student]

=IF(COUNTIF(A2:A2000,"CA")>0,"CA","")
=IF(COUNTIF(A2:A2000,"TX")>0,"TX","")

and continue downward. You will end up with fifty cells for the fifty
states, either visible (if they appeared in the big list) or blank

This works, but it is a painful substitute for using either pivots for
filters.

 

[Teethless mama]

It is an array formula you have to commit with ctrl+shift+enter, not just enter

=IF(ISERR(SMALL(IF(FREQUENCY(MATCH($A$2:$A$10,$A$2:$A$10,0),MATCH($A$2:$A$10,$A$2:$A$10,0))>0,ROW(INDIRECT("1:"&ROWS($A$2:$A$10)))),ROWS($1:1))),"",INDEX($A$2:$A$10,SMALL(IF(FREQUENCY(MATCH($A$2:$A$10,$A$2:$A$10,0),MATCH($A$2:$A$10,$A$2:$A$10,0))>0,ROW(INDIRECT("1:"&ROWS($A$2:$A$10)))),ROWS($1:1))))

Adjust to suit

 

[Ron Rosenfeld]

Is there a formula to return a unique data from a list? For example, from
A2:A2000 there are names of States (i.e. CA, TX, NY, NJ, etc) that are
repeated. The list does not contain all 50 States and I need to isolate
which of the 50 states are included in the list. Thanks.

You could download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

and then use the UNIQUEVALUES formula to return a sorted array of the state
abbreviations.

Some something like

=INDEX(UNIQUEVALUES(rng,1),ROWS($1:1))

copy/dragged down would return a sequential list of the abbreviations.
--ron

 

[T. Valko]

Try this:

Assumes no empty/blank cells within the range.

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=IF(ROWS($1:1)<=SUM(1/COUNTIF(A$2:A$20,A$2:A$20)),INDEX(A$2:A$20,SMALL(IF(ROW(A$2:A$20)-MIN(ROW(A$2:A$20))+1=MATCH(A$2:A$20,A$2:A$20,0),ROW(A$2:A$20)-MIN(ROW(A$2:A$20))+1),ROWS($1:1))),"")

Copy down until you get blanks

Biff

 

[Harlan Grove]

It is an array formula you have to commit with ctrl+shift+enter, not
just enter

=IF(ISERR(SMALL(IF(FREQUENCY(MATCH($A$2:$A$10,$A$2:$A$10,0),
MATCH($A$2:$A$10,$A$2:$A$10,0))>0,
ROW(INDIRECT("1:"&ROWS($A$2:$A$10)))),ROWS($1:1))),"",
INDEX($A$2:$A$10,SMALL(IF(FREQUENCY(MATCH($A$2:$A$10,$A$2:$A$10,0),
MATCH($A$2:$A$10,$A$2:$A$10,0))>0,
ROW(INDIRECT("1:"&ROWS($A$2:$A$10)))),ROWS($1:1))))

....

Shorter formulas are possible. Since this formula would need to be
filled down into other cells (the ROWS($1:1) term gives this away),
why not use different formulas in the first and subsequent cells?

If the data to be condensed were in a range named D, and if the
topmost cell of the result range were G3, try

G3:
=T(D)

G4 [array formula]:
=IF(IF(G3<>"",MAX(MATCH(D,D,0))>MATCH(G3,D,0)),
INDEX(D,SMALL(IF(MATCH(D,D,0)=ROW(D)-MIN(ROW(D))+1,
ROW(D)-MIN(ROW(D))+1),ROWS(G$3:G4))),"")

Fill G4 down as far as needed. Even with all instances of D replaced
with $A$2:$A$10, this is still a smaller formula, it uses no volatile
function calls, and it avoids unnecessary ISERROR calls.

 

[csong005]

T. Valko

Thanks for the help!!!! It works perfectly.

csong005

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

T. Valko

Thanks for the help!!!! It works perfectly.

csong005