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.

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

or

(2)

Extract a unique distinct list sorted alphabetically removing blanks from a range

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.

Download excel sample file for this tutorial.
extract-a-unique-distinct-list-sorted-alphabetically-removing-blanks-from-a-range-3.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

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 row 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:

How to extract a unique distinct list from a column in excel
Extract a unique distinct list from two columns using excel 2007 array formula
Extract a unique distinct list from three columns in excel

Extract distinct unique sorted year and month list from a date series in excel
Create a unique distinct list from a date range in excel

Unique values from multiple columns using array formulas
Extract a unique distinct list sorted from A-Z from range in excel
Sort a range by occurence using array formula in excel
Filter unique distinct values from two ranges combined in excel 2007

Create a unique list and sort by occurrances from large to small
Unique list to be created from a column where an adjacent column has text cell values
Create unique list from column where an adjacent column meets criteria
How to create a unique distinct list where other columns meet two criteria