Extract unique distinct values A to Z from a range and ignore blanks

This article demonstrates formulas that extract sorted unique distinct values from a cell range containing also blanks. Unique distinct values are all values merged into one distinct value, in other words, the list contains no duplicates.

1. Extract unique distinct values A to Z from a range and ignore blanks - Excel 365

Excel 365 formula in cell B8:

Explaining formula

Step 1 - Rearrange values

The TOCOL function lets you rearrange values in a 2D cell range to a single column.

TOCOL(array, [ignore], [scan_by_col])

TOCOL(B2:E5)

becomes

TOCOL({"Blackberry", 0, 0, 0;0, "Watermelon", "Apple", "Peach";0, "Kiwifruit", 0, "Blackberry";"Raspberry", "Blueberry", "Cranberry", "Banana"})

and returns

{"Blackberry"; 0; 0; 0; 0; "Watermelon"; "Apple"; "Peach"; 0; "Kiwifruit"; 0; "Blackberry"; "Raspberry"; "Blueberry"; "Cranberry"; "Banana"}

Step 2 - Extract a unique distinct list

The UNIQUE function lets you extract both unique and unique distinct values and also compare columns to columns or rows to rows.

UNIQUE(array, [by_col], [exactly_once])

UNIQUE(TOCOL(B2:E5))

becomes

UNIQUE({"Blackberry"; 0; 0; 0; 0; "Watermelon"; "Apple"; "Peach"; 0; "Kiwifruit"; 0; "Blackberry"; "Raspberry"; "Blueberry"; "Cranberry"; "Banana"})

and returns

{"Blackberry"; 0; "Watermelon"; "Apple"; "Peach"; "Kiwifruit"; "Raspberry"; "Blueberry"; "Cranberry"; "Banana"}

Step 3 - Sort values

The SORT function sorts values from a cell range or array.

SORT(array, [sort_index], [sort_order], [by_col])

SORT(UNIQUE(TOCOL(B2:E5)))

becomes

SORT({"Blackberry"; 0; "Watermelon"; "Apple"; "Peach"; "Kiwifruit"; "Raspberry"; "Blueberry"; "Cranberry"; "Banana"})

and returns

{"Apple"; "Banana"; "Blackberry"; "Blueberry"; "Cranberry"; "Kiwifruit"; "Peach"; "Raspberry"; "Watermelon"; 0}

Step 4 - Logical test

To remove 0 (zeros) we need to identify values not equal to zero. The less than and larger than characters combined is the same as not equal to.

SORT(UNIQUE(TOCOL(B2:E5)))<>0

becomes

{"Apple"; "Banana"; "Blackberry"; "Blueberry"; "Cranberry"; "Kiwifruit"; "Peach"; "Raspberry"; "Watermelon"; 0}<>0

and returns

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

Step 5 - Filter values not equal to 0 (zero)

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

FILTER(array, include, [if_empty])

FILTER(SORT(UNIQUE(TOCOL(B2:E5))),SORT(UNIQUE(TOCOL(B2:E5)))<>0)

becomes

FILTER({"Apple"; "Banana"; "Blackberry"; "Blueberry"; "Cranberry"; "Kiwifruit"; "Peach"; "Raspberry"; "Watermelon"; 0},{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE})

and returns

{"Apple"; "Banana"; "Blackberry"; "Blueberry"; "Cranberry"; "Kiwifruit"; "Peach"; "Raspberry"; "Watermelon"}

Step 6 - Shorten formula

The LET function names intermediate calculation results which can shorten formulas considerably and improve performance.

LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])

FILTER(SORT(UNIQUE(TOCOL(B2:E5))),SORT(UNIQUE(TOCOL(B2:E5)))<>0)

SORT(UNIQUE(TOCOL(B2:E5))) is repeated twice in the formula, I will name this intermediate calculation x.

LET(x,SORT(UNIQUE(TOCOL(B2:E5))),FILTER(x,x<>0))

Back to top

2. Extract unique distinct values A to Z from a range and ignore blanks

This is an answer to a question in this blog post: Extract a unique distinct list sorted from A-Z from range in excel

Question: 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?

Answer: There are two things you can consider.

(1) Fill the blanks with some text

1. Select the range
2. Press F5
3. Press with left mouse button on "Special..."
4. Press with left mouse button on "Blanks"
5. Press with left mouse button on OK!
6. Type A
7. Press Ctrl + Enter

All blanks are filled with the letter A. Remember that your new unique distinct list will contain A.

See this post: How to automatically fill all blanks with missing data or formula

or

(2)

Array formula in B8:

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.

Then copy cell B8 and paste it down as far as necessary.

Back to top

How to implement array formula to your workbook
If your list starts at, for example, F3. Change $B$7:B7 in the above formulas to F2:$F$2.

Back to top

Explaining array formula in cell B8

Step 1 - Count previous values that are ignored

The COUNTIF function counts cells based on a condition, however, in this case, I am using it to check that no duplicates are returned.

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

returns {0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0}.

The array above contains only 0's (zeros), which means that no value has yet been shown. Cell range $B$7:B7 expands as the formula is copied to cells below, this makes sure that all previous values are checked.

Step 2 - Identify blank cells

The ISBLANK function returns TRUE if cell is empty and FALSE if it contains at least one character.

ISBLANK($B$2:$E$5)

returns {FALSE, TRUE, TRUE, TRUE; TRUE, FALSE, FALSE, FALSE; TRUE, FALSE, TRUE, FALSE; FALSE, FALSE, FALSE, FALSE}.

The image above shows the array entered in cell range B7:E10, boolean values TRUE corresponds to the empty values in cell range B2:E5.

Step 3 - Add arrays and compare to zero

COUNTIF($B$7:B9, $B$2:$E$5)+ISBLANK($B$2:$E$5)=0

becomes

{0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0}+{FALSE, TRUE, TRUE, TRUE; TRUE, FALSE, FALSE, FALSE; TRUE, FALSE, TRUE, FALSE; FALSE, FALSE, FALSE, FALSE}=0

becomes

{0,1,1,1;1,0,0,0;1,0,1,0;0,0,0,0}=0

and returns

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

The array above keeps track of blank cells and prior values.

Step 4 - Replace boolean values with  numbers based on their relative position if they were sorted

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

becomes

IF({TRUE,FALSE, FALSE,FALSE;FALSE,TRUE, TRUE,TRUE;FALSE,TRUE, FALSE,TRUE;TRUE,TRUE, TRUE,TRUE},{3,1,1,1;1,10,1,8;1,7,1,3;9,5,6,2},"")

and returns

{3,"","","";"",10,1,8;"",7,"",3;9,5,6,2}

Step 5 - Get smallest value in array

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

becomes

SMALL({3,"","","";"",10,1,8;"",7,"",3;9,5,6,2}, 1)

and returns 1.

Step 6 - Replace smallest value with row number

IF(SMALL(IF(COUNTIF($B$7:B7, $B$2:$E$5)+ISBLANK($B$2:$E$5)=0, COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1, ""), 1)=IF(ISBLANK($B$2:$E$5), "", 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=IF(ISBLANK($B$2:$E$5), "", 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={3, "", "", "";"", 10, 1, 8;"", 7, "", 3;9, 5, 6, 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 7 -Get smallest value

SMALL(IF(SMALL(IF(COUNTIF($B$7:B7,$B$2:$E$5)+ISBLANK($B$2:$E$5)=0,COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1,""),1)=IF(ISBLANK($B$2:$E$5),"",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 get the correct value.

Step 8 - Extract smallest value

The following steps calculate the column number needed to get the correct value.

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

becomes

MIN({3,"","","";"",10,1,8;"",7,"",3;9,5,6,2})

and returns 1.

Step 9 - Extract array from the correct row

Get array needed in the first argument in the INDEx function.

INDEX(IF(ISBLANK($B$2:$E$5),"",COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1),SMALL(IF(SMALL(IF(COUNTIF($B$7:B7,$B$2:$E$5)+ISBLANK($B$2:$E$5)=0,COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1,""),1)=IF(ISBLANK($B$2:$E$5),"",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({3, "", "", "";"", 10, 1, 8;"", 7, "", 3;9, 5, 6, 2},SMALL(IF(SMALL(IF(COUNTIF($B$7:B7,$B$2:$E$5)+ISBLANK($B$2:$E$5)=0,COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1,""),1)=IF(ISBLANK($B$2:$E$5),"",COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1),ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1),1),,1)

The following calculation returns the row number, I have already shown that calculation in steps 1 to 7.

INDEX({3, "", "", "";"", 10, 1, 8;"", 7, "", 3;9, 5, 6, 2},SMALL(IF(SMALL(IF(COUNTIF($B$7:B7,$B$2:$E$5)+ISBLANK($B$2:$E$5)=0,COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1,""),1)=IF(ISBLANK($B$2:$E$5),"",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({3, "", "", "";"", 10, 1, 8;"", 7, "", 3;9, 5, 6, 2},2,,1)

and returns {"", 10, 1, 8}

Step 10 - Calculate relative column number

MATCH(MIN(IF(COUNTIF($B$7:B7,$B$2:$E$5)+ISBLANK($B$2:$E$5)>0,"",COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1)),INDEX(IF(ISBLANK($B$2:$E$5),"",COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1),SMALL(IF(SMALL(IF(COUNTIF($B$7:B7,$B$2:$E$5)+ISBLANK($B$2:$E$5)=0,COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1,""),1)=IF(ISBLANK($B$2:$E$5),"",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(1,{"", 10, 1, 8},0)

and returns 3. This is the column number needed to get the correct value.

Step 11 - Get value

=INDEX($B$2:$E$5,SMALL(IF(SMALL(IF(COUNTIF($B$7:B7,$B$2:$E$5)+ISBLANK($B$2:$E$5)=0,COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1,""),1)=IF(ISBLANK($B$2:$E$5),"",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)+ISBLANK($B$2:$E$5)>0,"",COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1)),INDEX(IF(ISBLANK($B$2:$E$5),"",COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1),SMALL(IF(SMALL(IF(COUNTIF($B$7:B7,$B$2:$E$5)+ISBLANK($B$2:$E$5)=0,COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1,""),1)=IF(ISBLANK($B$2:$E$5),"",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,2,3)

and returns "Apple" in cell B8.

Back to top

Get excel *.xls file
extract-a-unique-distinct-list-sorted-alphabetically-removing-blanks-from-a-range-4.xls

Back to top

Unique distinct values category

5 easy ways to extract Unique Distinct Values
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]

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

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

Excel categories

Comments (23)