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

*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

In this blog post I will provide two solutions on how to remove blank cells and a solution on how […]

Merge two columns with possible blank cells

Question: This article is terrific. Thanks so much for posting this solution! I do have one question: Let's say my […]

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 […]

How to use the ISBLANK function

The ISBLANK function returns TRUE if the argument is an empty cell, returns FALSE if not. Excel Function Syntax ISBLANK(value) […]

This blog post shows you how to create a conditional formatting formula and highlight matching records. You can easily change […]

The above image demonstrates how to ignore blank cells in a SUMPRODUCT formula. The followingÂ formula is shown in cell E3. […]

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form