## Filter unique distinct values, sorted and blanks removed

**Table of Contents**

- Filter unique distinct values, sorted and blanks removed
- Filter duplicate values, sorted and blanks removed

*I am looking for the same formula on this page, but targeting a range of MxN (spanning multiple columns) not just a list in one column.*

*I see where you have formulas which act on MxN but not with all the features of this one:*

*1. Unique and distinct*

*2. Remove blanks*

*3. Sort*

*4. Properly handle numbers and text*

*And just to ask for the 'frosting on top' remove errors.*

**Answer:**

The array formula described in this post:

- Filters unique distinct values
- Returns values sorted
- Handles numbers and text together
- Removes blank cells
- Removes errors

**Excel 2007/2010 array formula in cell B8:**

**Named range**

tbl : B2:E5

How to create a named range

- Select cell range B2:E5
- Type
*tbl*in name box - Press Enter

**How to create an array formula**

- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
- Press and hold Ctrl + Shift.
- Press Enter once.
- Release all keys.

**Download excel file**

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

(Excel 2007 Workbook *.xlsx)

### Excel 2007/2010 array formula: Filter duplicate values, sorted and blanks removed

Array formula in cell B8:

### Download excel file

extract-a-duplicates-list-sorted-alphabetically-from-a-range-removing-blanks.xlsx

(Excel 2007 Workbook *.xlsx)

**Recommended blog posts**

Learn more about **sorting** unique distinct values. Read these blog posts:

- Extract a unique distinct list sorted from A-Z from range in excel
- Sort a range by occurence using array formula in excel
- Create a unique list and sort by occurrances from large to small

Learn more about **filtering** and **comparing** unique distinct records. Read these blog posts:

- Filter unique distinct records in excel 2007
- Compare two lists of data: Highlight common records in excel
- Compare two lists of data: Filter records existing in only one list in excel
- Compare two lists of data: Filter common row records in excel
- Quickly compare two tables in excel 2007

Learn more about **counting unique distinct values and records**. Read these blog posts:

Read more about **custom functions** in excel

- Filter duplicate words from a cell range in excel (udf)
- Filter unique words from a range in excel (udf)
- Excel udf: Count unique distinct values in a large dataset
- Excel udf: Filter unique distinct values (case sensitive)
- Excel udf: Filter unique distinct records (case sensitive)

Want to learn more about **filtering unique distinct values**? You must read these blog posts:

- 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
- Unique values from multiple columns using array formulas
- 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

Count unique distinct text values in a range

Array formula in D14: =SUM(IF(FREQUENCY(COUNTIF(tbl, "<"&IF(ISTEXT(tbl), tbl, "")), COUNTIF(tbl, "<"&IF(ISTEXT(tbl), tbl, "")))>0, 1, 0)) + CTRL + SHIFT + ENTER […]

Create a unique distinct list where a corresponding column has text cell values

Question: I want a unique list to be created from a column where an adjacent column has text cell value? […]

Extract and sort text cells from a range containing both numerical and text values

Array formula in B16: =INDEX(tbl, MIN(IF(SMALL(IF(ISTEXT(tbl), COUNTIF(tbl, "<"&tbl)), ROW(1:1))=IF(ISTEXT(tbl), COUNTIF(tbl, "<"&tbl)), ROW(tbl)-MIN(ROW(tbl))+1)), MATCH(SMALL(IF(ISTEXT(tbl), COUNTIF(tbl, "<"&tbl)), ROW(1:1)), INDEX(IF(ISTEXT(tbl), COUNTIF(tbl, "<"&tbl)), MIN(IF(SMALL(IF(ISTEXT(tbl), […]

Return value if it exists more then n times

Students are allowed to sign up to maximum two courses. How do I filter students who have signed up for […]

Extract numbers and text from a range sorted from A to Z

This blog post describes how to filter text values and then numbers from a range. Array formula in B15: =INDEX(tbl, […]

The following formula in cell D3 counts cells with values stored as text. =SUMPRODUCT(ISTEXT(B3:B14)*1) In other words, cells containing nothing, errors, […]

The picture above shows different values in column B and a formula in column C that tries to identifies the […]

How to use the ISTEXT function

The ISTEXT function returns TRUE if value is text. Formula in cell C3: =ISTEXT(B3) Excel Function Syntax ISTEXT(value) Arguments value […]

Filter numbers or text in a column

Question: I want to extract all numeric values into a new column? Also all text values into another column? Answer: […]

### 11 Responses to “Filter unique distinct values, sorted and blanks removed”

### 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. Unbelievable. This has got to be one of the coolest formulas ever. Well, certainly that I've had the need to dream requirements for. Thank you so much!

And you might also want to add to your bullet list at the top of the Answer section that it handles numbers and text together. Some of your other formulas weren't for both. I'd have to guess that you won't get many more requests for enhancing this because it does everything I can imagine and is just outright awesome. You've just made my day.

BTW, I'm not a big fan of named ranges for my application, so I hard code the range. The way I set it up is with headers in row A, the formula in A2 and drag it down. I have my original data range in B2:D4. Here's all of that hard coded. It's nothing more than a search and replace of yours, so there's no functionality change, just a formatting change to avoid named ranges and rearrangement of where things are.

=IFERROR(SMALL(IF(($B$2:$D$4"")*(ISNUMBER($B$2:$D$4))*(COUNTIF($A$1:A1, $B$2:$D$4)=0), $B$2:$D$4, ""), 1), IFERROR(INDEX($B$2:$D$4, SMALL(IF(SMALL(IF((COUNTIF($A$1:A1, $B$2:$D$4)=0)*(ISTEXT($B$2:$D$4)), COUNTIF($B$2:$D$4, "<"&$B$2:$D$4)+1, ""), 1)=IF((COUNTIF($A$1:A1, $B$2:$D$4)=0)*(ISTEXT($B$2:$D$4)), COUNTIF($B$2:$D$4, "<"&$B$2:$D$4)+1, ""), ROW($B$2:$D$4)-MIN(ROW($B$2:$D$4))+1), 1), MATCH(SMALL(IF((COUNTIF($A$1:A1, $B$2:$D$4)=0)*(ISTEXT($B$2:$D$4)), COUNTIF($B$2:$D$4, "<"&$B$2:$D$4)+1, ""), 1), INDEX(IF((COUNTIF($A$1:A1, $B$2:$D$4)=0)*(ISTEXT($B$2:$D$4)), COUNTIF($B$2:$D$4, "<"&$B$2:$D$4)+1, ""), SMALL(IF(SMALL(IF((COUNTIF($A$1:A1, $B$2:$D$4)=0)*(ISTEXT($B$2:$D$4)), COUNTIF($B$2:$D$4, "<"&$B$2:$D$4)+1, ""), 1)=IF((COUNTIF($A$1:A1, $B$2:$D$4)=0)*(ISTEXT($B$2:$D$4)), COUNTIF($B$2:$D$4, "<"&$B$2:$D$4)+1, ""), ROW($B$2:$D$4)-MIN(ROW($B$2:$D$4))+1), 1), , 1), 0), 1), ""))

EEK,

You are welcome!

This is great. Do you have an example where the values are just on one column? Thanks!

JP,

I can´t find an example but I created a workbook. Check it out:

Unique-distinct-list-from-a-column-sorted-A-to-Z-blanks.xls

Hi guys,

i have question, what i must do if i want to have duplicates data.

i mean in sort list i want to see duplicates.

Can you help me plzzz

Goran,

read this post:

Sort a range from A to Z using array formula

Oscar,

Ty vm its helps :)

Oscar,

i read post but i have problem....what i must do or change in this formula to have duplicates data

=IFERROR(SMALL(IF((csh"")*(ISNUMBER(csh))*(COUNTIF($B$3:B19,csh)=0),csh,""),1),IFERROR(INDEX(csh,SMALL(IF(SMALL(IF((COUNTIF($B$3:B19,csh)=0)*(ISTEXT(csh)),COUNTIF(csh,"<"&csh)+1,""),1)=IF((COUNTIF($B$3:B19,csh)=0)*(ISTEXT(csh)),COUNTIF(csh,"<"&csh)+1,""),ROW(csh)-MIN(ROW(csh))+1),1),MATCH(SMALL(IF((COUNTIF($B$3:B19,csh)=0)*(ISTEXT(csh)),COUNTIF(csh,"<"&csh)+1,""),1),INDEX(IF((COUNTIF($B$3:B19,csh)=0)*(ISTEXT(csh)),COUNTIF(csh,"<"&csh)+1,""),SMALL(IF(SMALL(IF((COUNTIF($B$3:B19,csh)=0)*(ISTEXT(csh)),COUNTIF(csh,"<"&csh)+1,""),1)=IF((COUNTIF($B$3:B19,csh)=0)*(ISTEXT(csh)),COUNTIF(csh,"<"&csh)+1,""),ROW(csh)-MIN(ROW(csh))+1),1),,1),0),1),""))

Goran,

read this:

Filter duplicate values, sorted and blanks removed (array formula)

Is there a version of this that works if the "tbl" is actually 3 different columns (non-consecutive)? I used the post "Extract a unique distinct list from three columns in excel," (https://www.get-digital-help.com/2009/06/20/extract-a-unique-distinct-list-from-three-columns-in-excel/) but that formula does not remove blanks, which I need. Also, sorting alphabetically is not necessary.

Thanks!

Ross,

I have added an array formula that removes blanks:

Extract a unique distinct list from three columns with possible blanks