Question:

How do I create a unique distinct list from a column sorted A to Z using array formula?

Answer:


Array formula in D2:

=INDEX(List, MATCH(MIN(IF(COUNTIF($D$1:D1, List)=0,COUNTIF(List, "<"&List)+1,9.9999E+307)), COUNTIF(List, "<"&List)+1, 0))

How to create an array formula

  1. Select cell D2.
  2. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
  3. Press and hold Ctrl + Shift.
  4. Press Enter once.
  5. Release all keys.

How to copy this array formula

  1. Select cell D2.
  2. Copy (Ctrl + C) cell D2.
  3. Select D3:D8
  4. Paste (CTRL + V)

How to create a named range

List (B2:B12)

  1. Select cell B2:B12.
  2. Type List in name box.

What is named ranges?

How to implement array formula to your workbook

Change the named range. If your list starts at, for example, F3. Change $D$1:D1 in the above formula to $F$2:F2.

How this array formula works

Step 1 - Filter unique distinct values

=INDEX(List, MATCH(MIN(IF(COUNTIF($D$1:D1, List)=0,COUNTIF(List, "<"&List)+1,9.9999E+307)), COUNTIF(List, "<"&List)+1, 0))

COUNTIF($D$1:D1,  List)=0

becomes

COUNTIF("Unique distinct list sorted A to Z:", {"VV";"NN";"JJ";"TT";"DD";"NN";"II";"VV";"DD";"II";"JJ"})=0

becomes

({0;0;0;0;0;0;0;0;0;0;0})=0

and returns {TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}.

Step 2 - Remove duplicate values from array

=INDEX(List, MATCH(MIN(IF(COUNTIF($D$1:D1, List)=0,COUNTIF(List, "<"&List)+1,9,9999E+307)), COUNTIF(List, "<"&List)+1, 0))

MIN(IF(COUNTIF($D$1:D1, List)=0,COUNTIF(List, "<"&List)+1,9,9999E+307))

becomes

MIN(IF({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE},{10;7;5;9;1;7;3;10;1;3;5},9,9999E+307))

becomes

MIN({10;7;5;9;1;7;3;10;1;3;5})

and returns 1.

Step 3 - Match smallest value

=INDEX(List, MATCH(MIN(IF(COUNTIF($D$1:D1, List)=0,COUNTIF(List, "<"&List)+1,9,9999E+307)), COUNTIF(List, "<"&List)+1, 0))

MATCH(MIN(IF(COUNTIF($D$1:D1, List)=0,COUNTIF(List, "<"&List)+1,9,9999E+307)), COUNTIF(List, "<"&List)+1, 0)

becomes

MATCH(1, {10;7;5;9;1;7;3;10;1;3;5}, 0)

and returns 5.

Step 3 - Return a value or reference of the cell at the intersection of a particular row and column

=INDEX(List, MATCH(MIN(IF(COUNTIF($D$1:D1, List)=0,COUNTIF(List, "<"&List)+1,9,9999E+307)), COUNTIF(List, "<"&List)+1, 0))

becomes

=INDEX(List, 5)

becomes

=INDEX({"VV";"NN";"JJ";"TT";"DD";"NN";"II";"VV";"DD";"II";"JJ"}, 5)

and returns DD in cell D2.

Download excel sample file for this tutorial.

Unique-distinct-list-from-a-column-sorted-A-to-Z-using-array-formula11.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

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

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

MAX(number1,[number2],)
Returns the largest value in a set of values. Ignores logical values and text.

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

Recommended blog posts

There are more blog posts about how to sort a list using array formulas. You must read these articles: