## Reverse a list ignoring blanks

The image above demonstrates a formula in cell D3 that rearranges values, bottom value is now on top etc.

Formula in C2:

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

### Explaining formula in cell D3

#### Step 1 - Find non-empty cells

$B$3:$B$14<>""

becomes

{"AA"; ""; "BB"; "CC"; ""; "BB"; "DD"; ""; "FF"; "GG"; ""; "HH"}<>""

and returns

{TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE}

#### Step 2 - Replace TRUE with corresponding row value

The IF function returns the row number if cell is not blank. FALSE returns "" (nothing).

IF($B$3:$B$14<>"", MATCH(ROW($B$3:$B$14), ROW($B$3:$B$14)), "")

becomes

IF({TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE}, MATCH(ROW($B$3:$B$14), ROW($B$3:$B$14)), "")

becomes

IF({TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE}, {1;2;3;4;5;6;7;8;9;10;11;12}, "")

and returns

{1;"";3;4;"";6;7;"";9;10;"";12}

#### Step 3 - Find k-th largest row number

The LARGE function extracts the k-th smallest number from cell range or array. LARGE(array. k) The second argument contains ROWS($D$1:D1), it has an expanding cell reference that grows when cell is copied to cells below.

LARGE(IF($B$3:$B$14<>"", MATCH(ROW($B$3:$B$14), ROW($B$3:$B$14)), ""), ROWS($D$1:D1))

becomes

LARGE({1;"";3;4;"";6;7;"";9;10;"";12}, ROWS($D$1:D1))

becomes

LARGE({1;"";3;4;"";6;7;"";9;10;"";12}, 1)

and returns 12.

#### Step 4 - Return value

The INDEX function returns a value based on a row and column number. In this formula the cell range is only in one column, the row number is only needed.

INDEX($B$3:$B$14, LARGE(IF($B$3:$B$14<>"", MATCH(ROW($B$3:$B$14), ROW($B$3:$B$14)), ""), ROWS($D$1:D1)))

becomes

INDEX($B$3:$B$14, 12)

and returns "HH" in cell D3.

### Download Excel *.xlsx file

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

Extract a unique distinct list sorted from A to Z ignore blanks

The image above demonstrates a formula in cell D3 that extracts unique distinct numbers and text values sorted from A […]

Sort dates within a date range

Array formula in D5: =SMALL(IF(($B$3:$B$12<=$E$3)*($B$3:$B$12>=$E$2), $B$3:$B$12, "A"), ROWS($A$1:A1)) How to create an array formula Copy array formula Select cell D5 […]

Lookup and return multiple sorted values based on corresponding values in another column

This article demonstrates a formula that extracts values based on a condition and sorts the returned values based on values […]

The image above shows a table with two columns in cell range B3:C16, it contains random text values in column […]

Sort column based on frequency

Question: How do I create a new unique distinct list from a column. I also want the list sorted from large […]

Sort text cells alphabetically from two columns

Table of Contents Sort text from two columns combined (array formula) Sort text from multiple cell ranges combined (user defined […]

Denisa asks: I have a problem and i cant figure it out, even if i'm seraching for 2 days. I […]

Sort values in an Excel table programmatically [VBA]

This article demonstrates how to sort a specific column in an Excel defined Table based on event code. The event […]

Sort a range from A to Z [Array formula]

Question: How do I sort a range alphabetically using excel array formula? Answer: Cell range $B$2:$E$5 contains text values in random […]

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

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form