Question:

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

Answer:

Array formula in D2:

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:

• Extract a unique distinct list sorted from A-Z from range in excel
• Sort a range by occurence using array formula in excel
• Create a unique list and sort by occurrances from large to small

Related posts:

Filter a column and create a new unique list sorted from A to Z using array formula in excel

Create unique distinct list sorted based on text length using array formula in excel

Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula

Filter unique distinct list sorted based on sum of adjacent values using array formula in excel

Create a unique distinct alphabetically sorted list, extracted from a column in excel