## Extract a unique distinct list sorted alphabetically and ignore blanks from a range

*Article updated on February 18, 2018*

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.

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), […]

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

Extract unique distinct values from a multi-column cell range

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

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

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

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 […]

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

Sort a range based on value frequency

Learn how to sort cell values by frequency.

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

Sorting numbers and text cells in an descending order also removing blanks

This blog article is one out of five articles on the same subject. Sorting text cells using array formula in […]### 21 Responses to “Extract a unique distinct list sorted alphabetically and ignore blanks from a range”

### Leave a Reply

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

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

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

https://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.

https://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?

Thanks for the work on these formulas. I have this working on range over 6 columns, however I only need the unique values of data in this range if another column matches data entered into a cell.

This is a formula that worked for a single column (but not 6 columns)

`=INDEX(WLDRI, MATCH(0, IF((PKG=$C$2),COUNTIF($R$2:R2,WLDRI), ""), 0))`

The key here is I want unique values returned for columns B3:G if column A3:A matches data in C2.

Thanks again.