Table of contents
- Create a unique distinct alphabetically sorted list
- Create a unique distinct alphabetically sorted list with criteria
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.
How to create an array formula
- Select cell B2
- Click in formula bar
- Copy and paste array formula to formula bar
- Press and hold Ctrl + Shift
- Press Enter
How to copy array formula
- Select cell B2
- Copy cell (not formula in formula bar) (Keyboard shortcut: Ctrl + c)
- Select cell range B3:B11
- Paste (Keyboard shortcut: Ctrl + v)
Remove errors (Excel 2007), array formula:
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
(Excel 97-2003 Workbook *.xls)
Create a unique distinct alphabetically sorted list with criteria
Array formula in cell D2:
Array formula in cell E2:
Download excel *.xlsx file
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
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range
returns the k-th smallest number in this data set.
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
Counts the number of cells within a range that meet the given condition
Counts the number of cells in a range that contain numbers