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))

How to create an array formula

1. Select cell B2
2. Click in formula bar
3. Copy and paste array formula to formula bar
4. Press and hold Ctrl + Shift
5. Press Enter

How to copy array formula

1. Select cell B2
2. Copy cell (not formula in formula bar) (Keyboard shortcut: Ctrl + c)
3. Select cell range B3:B11
4. Paste (Keyboard shortcut: Ctrl + v)

Remove errors (Excel 2007), array formula:

=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)), "")

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.

unique-list-sorted-alphabetically.xls
(Excel 97-2003 Workbook *.xls)

### Create a unique distinct alphabetically sorted list with criteria

Array formula in cell D2:

=INDEX(\$A\$2:\$A\$14, MATCH(MIN(IF((COUNTIF(\$D\$1:D1, \$A\$2:\$A\$14)=0)*(\$B\$2:\$B\$14=0), 1, MAX((COUNTIF(\$A\$2:\$A\$14, "<"&\$A\$2:\$A\$14)+1)*2))*(COUNTIF(\$A\$2:\$A\$14, "<"&\$A\$2:\$A\$14)+1)), IF(\$B\$2:\$B\$14=0,COUNTIF(\$A\$2:\$A\$14, "<"&\$A\$2:\$A\$14)+1,""), 0))

Array formula in cell E2:

=INDEX(\$A\$2:\$A\$14, MATCH(MIN(IF((COUNTIF(\$E\$1:E1, \$A\$2:\$A\$14)=0)*(\$B\$2:\$B\$14=1), 1, MAX((COUNTIF(\$A\$2:\$A\$14, "<"&\$A\$2:\$A\$14)+1)*2))*(COUNTIF(\$A\$2:\$A\$14, "<"&\$A\$2:\$A\$14)+1)), IF(\$B\$2:\$B\$14=1,COUNTIF(\$A\$2:\$A\$14, "<"&\$A\$2:\$A\$14)+1,""), 0))

Create a unique distinct alphabetically sorted list with criteria.xlsx

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 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