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. Click "Special..."
4. Click "Blanks"
5. Click 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.

or

(2)

Array formula in B8:

=INDEX(tbl, SMALL(IF(SMALL(IF(COUNTIF(\$B\$7:B7, tbl)+ISBLANK(tbl)=0, COUNTIF(tbl, "<"&tbl)+1, ""), 1)=IF(ISBLANK(tbl), "", COUNTIF(tbl, "<"&tbl)+1), ROW(tbl)-MIN(ROW(tbl))+1), 1), MATCH(MIN(IF(COUNTIF(\$B\$7:B7, tbl)+ISBLANK(tbl)>0, "", COUNTIF(tbl, "<"&tbl)+1)), INDEX(IF(ISBLANK(tbl), "", COUNTIF(tbl, "<"&tbl)+1), SMALL(IF(SMALL(IF(COUNTIF(\$B\$7:B7, tbl)+ISBLANK(tbl)=0, COUNTIF(tbl, "<"&tbl)+1, ""), 1)=IF(ISBLANK(tbl), "", COUNTIF(tbl, "<"&tbl)+1), ROW(tbl)-MIN(ROW(tbl))+1), 1), , 1), 0), 1) + CTRL + SHIFT + ENTER

Copy cell B8 and paste it down as far as necessary.

Named ranges
tbl (B2:E5)
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 \$B\$7:B7 in the above formulas to F2:\$F\$2.

extract-a-unique-distinct-list-sorted-alphabetically-removing-blanks-from-a-range-3.xls

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

SUM(number1,[number2],)
Adds all the numbers in a range of cells

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

MATCH(lookup_value;lookup_array, [match_type]
Returns the relative position of an item in an array that matches a specified value

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

SMALL(array,k)
Returns the k-th smallest number in this data set.

ISBLANK(value)
Checks whether a reference is to an empty cell and returns TRUE or FALSE

More blog articles on the same subject: