Fill non-contiguous blank cells with the value from the cell above the first blank [duplicate]

Question

I want to know if there is a way to do this conditional in excel or open office:

if the cell is empty then
    the cell will have the same value as the cell above it
else
    do nothing.

Should I use a macro for this? Please help me out; I have very little experience with Excel.

c 1 | 10/21/2011
c 2 |
c 3 |
c 4 | 10/24/2011
c 5 |
c 6 |
c 7 | 10/25/2011
c 8 |
c 9 |
c10| 10/26/2011

Answer 1

1. Select the range that contains blank cells you need to fill.

2. Click Home > Find & Select > Go To Special…, and a Go To Special dialog box will appear, then check Blanks option.

3. Click OK, and all of the blank cells have been selected. Then input the formula “=B2” into active cell B3 without changing the selection. This cell reference can be changed as you need.

4. Press Ctrl + Enter, Excel will copy the respective formula to all blank cells.

5. At this point, the filled contents are formulas, and we need to convert the formals to values. Then select the whole range, right-click to choose Copy, and then press Ctrl + Alt + V to active the Paste Special… dialog box. And select Values option from Paste, and select None option from Operation.

6. Then click OK. And all of the formulas have been converted to values.

Answer 2

In Excel:

If you have a table that someone has merged cells on or if you are copying a table from an html site where there can be many inconsistnacies in the copied data such as empty rows that really should have the same value as the previous row an easy way to fix this is go to an empty column and create the formula that follows.

This assumes that you have a header in A1 and A2 contains the first data and a valid row value.
If only every other cell is empty you can do the following:

= if(A2="",A1,A2)

The above function checks if A2 is blank, if it IS, it assigns the value of A1, if it is NOT, it assigns the value of A2.


However, if there are multiple empty rows that need to be filled it is better to do the following:
(assume cell whose value you want to duplicate in all subsequent empty cells begins at A2, you are willing to set the first two cells in the following column manually, column I:I is first empty column in your table)

Step One: In cells I2 and I3 ensure the proper vales are present for the associated rows.
Step Two: In cell I4 enter the following formula:

= if(A4="",if(A3<>"",A3,I3),A4)

The above function checks if A4 is blank, if it IS, it checks if A3 is NOT blank, if it indeed is NOT, it assigns the value of A3, if it too is blank, it assigns the value in I3, finally, if A4 was NOT blank, it assigns the value of A4.

You will have to drag the formula down manually (since there are empty rows).

Any empty row that follows data that you don't want should be easily identifiable by sorting the column by a key field.

As indicated by Robert above you are dealing with formula values so you can't just move them around, overwriting their source cells, but if you do a "special" paste you can paste the values directly over your source cells.