Avoid duplicates in data validation and create a linked list in Excel

May 30th, 2011 by Tips Master | Posted under Excel, Excel Functions.

Data validation - Linked list and avoid duplicate valuesThis tutorial explains how to create a linked list in Excel. Linked list or a depended list means you have drop down list where one drop down depends on the value of the other. We need to restrict the values based on the value selected from the first list. Here I have attempted to create a linked list  using Excel tables.  There are other ways for creating Linked list , for e.g. using Indirect function. But the disadvantage is that when the database runs in to a large number of lines you will find it difficult to add each one two different names and also creating new names for each combination. Also explains how to avoid duplicate values in a data validation. Don’t miss this one…

Download the file

Use the link to download the file – Linked list in Excel and avoid duplicate values

 

Understand the file

I have provided the option for download at the top because without downloading the file you will find it really difficult to understand the concepts. This looks little complicated at the initial view, but when you start working on it you will find it is fun.

There is a tab named form where you will find the drop down options

Data Validation screen

Select the region for e.g. ASIA you can see that the drop down for country is restricted to only the Asian countries in the database.

Data Validation in Excel

 

Understand the Methodology

 

Basically the data validation attempted is a 3 level where the 2nd one depends on the first list which is region an the customer depends upon the country and region selected

1. Getting Unique list for region

Thanks to chandoo for the idea of getting unique list using Pivot tables.  What we do is that create a pivot table from the region name as shown below

Pivot Table in Excel

Create a named range with the formula =OFFSET(‘Base table’!$A$3,1,0,COUNTA(‘Base table’!$A:$A)-1) with the  name “Region”. We have created a dynamic range so that the pivot source list will automatically expand as and when a new entry appears. See the link to find more on creating dynamic ranges using offset function

So in the form tab the data validation will be linked to the name region

Data Validation for regions

This method will avoid getting duplicates in to data validation list. simple but very effective method.

 

2. Getting Unique list for Country.

We need to create some unique items for this purpose.

Here we need to link the country with the region.  Our objective is to get the unique items in a separate column which we can use a the link to the data validation.

Unique list for country

The country column is the end result of that. The formula used is =INDEX([Countries],MATCH(Table1[[#This Row],[Unique2(Country)]],[Unique1(Country)],0)), which used the column unique 2(country) as the base to look up the unique country values. There are two objectives 1. Avoid duplicates and 2. Get the entire list based on the region selected to the top in a sequence.

The formula here is =”1″&ROW()-3&Form!$C$3 which is 2 sets of numbers + the region selected. The first number will help us to avoid the duplicates and the 2nd number  will help to get the list in the top.

Unique1 (country) is used to generate a unique reference for each line. We use the countif function to create this unique reference. =COUNTIF($M$4:M5,[Unique4])&COUNTIF($G$4:G4,[Column1])&[Region] . The first countif is looking for the duplicates . If it finds that the region is getting duplicated it will show number as the nth number, else 1. if there are 2 duplicates the number will be 2 and 3 duplicates number will be 3 so on . In the column unique2(country) we have only number 1 to start with which helps us to avoid all those duplicates.

Once we have the country list we will create another name for this using offset function.

Define name for country

The data validation is linked with this name to create the list.

2. Getting Unique list for Customer.

Now as the combination grows we need to have more unique values to create the list

Unique list for customers

Unique4(customer) is created for looking up the unique customer values. This is also worked on the same principle as we see for the country except we need to take care of the multiple combinations.  When working on this this i have also considered the possibility that there can be also some cases where customer appearing for Asia will also appear for Europe. So taking all those things in to account we need to create some multiple unique items.

Here is the snap shot of our final list

Data validation - Linked list and avoid duplicate values new

 

 

 

I have tried my best to explain the file. If you still have doubts  on the file don’t hesitate to give me some comments or send me mail at tipsindeed@gmail.com

 

 

 

 


 

 

 

 



Comments

5 Responses to “Avoid duplicates in data validation and create a linked list in Excel”
  1. Question says:

    The linked file is a zip with a bunch of .xml files. How can I turn this into a workbook??

    • Tips Master says:

      This is because you don’t have the 2007 version.try to download the file and right clck and give the extension as .xls .Then try opening the file and let me know if it works

    • Ulf says:

      The downloadable file is a xlsx file (the new Exel file format). If you’re running a Office version prior to Office 2007, you need to install a plugin to open the file.

      http://www.lazerwire.com

  2. Sherif says:

    Hi,
    Thanks for the brilliant idea of this sheet. Looked everywhere to reach same result with not much success!
    One concern though, if any of the basic table data levels bypasses 10 different entries (11 customers for the same country for example), a #N/A value, along with the correct values, is returned in the drop down menu.
    Might be the “numbering” logic that creates this constraint, or maybe I missed something when applying on my db ..?
    Sherif.

Do you have any comments on Avoid duplicates in data validation and create a linked list in Excel ?