## Extract unique distinct values A to Z from a range and ignore blanks

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
- Press with left mouse button on "Special..."
- Press with left mouse button on "Blanks"
- Press with left mouse button on 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:**

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

Then copy cell B8 and paste it down as far as necessary.

**How to implement array formula to your workbook**

If your list starts at, for example, F3. Change $B$7:B7 in the above formulas to F2:$F$2.

### Explaining array formula in cell B8

#### Step 1 - Count previous values are ignored

The COUNTIF function counts cells based on a condition, however, in this case, I am using it to check that no duplicates are returned.

COUNTIF($B$7:B7,$B$2:$E$5)

returns {0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0}.

The array above contains only 0's (zeros), this means that no value has yet been shown. Cell range $B$7:B7 expands as the formula is copied to cells below, this makes sure that all previous values are checked.

#### Step 2 - Identify blank cells

The ISBLANK function returns TRUE if cell is empty and FALSE if it contains at least one character.

ISBLANK($B$2:$E$5)

returns {FALSE, TRUE, TRUE, TRUE; TRUE, FALSE, FALSE, FALSE; TRUE, FALSE, TRUE, FALSE; FALSE, FALSE, FALSE, FALSE}.

The image above shows the array entered in cell range B7:E10, boolean values TRUE corresponds to the empty values in cell range B2:E5.

#### Step 3 - Add arrays and compare to zero

COUNTIF($B$7:B9, $B$2:$E$5)+ISBLANK($B$2:$E$5)=0

becomes

{0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0}+{FALSE, TRUE, TRUE, TRUE; TRUE, FALSE, FALSE, FALSE; TRUE, FALSE, TRUE, FALSE; FALSE, FALSE, FALSE, FALSE}=0

becomes

{0,1,1,1;1,0,0,0;1,0,1,0;0,0,0,0}=0

and returns

{TRUE,FALSE,FALSE,FALSE;FALSE,TRUE,TRUE,TRUE;FALSE,TRUE,FALSE,TRUE;TRUE,TRUE,TRUE,TRUE}

The array above keeps track of blank cells and prior values.

#### Step 4 - Replace boolean values with numbers based on their relative position if they were sorted

IF(COUNTIF($B$7:B7,$B$2:$E$5)+ISBLANK($B$2:$E$5)=0,COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1,"")

becomes

IF({TRUE,FALSE, FALSE,FALSE;FALSE,TRUE, TRUE,TRUE;FALSE,TRUE, FALSE,TRUE;TRUE,TRUE, TRUE,TRUE},{3,1,1,1;1,10,1,8;1,7,1,3;9,5,6,2},"")

and returns

{3,"","","";"",10,1,8;"",7,"",3;9,5,6,2}

#### Step 5 - Get smallest value in array

SMALL(IF(SMALL(IF(COUNTIF($B$7:B9, $B$2:$E$5)+ISBLANK($B$2:$E$5)=0, COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1, ""), 1)

becomes

SMALL({3,"","","";"",10,1,8;"",7,"",3;9,5,6,2}, 1)

and returns 1.

#### Step 6 - Replace smallest value with row number

IF(SMALL(IF(COUNTIF($B$7:B7, $B$2:$E$5)+ISBLANK($B$2:$E$5)=0, COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1, ""), 1)=IF(ISBLANK($B$2:$E$5), "", COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)

becomes

IF(1=IF(ISBLANK($B$2:$E$5), "", COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)

becomes

IF(1={3, "", "", "";"", 10, 1, 8;"", 7, "", 3;9, 5, 6, 2}, ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)

becomes

IF({FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, TRUE, FALSE;FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE}, ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)

becomes

=IF({FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, TRUE, FALSE;FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE}, {1;2;3;4})

and returns

{FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, 2, FALSE;FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE}

#### Step 7 -Get smallest value

SMALL(IF(SMALL(IF(COUNTIF($B$7:B7,$B$2:$E$5)+ISBLANK($B$2:$E$5)=0,COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1,""),1)=IF(ISBLANK($B$2:$E$5),"",COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1),ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1),1)

becomes

SMALL({FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, 2, FALSE;FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE},1)

and returns 2. This is the row number we need to get the correct value.

#### Step 8 - Extract smallest value

The following steps calculate the column number needed to get the correct value.

MIN(IF(COUNTIF($B$7:B7,$B$2:$E$5)+ISBLANK($B$2:$E$5)>0,"",COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1))

becomes

MIN({3,"","","";"",10,1,8;"",7,"",3;9,5,6,2})

and returns 1.

#### Step 9 - Extract array from the correct row

Get array needed in the first argument in the INDEx function.

INDEX(IF(ISBLANK($B$2:$E$5),"",COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1),SMALL(IF(SMALL(IF(COUNTIF($B$7:B7,$B$2:$E$5)+ISBLANK($B$2:$E$5)=0,COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1,""),1)=IF(ISBLANK($B$2:$E$5),"",COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1),ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1),1),,1)

becomes

INDEX({3, "", "", "";"", 10, 1, 8;"", 7, "", 3;9, 5, 6, 2},SMALL(IF(SMALL(IF(COUNTIF($B$7:B7,$B$2:$E$5)+ISBLANK($B$2:$E$5)=0,COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1,""),1)=IF(ISBLANK($B$2:$E$5),"",COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1),ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1),1),,1)

The following calculation returns the row number, I have already shown that calculation in steps 1 to 7.

INDEX({3, "", "", "";"", 10, 1, 8;"", 7, "", 3;9, 5, 6, 2},SMALL(IF(SMALL(IF(COUNTIF($B$7:B7,$B$2:$E$5)+ISBLANK($B$2:$E$5)=0,COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1,""),1)=IF(ISBLANK($B$2:$E$5),"",COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1),ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1),1),,1)

becomes

INDEX({3, "", "", "";"", 10, 1, 8;"", 7, "", 3;9, 5, 6, 2},2,,1)

and returns {"", 10, 1, 8}

#### Step 10 - Calculate relative column number

MATCH(MIN(IF(COUNTIF($B$7:B7,$B$2:$E$5)+ISBLANK($B$2:$E$5)>0,"",COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1)),INDEX(IF(ISBLANK($B$2:$E$5),"",COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1),SMALL(IF(SMALL(IF(COUNTIF($B$7:B7,$B$2:$E$5)+ISBLANK($B$2:$E$5)=0,COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1,""),1)=IF(ISBLANK($B$2:$E$5),"",COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1),ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1),1),,1),0)

becomes

MATCH(1,{"", 10, 1, 8},0)

and returns 3. This is the column number needed to get the correct value.

#### Step 11 - Get value

=INDEX($B$2:$E$5,SMALL(IF(SMALL(IF(COUNTIF($B$7:B7,$B$2:$E$5)+ISBLANK($B$2:$E$5)=0,COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1,""),1)=IF(ISBLANK($B$2:$E$5),"",COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1),ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1),1),MATCH(MIN(IF(COUNTIF($B$7:B7,$B$2:$E$5)+ISBLANK($B$2:$E$5)>0,"",COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1)),INDEX(IF(ISBLANK($B$2:$E$5),"",COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1),SMALL(IF(SMALL(IF(COUNTIF($B$7:B7,$B$2:$E$5)+ISBLANK($B$2:$E$5)=0,COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1,""),1)=IF(ISBLANK($B$2:$E$5),"",COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1),ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1),1),,1),0),1)

becomes

=INDEX($B$2:$E$5,2,3)

and returns "Apple" in cell B8.

**Get excel *.xls file**

extract-a-unique-distinct-list-sorted-alphabetically-removing-blanks-from-a-range-4.xls

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: Cell range $B$2:$E$5 contains text values in random […]

Sort a range from A to Z [Array formula]

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

Extract unique distinct values from a multi-column cell 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

The image above shows an array formula in cell B8 that extracts unique distinct values sorted alphabetically from cell range […]

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

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

Extract duplicates from a range

The following array formula in cell B11 extracts duplicates from cell range B3:E8, only one instance of each duplicate is […]

Extract duplicates from a range

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

The formula in cell B8 extracts a list sorted based on frequency. Array formula in B8: =TEXTJOIN("", TRUE, IF(MIN(IF((MAX(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, […]

First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]

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

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

### 23 Responses to “Extract unique distinct values A to Z from a range and ignore blanks”

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

Paste image link to your comment.

**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) Get the 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