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
Related posts:
Filter unique distinct list sorted based on sum of adjacent values using array formula in excel
Filter a column and create a new unique list sorted from A to Z using array formula in excel
Excel table: Filter unique distinct values (array formula)
Extract a unique distinct list sorted alphabetically removing blanks from a range in excel
Recommended categories
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," (http://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