Extract Unique List

  • Thread starter Thread starter ZootRot
  • Start date Start date
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.
 
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!
 
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
 
ZootRot said:
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.
Click to expand...

Bad news first. Data validation lists must be ranges or hardcoded, so the
list of distinct animals would need to be extracted to a range. Now the good
news. This is simple to do with formulas. If your original range above were
in C3:C10 and the list of distinct animals would have first (topmost) entry
in F5, use these formulas.

F5:
=C3

F6: [array formula]
=INDEX($C$3:$C$10,MATCH(0,COUNTIF(F$5:F5,$C$3:$C$10),0))

Select F6 and fill down as far as needed. The formula will return #N/A when
you've exhausted the original list. Also, no need to add a row on top for a
field name.

However, if you want to add a field name then name the augmented range
(field name plus animal entries), you could use a more elegant formula
approach. If the range were named Animals and the field name were Animal,
and the file had been saved as C:\pets\list.xls, you could use the array
formula

=SQL.REQUEST("DSN=Excel Files;DBQ=C:\pets\list.xls",,,
"Select Distinct Animal From Animals",0)

Unlike pivot tables and advanced filters, this will update/recalc
automatically when there are changes to your original list of animals.
 
Harlan Grove said:
if you want to add a field name then name the augmented range
(field name plus animal entries), you could use a more elegant formula
approach. If the range were named Animals and the field name were Animal,
and the file had been saved as C:\pets\list.xls, you could use the array
formula

=SQL.REQUEST("DSN=Excel Files;DBQ=C:\pets\list.xls",,,
"Select Distinct Animal From Animals",0)

Unlike pivot tables and advanced filters, this will update/recalc
automatically when there are changes to your original list of animals.
Click to expand...

Would a 'dynamic range' work for Validation? Something like

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1)

The reason I suggest this is because IIRC Jake Marx once mentioned
that querying an open workbook using SQL.REQUEST (ODBC Direct) can
cause problems similar to the ADO memory leak bug (Q319998).

--
 
Back
Top