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

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 [Array formula]

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

In this blog post I will provide two solutions on how to remove blank cells and a solution on how […]

Merge two columns with possible blank cells

Question: This article is terrific. Thanks so much for posting this solution! I do have one question: Let's say my […]

Create a unique distinct sorted list containing both numbers text removing blanks

Table of contents Create a unique distinct sorted list containing both numbers text removing blanks Create a unique distinct sorted […]

How to use the ISBLANK function

The ISBLANK function returns TRUE if the argument is an empty cell, returns FALSE if not. Excel Function Syntax ISBLANK(value) […]

5 easy ways to extract unique distinct values

You have quite a few options to choose from if you are looking for a way to create a unique […]

Extract a unique distinct list from two columns

Question: I have two ranges or lists (List1 and List2) from where I would like to extract an unique distinct […]

Create a unique distinct alphabetically sorted list

The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]

Vlookup – Return multiple unique distinct values

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]

Extract a unique distinct list and sum amounts based on a condition

Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]

### 23 Responses to “Extract a unique distinct list sorted alphabetically and ignore blanks from a range”

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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.

Man you're awesome. Your formula work pretty soft. Something I would like to ask is wether you can give some piece of advice about how to learn Excel. Of course I have to practice utterly a lot, but you can tell me like certain steps (you have to start by this, and after that you have to do this, etcetera); something general (I would appreciate to you Mr Cronquist).

Take care

Regards

Sergio Bautista

In testing the formula shown in this article for "Extract a unique distinct list sorted alphabetically and ignore blanks from a range" shown below, I found that the formula breaks if you have more than 2 entries of numbers even though they're entered as '1, '2. As soon as you enter a third such value in the tbl, the next value repeats in the distinct list from that point to the end of the list array. If that can be solved, that is exactly what I need.

Thank you!!

=INDEX(tbl, SMALL(IF(SMALL(IF(COUNTIF($B$7:B7, tbl)+ISBLANK(tbl)=0, COUNTIF(tbl, "<"&tbl)+1, ""), 1)=IF(ISBLANK(tbl), "", COUNTIF(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