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

Posted on February 2nd, 2010 in Learn Excel - 12 comments

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

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

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:

sorted-vs-jumbled-data-validation-lists

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:

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

12 Responses to “Data Validation using an Unsorted column with Duplicate Entries as a Source List”

  1. Vipul says:

    Pivot Table will involve manual intervention; hence I prefer to use the ‘countif remove duplicate trick’ along with ‘text sorting formula trick; then using the offset with len to name the final range for validation.

  2. Rich says:

    if using the pivot table, set the sort to Ascending, so the list in the validation cell comes back alphabetically.

  3. Kieranz says:

    Hui: Brillant neat idea.
    Vipul: I am intrigued by what you are saying. Please is it possible to show us how it can be done, because as u said Hui’s method requires user intervention.
    Thks to PHD and all
    K

  4. sam says:

    Table names dont work directly inside Data validation.
    You will have to define a name and point it to the table name and then use the name inside validation
    Eg MyClient : Refers to :=Table1[Client]
    And then in the list validation say = MyClient

  5. Vipul says:

    Kieranz,
    Pls download the sample here http://cid-e98339d969073094.skydrive.live.com/self.aspx/.Public/data-validation-unsorted-list-example.xls
    Off course there are many other ways of doing the same and integrating the formulae in multiple columns into one.

  6. Vipul says:

    Pls refer to column FGHI in that file. Cell G4 is where my validation is.

  7. Kieranz says:

    Vipul:
    Many thks, will study it latter.
    Rgds
    K

  8. […] to chandoo for the idea of getting unique list using Pivot tables. ¬†What we do is that create a pivot table […]

  9. Playercharlie says:

    @Vipul:

    Thanks, that was awesome! :)

  10. Vipul says:

    @Playercharlie Happy to hear that :)

  11. Enrique says:

    Great contribution, Hui. Solved a problem of many years!

  12. FARIS says:

    Thanks to you, A LOT

Leave a Reply