Indexing function from bottom up

[4thgenlude]

Hi I have some data & some blanks in cells B31 through B38. Those cells get updated based on formulas that sometimes return blank.

I need to clear out the blank spaces and put them all in order in Cells B41 to B48.

I've used this formula to accomplish that:

=IFERROR(INDEX(B$31:B$38,SMALL(IF(B$31:B$38<>"",ROW(B$31:B$38)-ROW(B$31)+1),ROWS(B$41:B41))),"")

I that formula is in B41 and entered with Ctrl + Shift + Enter. It enters the data from B31:B38 in order from the top down, and leaves the bottom rows blank if there is space.

I am hoping there is a way to shift the blank rows to the top, while still preserving the original order.

For example, if there are two blanks in B31:B38, then I would like B41 & B42 to be blank, followed by the data points.

Anyone know how I can accomplish that?

[XLent]

One option, if I've understood...

Please Login or Register  to view this content.

use of inner INDEX in Small to negate need for Array entry -- no more efficient, however.

[Glenn Kennedy]

or:

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$31:$B$38)/($B$31:$B$38<>""),ROWS(B$41:B41))),"")

copied down. Since you have Excel 2010+ you can take advantage of AGGREGATE and avoid the use of arrays, too.

[Glenn Kennedy]

Ooops. I put the blanks to the bottom...

[Glenn Kennedy]

The AGGREGATE version of Xlents solution (blanks first) is:
=IF(ROWS(F$41:F41)<=COUNTBLANK($B$31:$B$38),"",INDEX(B:B,AGGREGATE(15,6,ROW($B$31:$B$38)/($B$31:$B$38<>""),ROWS(F$41:F41)-COUNTBLANK($B$31:$B$38))))

It's a bit longer than Xlents... and no more/less efficient, so go with his.