Return unique data from a list

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

csong005 - 19 Mar 2007 19:21 GMT

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.

Reply to this Message

Gary''s Student - 19 Mar 2007 19:26 GMT

Try:

Data > Filter > AdvancedFilter > Unique records only

Signature

Gary''s Student
gsnu200711

Reply to this Message

csong005 - 19 Mar 2007 19:35 GMT

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.

> Try:
>
> Data > Filter > AdvancedFilter > Unique records only

Reply to this Message

Gary''s Student - 19 Mar 2007 19:59 GMT

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

Signature

Gary''s Student
gsnu200711

Reply to this Message

Teethless mama - 19 Mar 2007 20:55 GMT

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

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

Reply to this Message

Harlan Grove - 19 Mar 2007 21:38 GMT

Teethless mama <Teethlessm...@discussions.microsoft.com> wrote...

>It is an array formula you have to commit with ctrl+shift+enter, not
>just enter
[quoted text clipped - 5 lines]
>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.

Reply to this Message

Ron Rosenfeld - 19 Mar 2007 21:05 GMT

>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

Reply to this Message

T. Valko - 19 Mar 2007 21:15 GMT

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

Reply to this Message

csong005 - 19 Mar 2007 21:48 GMT

T. Valko

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

csong005

> Try this:
>
[quoted text clipped - 13 lines]
> > 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.

Reply to this Message

T. Valko - 19 Mar 2007 21:58 GMT

You're welcome. Thanks for the feedback!

Biff

> T. Valko
>
[quoted text clipped - 22 lines]
>> > isolate
>> > which of the 50 states are included in the list. Thanks.

Reply to this Message