Author: Oscar Cronquist Article last updated on January 11, 2023

This article demonstrates ways to extract unique distinct sorted from A to Z ignoring blanks, and based on a condition.

1. Extract a unique distinct list sorted from A to Z ignore blanks

The image above demonstrates a formula in cell D3 that extracts unique distinct numbers and text values sorted from A to Z and also ignores blank cells.

Array formula in cell D3:

=IFERROR(SMALL(IF((COUNTIF($D$2:D2, $B$3:$B$16)=0)*ISNUMBER($B$3:$B$16), $B$3:$B$16, "A"), 1), INDEX($B$3:$B$16, MATCH(SMALL(IF(ISTEXT($B$3:$B$16)*(COUNTIF(D2:$D$2, $B$3:$B$16)=0), COUNTIF($B$3:$B$16, "<"&$B$3:$B$16), ""), 1), IF(ISTEXT($B$3:$B$16), COUNTIF($B$3:$B$16, "<"&$B$3:$B$16), ""), 0)))


How to create an array formula

  1. Copy above array formula
  2. Select cell D3
  3. Press with left mouse button on in formula bar
  4. Paste formula (Ctrl + v)
  5. Press and hold Ctrl + Shift
  6. Press Enter

How to copy array formula

Copy cell B2 and paste it down as far as needed.

Explaining formula in cell D3

There are two formulas in the IFERROR function, when the first formula is running out of numbers to return the second formula starts extracting text values.

IFERROR( formula1, formula2)

Step 1 - Prevent extracting duplicate numbers

The COUNTIF function counts cells in cell range based on a condition or criteria. If the value is equal to 0 then it has not been displayed yet. The first cell reference grows when the cell is copied to cells below, this makes the formula aware of previously displayed value above the current cell.

COUNTIF($D$2:D2,$B$3:$B$16)=0)

becomes

COUNTIF("Sorted list",{"MM"; 8; 12; "AA"; "TT"; 0; "FF"; "KK"; 9; "CC"; 0; 9; "AA"; "NN"})=0

becomes

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

Step 2 - Identify numbers in column

The ISNUMBER function returns TRUE if value is a number.

ISNUMBER($B$3:$B$16)

becomes

ISNUMBER({"MM"; 8; 12; "AA"; "TT"; 0; "FF"; "KK"; 9; "CC"; 0; 9; "AA"; "NN"})

and returns

{FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE}

Step 3 - Multiply arrays

Both arrays must return TRUE for the logical expression to return TRUE. In ordeer to achieve AND logic I multiply the arrays. TRUE * TRUE = TRUE, TRUE * FALSE = FALSE and FALSE * FALSE = FALSE.

(COUNTIF($D$2:D2, $B$3:$B$16)=0)*ISNUMBER($B$3:$B$16)

becomes

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}*{FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE}

and returns

{0;1;1;0;0;0;0;0;1;0;0;1;0;0}. 0 (zeros is the equivalent to FALSE and any other number is equal to boolean TRUE.

Step 4 - Replace TRUE with numbers in column

The IF function returns one value (argument2) if TRUE and another (argument3) if FALSE. The IF function returns "A" if logical expression is FALSE, this makes the SMALL function ignore the text value in the next step.

IF((COUNTIF($D$2:D2, $B$3:$B$16)=0)*ISNUMBER($B$3:$B$16), $B$3:$B$16, "A")

becomes

IF({0;1;1;0;0;0;0;0;1;0;0;1;0;0}, $B$3:$B$16, "A")

becomes

IF({0;1;1;0;0;0;0;0;1;0;0;1;0;0}, {"MM"; 8; 12; "AA"; "TT"; 0; "FF"; "KK"; 9; "CC"; 0; 9; "AA"; "NN"}, "A")

and returns {"A";8;12;"A";"A";"A";"A";"A";9;"A";"A";9;"A";"A"}.

Step 5 - Extract smallest number

To be able to return a new value in a cell each I use the SMALL function to filter numbers from smallest to largest.

SMALL(IF((COUNTIF($D$2:D2, $B$3:$B$16)=0)*ISNUMBER($B$3:$B$16), $B$3:$B$16, "A"), 1)

becomes

SMALL({"A";8;12;"A";"A";"A";"A";"A";9;"A";"A";9;"A";"A"}, 1)

and returns 8 in cell D3.

Explaining formula in cell D6

Step 1 - Prevent duplicates and only extract text values

This step and forward explains how to extract text values. We begin with cell D6 which is the first cell that extracts text values in our example. The ISTEXT function returns TRUE if value is a text value. The IF function returns a number representing the rank order if the list were sorted from A to Z.

IF(ISTEXT($B$3:$B$16)*(COUNTIF(D$2:$D5, $B$3:$B$16)=0), COUNTIF($B$3:$B$16, "<"&$B$3:$B$16), "")

becomes

IF({1;0;0;1;1;0;1;1;0;1;0;0;1;1}, COUNTIF($B$3:$B$16, "<"&$B$3:$B$16), "")

becomes

IF({1; 0; 0; 1; 1; 0; 1; 1; 0; 1; 0; 0; 1; 1}, {5; 0; 3; 0; 7; 0; 3; 4; 1; 2; 0; 1; 0; 6}, "")

and returns

{5; ""; ""; 0; 7; ""; 3; 4; ""; 2; ""; ""; 0; 6}.

Step 2 - Extract k-th smallest number

SMALL(IF(ISTEXT($B$3:$B$16)*(COUNTIF(D$2:$D5, $B$3:$B$16)=0), COUNTIF($B$3:$B$16, "<"&$B$3:$B$16), ""), 1)

becomes

SMALL({5; ""; ""; 0; 7; ""; 3; 4; ""; 2; ""; ""; 0; 6}, 1)

and returns 0 (zero).

Step 3 - Find number in array

The MATCH function finds the relative position of a value in an array or cell range.

MATCH(SMALL(IF(ISTEXT($B$3:$B$16)*(COUNTIF(D$2:$D5, $B$3:$B$16)=0), COUNTIF($B$3:$B$16, "<"&$B$3:$B$16), ""), 1), IF(ISTEXT($B$3:$B$16), COUNTIF($B$3:$B$16, "<"&$B$3:$B$16), ""), 0)

becomes

MATCH(0, IF(ISTEXT($B$3:$B$16), COUNTIF($B$3:$B$16, "<"&$B$3:$B$16), ""), 0)

becomes

MATCH(0, {5; ""; ""; 0; 7; ""; 3; 4; ""; 2; ""; ""; 0; 6}, 0)

and returns 4.

Step 4 - Extract value based on position

The INDEX function returns a value based on a cell reference and column/row numbers.

INDEX($B$3:$B$16, MATCH(SMALL(IF(ISTEXT($B$3:$B$16)*(COUNTIF(D$2:$D5, $B$3:$B$16)=0), COUNTIF($B$3:$B$16, "<"&$B$3:$B$16), ""), 1), IF(ISTEXT($B$3:$B$16), COUNTIF($B$3:$B$16, "<"&$B$3:$B$16), ""), 0))

becomes

INDEX($B$3:$B$16, 4)

and returns "AA" in cell D6.

Get the Excel file


Unique-and-Sort-numbers-and-text-cells-using-excel-array-formula.xlsx

2. Create a unique distinct sorted list containing both numbers text removing blanks with a condition

Array formula in cell F4:

=IFERROR(INDEX(Table1[Values], MATCH(SMALL(IF(Table1[Condition]=$F$2, IF(ISBLANK(Table1[Values]), "", IF(COUNTIF($F$3:F3, Table1[Values])=0, IF(ISNUMBER(Table1[Values]), COUNTIF(Table1[Values], "<"&Table1[Values]), COUNTIF(Table1[Values], "<"&Table1[Values])+SUM(1*ISNUMBER(Table1[Values]))+1), "")), ""), 1), IF(Table1[Condition]=$F$2, IF(ISBLANK(Table1[Values]), "", IF(COUNTIF($F$3:F3, Table1[Values])=0, IF(ISNUMBER(Table1[Values]), COUNTIF(Table1[Values], "<"&Table1[Values]), COUNTIF(Table1[Values], "<"&Table1[Values])+SUM(1*ISNUMBER(Table1[Values]))+1), "")), ""), 0)), "")

How to create an array formula

How to copy array formula in cell F4

  1. Select cell F4
  2. Copy (Ctrl + c)
  3. Select cell range F5:F10
  4. Paste (Ctrl + v)

Get the Excel file


unique-list-sorted-alphabetically-with-a-condition1.xlsx

3. List unique distinct sorted values removing blanks based on a condition - Excel 365

List unique distinct sorted values removing blanks based on a condition Excel 365

Update 10th of December 2020: Excel 365 subscribers can now use this much shorter regular formula in cell D3.

=SORT(UNIQUE(FILTER(B3:B16,B3:B16<>"")))

Here is how it works:
Extract unique distinct values sorted from A to Z ignoring blanks

4. Create a unique distinct sorted list containing both numbers text removing blanks with a condition - Excel 365

Excel 365 formula in cell F4:

=SORT(UNIQUE(FILTER(Table13[Values],(Table13[Condition]=F2)*(Table13[Values]<>""))))

Explaining formula in cell F4

Step 1 - Compare values in column Condition to value in cell F2

The equal sign is a logical operator that lets you compare value to value, it returns a boolean value TRUE or FALSE.

Table13[Condition]=F2

becomes

{"Yes"; "Yes"; "No"; "Yes"; "No"; "Yes"; "No"; "Yes"; "No"; "Yes"}="Yes"

and returns

{TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE}.

Step 2 - Check if values in  column "Values" are not equal to nothing

The less than and greater than signs combined let you check if a value is not equal to another value, the result is a boolean value TRUE or FALSE.

Table13[Values]<>""

becomes

{"AA"; "CC"; "BB"; "DD"; "CC"; 0; 1; 2; 1; 2}<>""

and returns

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}.

Step 3 - Multiply arrays - AND logic

The asterisk lets you multiply numbers or boolean values in an Excel formula. Multiplying boolean values lets you perform AND logic between two values.

TRUE * TRUE = TRUE
TRUE * FALSE = FALSE
FALSE * TRUE = FALSE
FALSE * FALSE = FALSE

AND logic means that all values must be TRUE in order to return TRUE.

Also, multiplying boolean values convert them automatically to their numerical equivalents.

TRUE - 1
FALSE - 0 (zero)

(Table13[Condition]=F2)*(Table13[Values]<>"")

becomes

{TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE} * {TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}

and returns

{1; 1; 0; 1; 0; 0; 0; 1; 0; 1}.

Step 4 - Filter values in Table13[Values]

The FILTER function extracts values/rows based on a condition or criteria.

Function syntax: FILTER(array, include, [if_empty])

FILTER(Table13[Values],(Table13[Condition]=F2)*(Table13[Values]<>""))

becomes

FILTER(Table13[Values],{1; 1; 0; 1; 0; 0; 0; 1; 0; 1})

becomes

FILTER({"AA"; "CC"; "BB"; "DD"; "CC"; 0; 1; 2; 1; 2},{1; 1; 0; 1; 0; 0; 0; 1; 0; 1})

and returns

{"AA"; "CC"; "DD"; 2; 2}.

Step 5 - Extract unique distinct values

The UNIQUE function returns a unique or unique distinct list.

Function syntax: UNIQUE(array,[by_col],[exactly_once])

UNIQUE(FILTER(Table13[Values],(Table13[Condition]=F2)*(Table13[Values]<>"")))

becomes

UNIQUE({"AA"; "CC"; "DD"; 2; 2})

and returns

{"AA"; "CC"; "DD"; 2}.

Step 6 - Sort values from A to Z

The SORT function sorts values from a cell range or array

Function syntax: SORT(array,[sort_index],[sort_order],[by_col])

SORT(UNIQUE(FILTER(Table13[Values],(Table13[Condition]=F2)*(Table13[Values]<>""))))

becomes

SORT({"AA"; "CC"; "DD"; 2})

and returns

{2; "AA"; "CC"; "DD"}.