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.

Try:

Data Filter AdvancedFilter Unique records only
--
Gary''s Student
gsnu200711

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" wrote:

Try:

Data Filter AdvancedFilter Unique records only
--
Gary''s Student
gsnu200711

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

--
Gary''s Student
gsnu200711

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(IN DIRECT("1:"&ROWS($A$2:$A$10)))),ROWS($1:1))),"",IN DEX($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,R OW(INDIRECT("1:"&ROWS($A$2:$A$10)))),ROWS($1:1))))

Adjust to suit


"csong005" wrote:

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.

On Mon, 19 Mar 2007 11:21:20 -0700, csong005
wrote:

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

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(I F(ROW(A$2:A$20)-MIN(ROW(A$2:A$20))+1=MATCH(A$2:A$20,A$2:A$20,0),RO W(A$2:A$20)-MIN(ROW(A$2:A$20))+1),ROWS($1:1))),"")

Copy down until you get blanks

Biff

"csong005" wrote in message
...
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.

Teethless mama wrote...
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.

T. Valko

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

csong005

"T. Valko" wrote:

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(I F(ROW(A$2:A$20)-MIN(ROW(A$2:A$20))+1=MATCH(A$2:A$20,A$2:A$20,0),RO W(A$2:A$20)-MIN(ROW(A$2:A$20))+1),ROWS($1:1))),"")

Copy down until you get blanks

Biff

"csong005" wrote in message
...
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're welcome. Thanks for the feedback!

Biff

"csong005" wrote in message
...
T. Valko

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

csong005

"T. Valko" wrote:

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(I F(ROW(A$2:A$20)-MIN(ROW(A$2:A$20))+1=MATCH(A$2:A$20,A$2:A$20,0),RO W(A$2:A$20)-MIN(ROW(A$2:A$20))+1),ROWS($1:1))),"")

Copy down until you get blanks

Biff

"csong005" wrote in message
...
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.