Removing the Blanks

[novice007]

If I have a column i.e.


ColA

Apples

Bananas

blank cell

Oranges

blank cell

lemon


What formula can I use to get output in ColB, i.e. a continuous list of values from Cola without the blank cells in between.)

ColB

Apples

Bananas

Oranges

lemon

 

[NARAYANK991]

Hi ,


Try 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))),"")


entered as an array formula (using CTRL SHIFT ENTER ).


Here , I am assuming your data is from A16 through A21. The above formula is to be entered in any cell in row 16 i.e. B16 or C16 or D16 ... Copy this formula down. The first cells in the column will be filled with the valid values , and the remaining cells will be blank.


Narayan

 

[novice007]

Thanks Narayan, I'll give it a go.

 

[bobhc]

Good day novice007


I have used this before works , works well...http://chandoo.org/wp/2010/01/26/delete-blank-rows-excel/

 

[novice007]

Thanks Nararank991, and you were right bobhc, it works perfectly. I wouldn't have been able to figure it out for myself. I'd already beaten my head against a wall.