unqiue-list-sorted-alphabetically2I 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.

unqiue-list-sorted-alphabetically


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