fbpx
Search
Close this search box.

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

Share

Facebook
Twitter
LinkedIn

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:

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

13 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

  13. Mohamed says:

    Hi Hui,
    Greeting
    hope you are doing well.
    I'm interested to send you a private vba excel file which i need to show detail of pivot in new workbook instead of showing in same workbook as new sheet.

    Please contact me on muhammed.ye@gmail.com

    Best Regards

Leave a Reply