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.

Learn how to filter a multi-column and multi-row range and sort the result from A to Z:

Sort a range from A to Z using array formula in excel

Question: How do I sort a range alphabetically using excel array formula? Answer: Array formula in B8: =INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), […]

Comments(3) Filed in category: Excel, Sort values

Learn how to filter unique distinct values from a multi-column and multi-row cell range:

Unique distinct values from multiple columns using array formula

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

Comments(23) Filed in category: Excel, Unique distinct values

The following article demonstrates how to filter a unique distinct list sorted from A to Z, from a multi-column and multi-row cell range:

Extract a unique distinct list sorted from A-Z from range in excel

Inspired from a comment in this article Unique values from multiple columns using array formulas I have now created this […]

Comments(3) Filed in category: Excel, Unique distinct values

How to filter duplicate values from a multi-column and multi-row cell range:

Extract duplicates from a range using excel array formula

Overview Inspired by a comment in this post Unique values from multiple columns using array formulas, I  created an array […]

Comments(0) Filed in category: Duplicate values, Excel

This post shows you how to filter a multi-column and multi-row cell range based on frequency:

Sort a range by occurence using array formula in excel

Learn how to sort cell values by frequency.

Comments(4) Filed in category: Excel, Sort values

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 *.xls file
extract-a-unique-distinct-list-sorted-alphabetically-removing-blanks-from-a-range-3.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 number in this data set.

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