Data validation - Drop down list with no duplicates in excel -

could please assist me removing duplicates in drop down list. list not static. example:

before

james peter james nick peter 

after

james peter nick 

i not sure formula use data validation formula box.

i had attempted below, no success:

=offset($c$13,0,0,countif(c:c,"?*")-1) 

step 1 - original names in column a, put array formula¹ somewhere off right in second row. i'll use z2.

=iferror(index(a$2:index(a:a, match("zzz",a:a )), match(0, countif(z$1:z1, a$2:index(a:a, match("zzz",a:a ))&""), 0)), "") 

fill down until run out of names , few more rows allow future expansion.

step 2 - go formulas ► defined names ► name manager , create new name.

name: listnames scope: workbook refers to: =sheet2!$z$2:index(sheet2!$z:$z, match("zzz", if(len(sheet2!x:x), sheet2!$z1:$z98) )) 

step 3 - go cell want data validation , use data ► data tools ► data validation.

allow: list source: listnames 

        

---

¹ _{array formulas need finalized ctrl+shift+enter↵. if entered correctly, excel wrap formula in braces (e.g. { , }). not type braces in yourself. once entered first cell correctly, can filled or copied down or right other formula. try , reduce full-column references ranges more closely representing extents of actual data. array formulas chew calculation cycles logarithmically practise narrow referenced ranges minimum. see guidelines , examples of array formulas more information.}