Data Validation: How to create a drop down list that excludes duplicate data

Hi,

I'd like to create a drop down list in data validation from a column of data that contains numerous duplicates.

For example, let's say column A contained hundreds of transactions with either North, South, East and West, how could I create a drop down list in another cell that only had four selection options?

Thanks in advance,

Snook

Select Col A:
Go to Data > Data Validation..


In Setting tab.. Allow dropdown select "List"

and in source section write,

North, South, East, West,

To get unique values list you can formula below. Assuming your data locate in column A range A2:A100 enter formula in cell B2

=INDEX($A$2:$A$100,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$100),0,0),0))