Extract Unique List

Z

ZootRot

Excel 2002

From a list of multiple entries, how do I extract to another list of unique
entries, prefereably without a macro. Similar to what appears in a data
filter.

For example:
list contains:
dog
cat
pig
monkey
dog
dog
pig
monkey

I want to extract only: dog, cat, pig, monkey and use it in a data
validation list.

Thanks for your help.
 
G

Gord Dibben

D

Domenic

Excel 2002

From a list of multiple entries, how do I extract to another list of unique
entries, prefereably without a macro. Similar to what appears in a data
filter.

For example:
list contains:
dog
cat
pig
monkey
dog
dog
pig
monkey

I want to extract only: dog, cat, pig, monkey and use it in a data
validation list.

Thanks for your help.
Click to expand...
You can use the advanced filter to copy the unique entries to another
location.

1) Make sure that your list has a column label (a heading).
2) Click a cell in the list
3) Date > Filter > Advanced Filter
4) Select "Copy to another location"
5) Make sure the "List Range" is correct
6) Skip "Criteria"
7) Select location to copy to
8) Select "Unique records only"
9) Click Ok

That should give you a new list of unique items which you can use in your
Data Validation list.

Hope this helps!
 
S

Stefano Gatto

Another faster way is to select the data range making sure the column has a
header (like "animal" in your example), start Pivot table and drag the
animal column towards both the rows and central locations.

You will get a list of unique animals and the count too! From there you can
copy and paste-special-values whereever you want.

Stefano
 
Top