Table of contents

  1. Create a unique distinct sorted list containing both numbers text removing blanks
  2. Create a unique distinct sorted list containing both numbers text removing blanks with a condition

Create a unique distinct sorted list containing both numbers text removing blanks

Array formula in cell B2:

=INDEX(List, MATCH(MIN(IF(ISBLANK(List)+COUNTIF(B1:$B$1, List), "", IF(ISNUMBER(List), COUNTIF(List, "<"&List), COUNTIF(List, "<"&List)+SUM(IF(ISNUMBER(List), 1, 0))+1))), IF(ISBLANK(List)+COUNTIF(B1:$B$1, List), "", IF(ISNUMBER(List), COUNTIF(List, "<"&List), COUNTIF(List, "<"&List)+SUM(IF(ISNUMBER(List), 1, 0))+1)), 0))


How to create an array formula

  1. Copy above array formula
  2. Select cell B2
  3. Click in formula bar
  4. Paste formula (Ctrl + v)
  5. Press and hold Ctrl + Shift
  6. Press Enter

How to copy array formula

Copy cell B2 and paste it down as far as needed.

Named range

List ($A$2:$A$15)
What is a named range?

Download excel sample file for this tutorial.

Unique and Sort-numbers-and-text-cells-using-excel-array-formula.xls
(Excel 97-2003 Workbook *.xls)

Create a unique distinct sorted list containing both numbers text removing blanks with a condition

Array formula in cell F4:

=IFERROR(INDEX(Table1[Values], MATCH(SMALL(IF(Table1[Condition]=$F$2, IF(ISBLANK(Table1[Values]), "", IF(COUNTIF($F$3:F3, Table1[Values])=0, IF(ISNUMBER(Table1[Values]), COUNTIF(Table1[Values], "<"&Table1[Values]), COUNTIF(Table1[Values], "<"&Table1[Values])+SUM(1*ISNUMBER(Table1[Values]))+1), "")), ""), 1), IF(Table1[Condition]=$F$2, IF(ISBLANK(Table1[Values]), "", IF(COUNTIF($F$3:F3, Table1[Values])=0, IF(ISNUMBER(Table1[Values]), COUNTIF(Table1[Values], "<"&Table1[Values]), COUNTIF(Table1[Values], "<"&Table1[Values])+SUM(1*ISNUMBER(Table1[Values]))+1), "")), ""), 0)), "")

How to create an array formula

How to copy array formula in cell F4

  1. Select cell F4
  2. Copy (Ctrl + c)
  3. Select cell range F5:F10
  4. Paste (Ctrl + v)

Download excel *.xlsx file

unique-list-sorted-alphabetically-with-a-condition1.xlsx

Functions in this article:

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

ROW(reference) returns the rownumber of a reference

MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value

ISNUMBER(value)
Checks whether a value is a number and returns TRUE or FALSE