More Blank Problems

[novice007]

I am using the following formula:


=IFERROR(INDEX($A$16:$A$21,SMALL(IF($A$16:$A$21<>"",ROW($A$16:$A$21)-ROW($A$16)+1,ROW($A$21)),ROW(A1))),"")


Which removes blank lines, but it is only when there is one blank in between rows. How can I alter this formula, to account for varying number of blanks, there could e.g.


ColA

Red

Blank Line

White

Green

Blank Line

Blank Line

Blank Line

Orange

 

[Hui]

Novice007


If your data is in A16:A23, not A21 as your formula says

this should work:

=IFERROR(INDEX($A$16:$A$23,SMALL(IF($A$16:$A$23<>"",ROW($A$16:$A$23)-ROW($A$15)),ROW(A1))),"")

It as an array formula so needs to be entered with Ctrl Shift Enter


Now copy the cell

and paste it down below the first cell, not over the top of it though.


To understand how this works you may want to read: http://chandoo.org/wp/2012/03/01/formula-forensic-014/

which is a similar problem but uses Offset instead of Index

 

[NARAYANK991]

Hi ,


The formula you have posted is correct ; as Hui has mentioned , if it is entered as an array formula , it should work , even if there are multiple blank rows in between.


Narayan

 

[novice007]

Thanks guys. The formula worked perfectly; it was operator error which caused it

to fail initially.

 

[Montrey]

interesting formula. I'm going to have to dive into this one. I still havent quite exactly wrapped my head around the row function yet and how to use it properly.