Fill sparse rows in Excel

I have an Excel sheet which is sparsely filled:

I'm looking for a way to turn the left column into the right column. Basically all empty rows should be set to the first filled row above them.

Is there a way to do this? The sheet is awfully large (60.000 rows.)

-
How do you determine when the last item should stop? – DMA57361 Sep 21 '10 at 13:33
@DMA57361: There's never more than 10 rows, so it could stop after 10 – Andomar Sep 21 '10 at 13:39
Related: superuser.com/questions/151057/… – Kevin Vermeer Aug 9 '11 at 21:03

Assuming that the first "a" is in cell A3, you can use the following formula in C3:

=IF(A3="",C2,A3)

and drag that down column C as far as you want.

-
Actually used `IF(A3<>"";A3;B2)`, but this put me on the right track! Many thanks. – Andomar Sep 21 '10 at 13:43
Are you sure about that? Looks like your logic is backwards (<>"" means that the cell is not empty). Also, the parts of an IF statement in an Excel spreadsheet have to be delimited with commas rather than semicolons. – Alex Sep 21 '10 at 14:16
The function parameter separator is language dependent. Its a semicolon in German, e.g. since the comma is the decimal separator here. – Jens Sep 21 '10 at 14:19
@Jens: You learn something new every day! Thanks. – Alex Sep 21 '10 at 14:26
Your formula says, copy from above if it's empty, my formula says, copy from left if it's not empty. More or less the same! Jens is right about the semicolon, it's `;` in the Dutch version of Excel – Andomar Sep 21 '10 at 15:01

Here's another way. If you're first "a" is in A4, select A4 down to wherever you want to stop. Press F5, then Special, then Blanks. Type "=A4" and press Control+Enter. Now reselect A4:A? and Edit - Copy. Then Edit - Paste Special - Values.

-