Table of Contents


EEK asks:

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:

=IFERROR(SMALL(IF((tbl<>"")*(ISNUMBER(tbl))*(COUNTIF($B$7:B7, tbl)=0), tbl, ""), 1), IFERROR(INDEX(tbl, SMALL(IF(SMALL(IF((COUNTIF($B$7:B7, tbl)=0)*(ISTEXT(tbl)), COUNTIF(tbl, "<"&tbl)+1, ""), 1)=IF((COUNTIF($B$7:B7, tbl)=0)*(ISTEXT(tbl)), COUNTIF(tbl, "<"&tbl)+1, ""), ROW(tbl)-MIN(ROW(tbl))+1), 1), MATCH(SMALL(IF((COUNTIF($B$7:B7, tbl)=0)*(ISTEXT(tbl)), COUNTIF(tbl, "<"&tbl)+1, ""), 1), INDEX(IF((COUNTIF($B$7:B7, tbl)=0)*(ISTEXT(tbl)), COUNTIF(tbl, "<"&tbl)+1, ""), SMALL(IF(SMALL(IF((COUNTIF($B$7:B7, tbl)=0)*(ISTEXT(tbl)), COUNTIF(tbl, "<"&tbl)+1, ""), 1)=IF((COUNTIF($B$7:B7, tbl)=0)*(ISTEXT(tbl)), COUNTIF(tbl, "<"&tbl)+1, ""), ROW(tbl)-MIN(ROW(tbl))+1), 1), , 1), 0), 1), ""))

Named range

tbl : B2:E5

How to create a named range

  1. Select cell range B2:E5
  2. Type tbl in name box
  3. Press Enter

How to create an array formula

  1. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
  2. Press and hold Ctrl + Shift.
  3. Press Enter once.
  4. 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:

=IFERROR(SMALL(IF((tbl<>"")*(ISNUMBER(tbl))*(COUNTIF($B$7:B7, tbl)=0)*(COUNTIF(tbl, tbl)>1), tbl, ""), 1), IFERROR(INDEX(tbl, SMALL(IF(SMALL(IF((COUNTIF($B$7:B7, tbl)=0)*(ISTEXT(tbl)*(COUNTIF(tbl, tbl)>1)), COUNTIF(tbl, "<"&tbl)+1, ""), 1)=IF((COUNTIF($B$7:B7, tbl)=0)*(ISTEXT(tbl)*(COUNTIF(tbl, tbl)>1)), COUNTIF(tbl, "<"&tbl)+1, ""), ROW(tbl)-MIN(ROW(tbl))+1), 1), MATCH(SMALL(IF((COUNTIF($B$7:B7, tbl)=0)*(ISTEXT(tbl)*(COUNTIF(tbl, tbl)>1)), COUNTIF(tbl, "<"&tbl)+1, ""), 1), INDEX(IF((COUNTIF($B$7:B7, tbl)=0)*(ISTEXT(tbl)*(COUNTIF(tbl, tbl)>1)), COUNTIF(tbl, "<"&tbl)+1, ""), SMALL(IF(SMALL(IF((COUNTIF($B$7:B7, tbl)=0)*(ISTEXT(tbl)*(COUNTIF(tbl, tbl)>1)), COUNTIF(tbl, "<"&tbl)+1, ""), 1)=IF((COUNTIF($B$7:B7, tbl)=0)*(ISTEXT(tbl)*(COUNTIF(tbl, tbl)>1)), COUNTIF(tbl, "<"&tbl)+1, ""), ROW(tbl)-MIN(ROW(tbl))+1), 1), , 1), 0), 1), ""))

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:

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

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

Read more about custom functions in excel