I came up with a new idea when I reread a previous article Sorting text cells using array formula in excel. Maybe it is possible to sort the new unique distinct list alphabetically.

To the right is a picture of the list I will be working with. I created named ranges, List (A2:A10) and a named range for the start cell of the list, List _start (A2).

Here is the formula in B2:B10. See picture below.

Edited 2009-06-28 Here is a shorter formula:

Array formula in cell B2, see picture below.

=INDEX(List, MATCH(MIN(IF(COUNTIF($B$1:B1, List)=0, 1, MAX((COUNTIF(List, "<"&List)+1)*2))*(COUNTIF(List, "<"&List)+1)), COUNTIF(List, "<"&List)+1, 0)) + CTRL + SHIFT + ENTER and copy it down as far as necessary, to get unique list sorted alphabetically from A to Z.

To remove errors in Excel 2007:

=IFERROR(INDEX(List, MATCH(MIN(IF(COUNTIF($B$1:B1, List)=0, 1, MAX((COUNTIF(List, "<"&List)+1)*2))*(COUNTIF(List, "<"&List)+1)), COUNTIF(List, "<"&List)+1, 0)), "") + CTRL + SHIFT + ENTER and copy it down as far as necessary, to get unique list sorted alphabetically from A to Z.

How to customize formula to your workbook
Change named ranges in formula.

Download excel sample file for this tutorial.
unique-list-sorted-alphabetically.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

Related posts:

1. Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula
2. Extract a unique distinct list sorted alphabetically removing blanks from a range in excel
3. Filter a column and create a new unique list sorted from A to Z using array formula in excel
4. Extract a list of alphabetically sorted duplicates from a column in excel
5. Create unique distinct list sorted based on text length using array formula in excel
6. Unique distinct list sorted based on occurrance in a column in excel
7. Unique distinct list from a column sorted A to Z using array formula in excel
8. Extract a unique distinct list sorted from A-Z from range in excel
9. Create unique distinct list from column where an adjacent column meets criteria
10. Filter unique distinct list sorted based on sum of adjacent values using array formula in excel