## Excel 2007/2010 array formula: 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

### Category: Sorted unique distinct values

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

Comments(53) Filed in category: Excel, Sorted unique distinct values

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

Comments(24) Filed in category: Excel, Sorted unique distinct values

This is an answer to a question in this blog post: Extract a unique distinct list sorted from A-Z from […]

Comments(21) Filed in category: Excel, Sorted unique distinct values

Question: How do I create a new unique distinct list from a column. I also want the list sorted from large […]

Comments(15) Filed in category: Excel, Sorted unique distinct values

Table of Contents Filtering unique distinct text values and sort them based on sum of adjacent values Filtering unique distinct […]

Comments(10) Filed in category: Excel, Sorted unique distinct values

Question: How do I create a unique distinct list from a column sorted A to Z using array formula? Answer: […]

Comments(6) Filed in category: Excel, Sorted unique distinct values

Eddie asks: Find and Sort I have question, in range A1,A2,A3,A4 contain 097 494 861 575. What is the formula […]

Comments(6) Filed in category: Excel, Sorted unique distinct values

Sara asks: How can you use large with multiple criteria?? Example looking for top 5 of a list based on […]

Comments(3) Filed in category: Excel, Sorted unique distinct values

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

Comments(3) Filed in category: Excel, Sorted unique distinct values, Unique distinct values

### 11 Responses to “Excel 2007/2010 array formula: Filter unique distinct values, sorted and blanks removed”

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