Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 3 down vote favorite

I have an Excel sheet which is sparsely filled:

alt text

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.)

share|improve this question
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

2 Answers

up vote 5 down vote accepted

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.

share|improve this answer
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
2  
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
1  
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
up vote 4 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.