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
- Select the range
- Press F5
- Click "Special..."
- Click "Blanks"
- Click OK!
- Type A
- Press Ctrl + Enter
All blanks are filled with the letter A. Remember that your new unique distinct list will contain A.
Array formula in B8:
Copy cell B8 and paste it down as far as necessary.
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
Functions in this article:
Counts the number of cells within a range that meet the given condition
Adds all the numbers in a range of cells
Returns the relative position of an item in an array that matches a specified value
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range
Returns the k-th smallest number in this data set.
Checks whether a reference is to an empty cell and returns TRUE or FALSE
More blog articles on the same subject:
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