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

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)

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

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