## Extract a unique distinct list sorted alphabetically removing blanks from a range in excel

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.

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

or

(2)

Array formula in B8:

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 *.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

*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

### 20 Responses to “Extract a unique distinct list sorted alphabetically removing blanks from a range in excel”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Awesome resource! Using Excel 2003 SP3 if I blank out any of the first 6 values (left to right / top to bottom) it turns all the cells to ZERO. Any suggestions?

Thanks! I have updated the formula and the attached excel file.

I'm using this code with Excel 2003 SP3 and i'm getting #NUM! in the fields where it should be blank.

The column where the content is being pulled from has been defined as a list (Property) and it also has data validation to pull information from a list in a different sheet so that the users can't input a "property" that isn't on the list.

Is this causing the #NUM! error in the distinct list? Is there a way around it?

Below is the modified code.

=INDEX(Property, SMALL(IF(SMALL(IF(COUNTIF($L$1:L1, Property)+ISBLANK(Property)=0, COUNTIF(Property, "<"&Property)+1, ""), 1)=IF(ISBLANK(Property), "", COUNTIF(Property, "0, "", COUNTIF(Property, "<"&Property)+1)), INDEX(IF(ISBLANK(Property), "", COUNTIF(Property, "<"&Property)+1), SMALL(IF(SMALL(IF(COUNTIF($L$1:L1, Property)+ISBLANK(Property)=0, COUNTIF(Property, "<"&Property)+1, ""), 1)=IF(ISBLANK(Property), "", COUNTIF(Property, "<"&Property)+1), ROW(Property)-MIN(ROW(Property))+1), 1), , 1), 0), 1)

darzon,

I am not sure if wordpress removed any "greater than" or "less than" signs from your code.

Here is what it should look like:

=INDEX(Property, SMALL(IF(SMALL(IF(COUNTIF($L$1:L1, Property)+ISBLANK(Property)=0, COUNTIF(Property, "< "&Property)+1, ""), 1)=IF(ISBLANK(Property), "", COUNTIF(Property, "<"&Property)+1), ROW(Property)-MIN(ROW(Property))+1), 1), MATCH(MIN(IF(COUNTIF($L$1:L1, Property)+ISBLANK(Property)>0, "", COUNTIF(Property, "<"&Property)+1)), INDEX(IF(ISBLANK(Property), "", COUNTIF(Property, "<"&Property)+1), SMALL(IF(SMALL(IF(COUNTIF($L$1:L1, Property)+ISBLANK(Property)=0, COUNTIF(Property, "<"&Property)+1, ""), 1)=IF(ISBLANK(Property), "", COUNTIF(Property, "<"&Property)+1), ROW(Property)-MIN(ROW(Property))+1), 1), , 1), 0), 1) The code above does not remove #NUM errors. I can´t create a formula that removes #NUM error in excel 2003. I have more than seven levels of nesting. If you upgrade to excel 2007 or later versions you can use IFERROR() function.

hi,

I want to know how to find out frequency of each word from a group of sentences.

Eg.

Amit is a good boy.

He works with XYZ.

Anil is good boy.

Here we have 3 sentences. Result should be something like this:

Amit -1

is - 2

a -1

good - 2

boy -2

He -1

Works -1

with - 1

XYZ -1

Please help me out to get this result. after looking your multiple post i thought i split sentences into different columns (txt to columns option with blank)and define tbl range and work accordingly but not getting proper results.

Please advise.

Thanks,

Amit

Amit,

read this post:

Excel udf: Word frequency

Thank you so much Oscar.

Regards,

Amit

Hello,

I know this formula works to create a unique list and it works extermely well. However, in my situation, I have some duplicate values in my range and I would actually like to create a list of all the values sorted. (if they are duplicates, they can just be listed twice or thrice). Would you be able to help me with that?

Thank You,

Harsh

Harsh,

Array formula:

=INDEX(tbl, SMALL(IF(SMALL(IF((COUNTIF(tbl, tbl)< =1)+ISBLANK(tbl)=0, COUNTIF(tbl, "<"&tbl)+1, ""), ROW(A1))=IF(ISBLANK(tbl), "", COUNTIF(tbl, "<"&tbl)+1), ROW(tbl)-MIN(ROW(tbl))+1), 1), MATCH(SMALL(IF((COUNTIF(tbl, tbl)<=1)+ISBLANK(tbl)=0, COUNTIF(tbl, "<"&tbl)+1, ""), ROW(A1)), INDEX(IF(ISBLANK(tbl), "", COUNTIF(tbl, "<"&tbl)+1), SMALL(IF(SMALL(IF((COUNTIF(tbl, tbl)<=1)+ISBLANK(tbl)=0, COUNTIF(tbl, "<"&tbl)+1, ""), ROW(A1))=IF(ISBLANK(tbl), "", COUNTIF(tbl, "<"&tbl)+1), ROW(tbl)-MIN(ROW(tbl))+1), 1), , 1), 0), 1) Download excel file extract-duplicates-sorted-alphabetically-removing-blanks-from-a-range.xls

Hello Oscar,

Thank you for your help! However, I think I was not clear in my earlier inquiry. What I meant to ask was i wan't a complete list of all the values in the array (not just the duplicates). and if banana exists twice in the array, then in the produced list banana would be listed twice. Would that be possible?

Thank You,

Harsh

Harsh,

Read this:

http://www.cpearson.com/excel/MatrixToVector.aspx

Is there way to modify this formula so it sorts by occurrence versus alphabetically?

Thank you for your assistance!

I should add descendingly.

Very useful material here. Thanks!

However, my range name is discontinuous (e.g. tbl = A1:A6 and C1:C6). It seems the formula does not work in this case.

Would e brilliant if it would work.

Marius

Marius,

Check out the Filter unique distinct values from multiple sheets add-in.

http://www.get-digital-help.com/2009/03/30/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/#addin

It can use discontinuous ranges but unfortunately the add-in does not sort the values.

Hi Oscar, super tutorials as always.

Can you please update the formula to show blanks "" where iserror (to hide #NUM! cells)?

My application: I have 3 sheets with blanks and different abbreviations.

In a separate sheet I want to generate a Legend table (an alphabetic list of unique values, no blanks, dynamically updated).

I think this is a good start as a formula for my project.

Also, after having generated the "Legend" of all abbreviations used in those 3 sheets, I wish to have each abbreviation explained in the adjacent column (from an existing comprehensive list, which is defined as a named range).

Should a simple Vlookup do the trick?

Hi OP,

I have can see that this formula works well for the purpose its intended, how ever is it possible to replace #NUM! with a blank i.e. "". I tried the usual iserror method but it does not work is a array formula.

As there's been a few asking how to resolve same issue, I just thought I post my method, it involves a helper column I know.. I know) but at lease I was able to move on with the work I needed to do.

I created a the unique sorted list using the formula in this article, and next to it added a helper column that gave a true or false to the value in cell B8.

in Cell C8 I put in:

=IF(ISNONTEXT(B8)=TRUE,"",B8)

This will now list all test values sorted as I originally wanted.

Excellent Formula Oscar.

Hello Everyone,

Oscar, thank you for making this guide! I'm having some difficulty altering this formula to read a row of data. I figured I could replace instances of ROW( with COLUMN( but this is not working. Does anyone have any thoughts?