Ignore Duplicate Entries in Data Validation list

Question

Suppose we have three columns as below:

ID  Sys     Code
1   Roma    A4
2   Roma    A5
2   Roma    A5
2   Roma    A7
2   Lima    A7
3   Lima    B1
4   Lima    C1
4   Lima    D3
4   Lima    D5
5   Alpha   E9
6   Alpha   E2

As observed from the above, all columns may contain duplicates, the goal is to have three cells in a different sheet where data validation lists would be used. A typical scenario would be:

User selects the ID (e.g. 2), then on the next cell he selects the Sys (e.g. Roma), this list would be filtered according to the ID selected on the previous cell (only "Roma" and "Lima" shown), lastly, he selects the Code, this would be filtered according to the Sys selected (only "A5" and "A7" shown).

The approach which I am currently using is to have a separate list of only unique IDs, this is being used on the first validation list, then on the second validation list I am using the below formula to select the respective Sys:

=OFFSET(IDS_Start,MATCH(A1,IDS,0)-2,1,COUNTIF(IDS,A1),1)

Where IDS_Start is a reference to the first ID in my list, IDS a reference to the entire column ID and A1 is the cell in which the user selects the ID.

The problem with the above is that it will result in duplicates to be displayed on the second validation list, is there a way around this?

Solution

This is called cascaded lists and this post will help you to solve it:

http://www.bluepecantraining.com/portfolio/cascading-drop-down-lists-in-excel/