This blog article is one out of five articles on the same subject.

Sorting text cells using array formula in excel
Sorting numbers and text cells also removing blanks using array formula in excel
Sorting numbers and text cells descending also removing blanks using array formula in excel
Sort text cells alphabetically from two columns using excel array formula
Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula
Sort a range from A to Z using array formula in excel

Question: How do I create a drop down list with unique distinct alphabetically sorted values? See picture of original list to the right.

Answer:

Excel array formula in B2. See picture below

Edit: 2009-09-02, new somewhat smaller formula.

=INDEX(List, MATCH(0, IF(MAX(NOT(COUNTIF($B$1:B1, List))*(COUNTIF(List, ">"&List)+1))=(COUNTIF(List, ">"&List)+1), 0, 1), 0)) + CTRL + SHIFT + ENTER copied down as far as necessary.

=IF(COUNT(IF(MATCH(List, List, 0)=(ROW(List)-ROW(List_start)+1), COUNTIF(List, "<"&List)+1, ""))>(ROW(List)-ROW(List_start)), INDEX(List, MATCH(SMALL(IF(MATCH(List, List, 0)=(ROW(List)-ROW(List_start)+1), COUNTIF(List, "<"&List)+1, ""), (ROW(List)-ROW(List_start)+1)), IF(MATCH(List, List, 0)=(ROW(List)-ROW(List_start)+1), COUNTIF(List, "<"&List)+1, ""), 0)), "")

Named ranges

List (A2:A10)
List_start (A2)
What is named ranges?

How to create a drop down list with values updated dynamically in excel 2007

1. Click Data tab
2. Click Data validation button
3. Click "Data validation..."
4. Select List in the "Allow:" window. See picture below.
5. Type =OFFSET($B$2, 0, 0, COUNT(IF($B$2:$B$1000="", "", 1)), 1) in the "Source:" window
6. Click OK!

Download example workbook

Download excel sample file for this tutorial.
Create-a-drop-down-list-containing-only-unique.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

SMALL(array,k) returns the k-th smallest row number in this data set.

ROW(reference) returns the rownumber of a reference

MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

COUNT(value1;[value2])
Counts the number of cells in a range that contain numbers

OFFSET(reference,rows,cols, [height],[width])
Returns a reference to a range that is a given number of rows and columns from a given reference

This blog article is one out of five articles on the same subject.

Sorting text cells using array formula in excel
Sorting numbers and text cells also removing blanks using array formula in excel
Sorting numbers and text cells descending also removing blanks using array formula in excel
Sort text cells alphabetically from two columns using excel array formula
Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula
Sort a range from A to Z using array formula in excel

Related posts:

1. Create a unique distinct alphabetically sorted list, extracted from a column in excel
2. Sort text cells alphabetically from two columns using excel array formula
3. Create unique distinct list sorted based on text length using array formula in excel
4. Filter a column and create a new unique list sorted from A to Z using array formula in excel
5. Extract a unique distinct list sorted alphabetically removing blanks from a range in excel
6. Sorting numbers and text cells descending also removing blanks using array formula in excel
7. Filter unique distinct list sorted based on sum of adjacent values using array formula in excel
8. Unique distinct list from a column sorted A to Z using array formula in excel
9. Sorting text cells using array formula in excel
10. Sorting numbers and text cells also removing blanks using array formula in excel