List Validation & Columnar data

Posted by Mike on December 18, 2001 1:42 PM

Hello,

I have an application where there is a column of data that may have thousands of records, of which most will be one of a few dozen values.

I would like to have a drop-down list (under validation) of these values, but only one per, not the entire column!

Is there any way to select the unique data entries in a column and build a pick-list from this?

Ex.
john
mike
mary
john
mary
john
john
mike
andy

would result in a cell validation list containing:
andy
john
mary
mike

(sorting would be great too...)

Any thoughts?

Posted by Aladin Akyurek on December 18, 2001 1:54 PM

Mike --

You can use Advanced Filter to build a uniqified list of names.

Lets say that you have this long list of names in A from A2 on, with a label such as Names in A1. Put a border around A1.

Activate A2.
Activate Data|Filter|Advanced Filter.
List Range must be the range that houses the names.
Check Copy to another location.
Enter in the Copy to box a cell ref (from a free column in the same worksheet).
Check Unique records only.
Click OK.

You should have there a unique list. You can cut and paste this list into another worksheet. Select all the cells of this list except the label and give it a name via the Name Box on the Formula Bar, e.g., NameList. And, sort this list via Data|Sort.

You can now use NameList as Source in data validation to create a dropdown list.

Aladin

==========

Posted by Bill Moody on December 18, 2001 1:57 PM

Run a quick Pivot Table on your main list and copy the results back into your validation list.

Posted by Mike on December 19, 2001 5:04 PM

Thanks!

That is EXACTLY what I needed!

Thanks to everyone who answered - it's nice that there are so many people who know so many ways to do pretty much anything in Excel!

Mike -- You can use Advanced Filter to build a uniqified list of names. Lets say that you have this long list of names in A from A2 on, with a label such as Names in A1. Put a border around A1. Activate A2.