Data Validation using an Unsorted column with Duplicate Entries as a Source List

This is a guest post by Hui, our in-house excel ninja.

Here is a typical scenario: We want to allow only one of the pre-defined customer names in our spreadsheet. We have listed down all the customers in column B and want excel to check against this list and validate the data. But there are 3 problems. (1) Our list is not sorted alphabetically (2) It contains duplicates and (3) The list comes from external source, so we can not remove duplicates and sort the list every time.

Now how can we set up a simple data validation list that would not repeat customer names and shows them in sorted order like this:

Here is the solution:

We need to remove duplicate entries and sort our data before it is fed to data validation list source. For this, there is a perfect tool right inside excel.

The rapid sorting cat.

Well, I am kidding. We use pivot tables. Pivot tables can sort the data as well as remove any duplicates. We can construct list of unique and sorted customers as column headers.

Just follow these simple steps:

1. Select the source customer list and make a new pivot table. Insert the pivot table in the same sheet for convenience.
2. Now, just add customer name to the column header area as well as “values” area.
3. At this point, the column header in pivot contain all the customers (without duplicates) in sorted order.
4. Now, make a dynamic named range that would refer to column headers in the pivot table. I am leaving this to your imagination, but here is a clue.
5. Finally, use this dynamic range as input data source for data validation.
6. That is all. Now, as and when your source data changes, just refresh the pivot and your validation will be updated too.

Note: You can make a dynamic range out of your input data as well. Or use excel tables if you have 2007 and above.

Download this example and learn by playing:

Here is an example sheet with a different data set (I use animals instead of customers).

Added by PHD:

Hui is an active member on PHD forums and helps people in solving excel problems. He likes to work on excel and has been kind enough to write this post in his free time to share such good idea with us.

Please say thanks to him if you enjoyed this post.

Learn more about data validations & duplicates:

• Remove duplicates from data using pivot tables
• Create a simple data validation list (or incell drop-down)
• Learn how to use Excel OFFSET formula (and make dynamic named ranges)
• Learn how to make a pivot table in Excel