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:
- Select the source customer list and make a new pivot table. Insert the pivot table in the same sheet for convenience.
- Now, just add customer name to the column header area as well as “values” area.
- At this point, the column header in pivot contain all the customers (without duplicates) in sorted order.
- 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.
- Finally, use this dynamic range as input data source for data validation.
- 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:
13 Responses to “Data Validation using an Unsorted column with Duplicate Entries as a Source List”
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.
if using the pivot table, set the sort to Ascending, so the list in the validation cell comes back alphabetically.
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
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
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.
Pls refer to column FGHI in that file. Cell G4 is where my validation is.
Vipul:
Many thks, will study it latter.
Rgds
K
[...] to chandoo for the idea of getting unique list using Pivot tables. Â What we do is that create a pivot table [...]
@Vipul:
Thanks, that was awesome! 🙂
@Playercharlie Happy to hear that 🙂
Great contribution, Hui. Solved a problem of many years!
Thanks to you, A LOT
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