## Extract a unique distinct list sorted from A-Z from range

The image above shows an array formula in cell B8 that extracts unique distinct values sorted alphabetically from cell range B2:E5.

**Array formula in B8:**

Copy cell B8 and paste it to cells below as far as necessary.

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

### Explaining formula in cell B11

This formula consists of two parts, one extracts the row number and the other the column number needed to return the correct value.

INDEX(*reference*, *row*, *col*)

Step 1 to 6 shows how the row number is calculated, step 7 to 11 demonstrates how to calculate the column number.

#### Step 1 - Prevent duplicates

The COUNTIF function counts values based on a condition or criteria, in this case, we take into account previously displayed values in order to prevent duplicates in our output list.

COUNTIF($B$7:B7, $B$2:$E$5)=0

becomes

COUNTIF("Unique distinct", {"Banana", "Raspberry", "Banana", "Raspberry";"Grapefruit", "Banana", "Apple", "Grapefruit";"Blueberry", "Kiwifruit", "Raspberry", "Blackberry";"Raspberry", "Blueberry", "Blueberry", "Banana"})

becomes

{0,0,0,0;0,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, TRUE;TRUE, TRUE, TRUE, TRUE}.

#### Step 2 - Replace TRUE with the corresponding rank order

The following IF function returns the corresponding sort order if the list had been sorted from A to Z based on the logical expression. FALSE returns "" (nothing).

IF(COUNTIF($B$7:B7,$B$2:$E$5)=0,COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1,"")

becomes

IF({TRUE, TRUE, TRUE, TRUE;TRUE, TRUE, TRUE, TRUE;TRUE, TRUE, TRUE, TRUE;TRUE, TRUE, TRUE, TRUE}, {1,12,1,12;9,1,0,9;6,11,12,5;12,6,6,1}+1,"")

becomes

IF({TRUE, TRUE, TRUE, TRUE;TRUE, TRUE, TRUE, TRUE;TRUE, TRUE, TRUE, TRUE;TRUE, TRUE, TRUE, TRUE}, {1,12,1,12;9,1,0,9;6,11,12,5;12,6,6,1}+1,"")

becomes

IF({TRUE, TRUE, TRUE, TRUE;TRUE, TRUE, TRUE, TRUE;TRUE, TRUE, TRUE, TRUE;TRUE, TRUE, TRUE, TRUE}, {2,13,2,13;10,2,1,10;7,12,13,6;13,7,7,2},"")

and returns

{2,13,2,13;10,2,1,10;7,12,13,6;13,7,7,2}

#### Step 3 - Extract smallest value in array

The SMALL function extracts the k-th small number in array, in this case the second argument (k) is 1.

SMALL(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1, ""), 1)

becomes

SMALL({2,13,2,13;10,2,1,10;7,12,13,6;13,7,7,2}, 1)

and returns 1.

#### Step 4 - Replace TRUE with the corresponding row number

The IF function uses a logical expression to determine which value (argument) to return.

IF(SMALL(IF(COUNTIF($B$7:B7,$B$2:$E$5)=0,COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1,""),1)=COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1,ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)

becomes

IF(1=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1, ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)

becomes

IF(1={2,13,2,13;10,2,1,10;7,12,13,6;13,7,7,2}, ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)

becomes

IF({FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,TRUE,FALSE;FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE}, ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)

becomes

IF({FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,TRUE,FALSE;FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE}, {1;2;3;4})

and returns

{FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,2,FALSE;FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE}

#### Step 5 - Find smallest value

The SMALL function finds the smallest value in the array ignoring the boolean values

SMALL(IF(SMALL(IF(COUNTIF($B$7:B7,$B$2:$E$5)=0,COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1,""),1)=COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1,ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1),1)

becomes

SMALL({FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,2,FALSE;FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE},1)

and returns 2. This is the row number we need to extract the correct value from cell range B2:E5.

#### Step 6 - Return array

This step uses the INDEX function to return an array from a given row in cell range B2:E5.

INDEX(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1, SMALL(IF(SMALL(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1, ""), 1)=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1, ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1), 1), , 1)

becomes

INDEX(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1, 2, , 1)

becomes

INDEX({2,13,2,13;10,2,1,10;7,12,13,6;13,7,7,2}, 2, , 1)

and returns

{10,2,1,10}

#### Step 7 - Match value in array

This step uses the MATCH function to return the correct column number needed to extract the value we need from cell range B2:E5.

MATCH(MIN(IF(COUNTIF($B$7:B7, $B$2:$E$5)>0, "", COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1)), INDEX(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1, SMALL(IF(SMALL(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1, ""), 1)=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1, ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1), 1), , 1), 0)

becomes

MATCH(MIN(IF(COUNTIF($B$7:B7, $B$2:$E$5)>0, "", COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1)), {10,2,1,10}, 0)

becomes

MATCH(1, {10,2,1,10}, 0)

and returns 3. This is the column number we need.

#### Step 8 - Return value

INDEX($B$2:$E$5, SMALL(IF(SMALL(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1, ""), 1)=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1, ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1), 1), MATCH(MIN(IF(COUNTIF($B$7:B7, $B$2:$E$5)>0, "", COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1)), INDEX(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1, SMALL(IF(SMALL(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1, ""), 1)=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1, ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1), 1), , 1), 0), 1)

becomes

=INDEX($B$2:$E$5, 3, 2)

and returns "Apple" in cell B8.

### Get Excel *.xlsx file

Extract a unique distinct list sorted alphabetically from a range.xlsx

### Unique distinct values category

First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]

Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]

This article shows how to extract unique distinct values based on a condition applied to an adjacent column using formulas. […]

The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]

Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]

Question: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent […]

This article demonstrates formulas that extract unique distinct values and ignore blank empty cells. Table of contents Extract a unique […]

Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]

Question: How do I create a unique distinct list where other columns meet two criteria using excel array formula? Answer: […]

This article demonstrates formulas that extract sorted unique distinct values from a cell range containing also blanks. Unique distinct values […]

This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]

Question: How do I create a unique distinct list where adjacent cell values contain a search string? AA102 CA AA103 […]

EEK asks: I am looking for the same formula on this page, but targeting a range of MxN (spanning multiple […]

Filtering unique distinct text values and sort them based on the sum of adjacent values can easily be done by […]

This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see the […]

Question: How do I create a unique distinct list from a column sorted A to Z using array formula? Array […]

The image above demonstrates a formula in cell F3 that extracts unique distinct values from column B if they contain […]

Question: How to create unique distinct year and months from a long date listing (column A)? You can find the […]

Table of Contents Filter unique distinct values based on a date range How to enter an array formula Filter unique […]

Question: I want a unique distinct list to be created from a column where an adjacent column has text cell […]

## Functions in this article

More than 1300 Excel formulas

## Excel formula categories

## Excel categories

### 3 Responses to “Extract a unique distinct list sorted from A-Z from range”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

This is exactly what I've been looking for... almost. It breaks if there are blank cells in the named range. Is there a way to get this to work if there are blanks in the range?

Thanks.

Dave,

see this blog post: https://www.get-digital-help.com/2009/09/16/extract-a-unique-distinct-list-sorted-alphabetically-removing-blanks-from-a-range-in-excel/

Oscar,

I need to sort from largest value to smallest and continue to have issues. I'm working with values and have flipped SMALL to LARGE and Min to MAX. I must be missing something simple, could you please point me in the correct direction?.

Appreciate all the help.

Alex