Formulas

drewrogers 

My non-array version of that which was suggested by Subodh_Tiwari_sktneer is: 

=IFNA(LOOKUP(2,1/(
COUNTIF(A$2:A$25,">="&A$2:A$25)=AGGREGATE(14,6,
COUNTIF(A$2:A$25,">="&A$2:A$25)/(COUNTIF(C$1:C1,A$2:A$25)=0),ROW()-1)),
A$2:A$25),"")

drewrogers 

There are several ways you can extract the unique items from a list.

1. Array Formula
2. Remove Duplicate feature: I this is one time requirement, you may copy your list of names to a blank column, go to Data Tab --> click on Remove Duplicates and this will remove all the duplicates leaving behind a list of all the unique names.
3. Pivot Table: You can insert a Pivot Table and drag the Name Field from the Field List into the Rows area and you will see a unique list of names in the Pivot Table.
4. Power Query: You can create a query using Power Query and remove the duplicates in the query editor and load it back to the worksheet, a list of unique names.
5. VBA: Use VBA to generate a list of unique names.
6. Dynamic Array Formula Unique: If you have access to the dynamic array formula, you can use UNIQUE function to get a unique list of names. Assuming your data is in A2:A25, place the formula =UNIQUE(A2:A25) in a blank cell and hit Enter.

In case of Array Formula, assuming your list is in A2:A25, place this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.

In C2

=IFERROR(INDEX($A$2:$A$25,MATCH(0,INDEX(IF($A$2:$A$25<>"",COUNTIF($C$1:C1,$A$2:$A$25)),0,0),0)),"")

Confirm with Ctrl+Shift+Enter and copy it down until you get blank cells.

Adjust the range referred in the formula as per your requirement.

You may also create a Dynamic Named Range and refer it in the formula instead of referring ranges.

If you plan to place the above formula in another column, don't forget to change the bold column letters in the above formula as per your formula column.

Please find the attached with Array Formula, Dynamic Array Formula and Pivot Table solutions to get a unique list of names.