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

Ced

Answer in previous post: https://mcmap.net/q/1160727/-fill-non-contiguous-blank-cells-with-the-value-from-the-cell-above-the-first-blank/657668 – Treasurehouse

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.

Catchpole

I use this method all the time. Really comes in handy – Juba

Thank you caffeinatedcoder! :) – Catchpole

that was easy! The Staples commercial should reference this solution :) – Parimutuel

You have saved me hours of work. Thank you very much! – Manteau

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.

Blockhead

Answer 3

This is what I once wrote:

Sub FillInBlankCellsInColumns()
 'Go down and when you find an empty cell, put the value from the previous cell in there.
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Dim MyCounter As Long
MyCounter = 0
    For Each r In Selection
        On Error Resume Next
        If r.Value = "" Then
            r.Value = r.Offset(-1, 0).Value
        End If
    Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Linage

(1) I suggest it is best to store calculation mode at the start and restore at the end (ie if user has Manual or SemiAutomatic this apporach will change it). (2) Cell loops are much slower than SpecialCells for this task – Kissner

Answer 4

Debra Dalgleish provides both the

• manual SpecialCells Method (that Excellll refers to)
• the VBA SpecialCells method which seems to be more your area of focs

at her site under Excel Data Entry -- Fill Blank Cells in Excel Column

Kissner

This is the correct solution for this problem. It worked perfectly. – Insatiable

Answer 5

Alternatively, you could use the following formula in a new column:

D2: =IF(ISBLANK(C2),D1,C2)

• Create a new column next to C.
• Previous column D will now be named column E. Your data is in column C, the new data is temporarily created in column D.
• The first value from C1 must be copied manually to D1
• Then in D2 you put this formula. Copy the formula all the way down.
• Then copy column D and use Paste-Special Values Only to paste the results of column D over the existing data in column C.
• Now you can remove column D

This is a little more "work" but since you state that you are not experienced in Excel, writing a macro could be challenging.

Regards,

Robert Ilbrink

Linage

Answer 6

use Vlookup

=+VLOOKUP("Output",D1:G7,1)

• Replace D1 with the address of the starting cell to lookup
• Replace G7 with the address of the Ending cell to lookup
• Replace 1 with the column you want the value to be returned from

    Apple   1       Apple
            2       Apple
            7       Apple
    Mango   3       Mango
    =====================
    col1    Col2    Formula

Patience

Could you please explain properly what is output and how its work – Diatonic