## Sorting numbers and text cells in an descending order also removing blanks

*Article updated on February 05, 2018*

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

Sorting text cells using array formula in excel

Sorting numbers and text cells also removing blanks using array formula in excel

Sorting numbers and text cells descending also removing blanks using array formula in excel

Sort text cells alphabetically from two columns using excel array formula

Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula

Sort a range from A to Z using array formula in excel

In a previous post *Sorting numbers and text cells also removing blanks using **array formula in excel*

I created an array formula that sorted numbers and text ascending (Smallest to largest and A to Z). So how do I create a descending list? Z to A and largest to smallest.

Array formula in B2:B15:

=INDEX(List, MATCH(LARGE(IF(ISBLANK(List), "", IF(ISNUMBER(List), COUNTIF(List, "<"&List), COUNTIF(List, "<"&List)+SUM(IF(ISNUMBER(List), 1, 0))+1)), ROW()-ROW(sorted_list_start)+1), IF(ISBLANK(List), "", IF(ISNUMBER(List), COUNTIF(List, "<"&List), COUNTIF(List, "<"&List)+SUM(IF(ISNUMBER(List), 1, 0))+1)), 0)) + CTRL + SHIFT + ENTER

**Named ranges**

List (A2:A15)

sorted_list_start (B2)

What is named ranges?

**How to customize the formula to your excel workbook**

Change the named ranges.

**Download excel example fil****e**sort-numbers-and-text-cells-using-excel-array-formula_descending.xls

(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

**LARGE(**array,k**)** returns the k-th largest row 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 five articles on the same subject.*

Sorting text cells using array formula in excel

Sorting numbers and text cells also removing blanks using array formula in excel

Sorting numbers and text cells descending also removing blanks using array formula in excel

Sort text cells alphabetically from two columns using excel array formula

Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula

Sort a range from A to Z using array formula in excel

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

Table of contents Create a unique distinct sorted list containing both numbers text removing blanks Create a unique distinct sorted […]Extract a unique distinct list sorted alphabetically and ignore 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 […]### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

## Share this article