Question: How do I sort text and numbers and also removing blanks using an array formula?

Array formula in B2:B15:

=INDEX(\$A\$2:\$A\$15, MATCH(SMALL(IF(ISBLANK(\$A\$2:\$A\$15), "", IF(ISNUMBER(\$A\$2:\$A\$15), COUNTIF(\$A\$2:\$A\$15, "<"&\$A\$2:\$A\$15), COUNTIF(\$A\$2:\$A\$15, "<"&\$A\$2:\$A\$15)+SUM(IF(ISNUMBER(\$A\$2:\$A\$15), 1, 0))+1)), ROW(A1)), IF(ISBLANK(\$A\$2:\$A\$15), "", IF(ISNUMBER(\$A\$2:\$A\$15), COUNTIF(\$A\$2:\$A\$15, "<"&\$A\$2:\$A\$15), COUNTIF(\$A\$2:\$A\$15, "<"&\$A\$2:\$A\$15)+SUM(IF(ISNUMBER(\$A\$2:\$A\$15), 1, 0))+1)), 0))

Recommended articles:

Filter a unique distinct list and remove blanks

Question: How do I create a unique distinct list from a list containing several blanks? Answer: Cell range B3:B12 contains several [โฆ]

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

Extract a unique distinct list sorted alphabetically removing blanks from a range

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

Sort a column alphabetically

Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I [โฆ]

Comments(81) Filed in category: Excel, Sort values

Create a unique distinct alphabetically sorted list extracted from a column

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, Unique distinct values

Extract a list of alphabetically sorted duplicates from a column

Question: How do I create a new list of alphabetically sorted duplicates using excel array formula? Answer: Excel array formula [โฆ]

Comments(1) Filed in category: Duplicate values, Excel, Sort values

How to enter an array formula

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

If you did this correctly, the array formula now begins with a { and ends with a }.

How to copy array formula

1. Select cell B2
2. Copy cell B2 (Ctrl + C)
3. Select cell range B3:B14
4. Paste (Ctrl + v)

(Excel 97-2003 Workbook *.xls)

Functions used in this blog post:

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

SMALL(array,k)
Returns the k-th smallest number in this data set.

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

This blog article is one out of six articles on the same subject.

Read more related articles