Data validation - Drop down list with no duplicate

Question

Could you please assist me with removing duplicates in a drop down list. The list is not static. Example:

Before

James
Peter
James
Nick
Peter

After

James
Peter
Nick

I am not sure what formula to use data validation formula box.

I had attempted the below, but no success:

=OFFSET($C$13,0,0,COUNTIF(C:C,"?*")-1)

Answer 1

Step 1 - With the original names in column A, put this array formula¹ somewhere off to the right in the 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 you run out of names and then a few more rows to allow for future expansion.

Step 2 - Go to Formulas ► Defined Names ► Name Manager and create a 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 to the cell you want you data validation and use Data ► Data Tools ► Data Validation.

Allow: List
Source: listNames

        

---

¹ _{Array formulas need to be finalized with Ctrl+Shift+Enter↵. If entered correctly, Excel with wrap the formula in braces (e.g. { and }). You do not type the braces in yourself. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula. Try and reduce your full-column references to ranges more closely representing the extents of your actual data. Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum. See Guidelines and examples of array formulas for more information.}