Get a dynamic list of unique values in MS Excel

---

In this tutorial I want to demonstrate how to get a dynamic list of unique values without having to manually remove duplicated (i.e. Data> Remove Duplicates). Consider the table below containing a list of employee names and reference numbers. You will notice there are many duplicates names in this list. 

​The traditional manual way for removing duplicates has been illustrated below

However, in the method above we will need to continuously repeat this procedure if new data is added to the original data set. But there is a solution!!! The formula below will give us a dynamic list of unique values even as new values get added to the original list.

{=IFERROR(INDEX($A$2:$A$667, MATCH(0,COUNTIF($D$1:D1, $A$2:$A$667), 0)),"")}
 
Note that this is an array formula so CTRL+SHIFT+ENTER needs to be pushed in order to correctly activate the formula. And here is the outcome after I applied the formula to the entire column range:

---