Unique distinct list from a column sorted A to Z using array formula in excel
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
- Select cell D2.
- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
- Press and hold Ctrl + Shift.
- Press Enter once.
- Release all keys.
How to copy this array formula
- Select cell D2.
- Copy (Ctrl + C) cell D2.
- Select D3:D8
- Paste (CTRL + V)
How to create a named range
List (B2:B12)
- Select cell B2:B12.
- Type List in name box.
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
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



















Hi Oscar!
Can you help me?
I have a name products column and their prices (other column).
I want to create unique distinct list with products name sorted by SUM of prices. Is it real using array formula?
Bill,
read this post:
Filter unique distinct list sorted based on sum of adjacent values
Thanks, Oscar!
I am using the following formula. How can i get it to work if there are blank cells?
How can i get it to work if there are formulas in the column?
=IFERROR(INDEX(List1,MATCH(MIN(IF(COUNTIF($F$9:F9,List1)=0,1,MAX((COUNTIF(List1,"<"&List1)+1)*2))*(COUNTIF(List1,"<"&List1)+1)),COUNTIF(List1,"<"&List1)+1,0)),"")
Jimmie,
try this formula:
=INDEX(List, MATCH(MIN(IF((List="")+COUNTIF(B1:$B$1, List), "", IF(ISNUMBER(List), COUNTIF(List, "< "&List), COUNTIF(List, "<"&List)+SUM(IF(ISNUMBER($A$2:$A$15), 1, 0))+1))), IF((List="")+COUNTIF(B1:$B$1, List), "", IF(ISNUMBER(List), COUNTIF(List, "<"&List), COUNTIF(List, "<"&List)+SUM(IF(ISNUMBER(List), 1, 0))+1)), 0))
Download excel file
Unique-and-Sort-numbers-and-text-cells-using-excel-array-formula-works-with-formulas.xls