New Column that removes blanks of previous column (FORMULA ONLY)

Question

Is this possible using formulas?

A1 has Apple in it. A5 has banana in it. A9 has carrot. There are spaces between A2-A4, A6-A8. I want a new column (Starting at B1) to fill those blanks with val above. I have tried using offset and row numbers but can’t get it working so far.

Excel sheet link

Column A or 1 represents what my data looks like in image/excel

Column B represents what I want it to do in image/excel

Answer 1

In Cell B1 enter following formula

=A1

Then in Cell B2 enter the following formula

=IF(A2="",B1,A2)

Drag/Copy down as required. See image for reference.

This can also be done without using formula. See this.