Table of contents

unqiue-list-sorted-alphabetically2

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.

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)

Create a unique distinct alphabetically sorted list with criteria

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

Download excel *.xlsx file

Create a unique distinct alphabetically sorted list with criteria.xlsx

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