Fill the disjoint empty cells with the value from the cell above the first space

Question

I want to know if there is a way to do this conditionally 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; I have very little experience with Excel.

c 1 | 11/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

• Select the range containing the empty cells you want to fill.

• Click Home> Find & Select> Go To Special ... and the Go To Special dialog box will appear, and then select the Forms check box.

• Click OK and all empty cells have been selected. Then enter the formula "= B2" into the active cell B3 without changing the selection. This cell reference can be changed as needed.

• Press Ctrl + Enter, Excel will copy the corresponding formula to all empty cells.

• At this point, the filled content is a formula, and we need to convert the formulas to values. Then select the entire range, right-click to select Copy, and then press Ctrl + Alt + V to activate the Paste Special ... dialog box. And select the “Values” option from “Paste” and select “None” from “Operations”.

• Then click OK. And all the formulas were converted to values.

Answer 2

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 

Answer 3

In Excel:

If you have a table in which someone merged cells or copied a table from the html site, where there may be many inconsistencies in the copied data, such as empty lines, which really should have the same value as the previous row to fix this , go to the empty column and create the following formula. This assumes that your heading 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 empty, if it is IS, it assigns the value A1, if it is NOT, it assigns the value A2.


However, if there are several blank lines that need to be filled, it is better to do the following:
(suppose the cell whose value you want to duplicate in all subsequent empty cells starts with A2, you are ready to set the first two cells in the next column manually, column I: I am the first empty column in your table)

Step one: in cells I2 and I3, make sure that the corresponding values ​​are present for the corresponding rows.
Step Two: In cell I4, enter the following formula:

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

The above function checks if A4 is free, if it is IS, it checks if A3 is not empty, if it is really NOT, it assigns the value A3, if it is too empty, it assigns the value in I3, finally if A4 was not empty, he assigns the value A4.

You will have to drag the formula manually (since there are blank lines).

Any empty row that follows data that you do not want should be easily identified by sorting the column by key field.

As stated above, you are dealing with formula values, so you cannot just move them overwriting the original cells, but if you do a “special” insert, you can paste the values ​​directly above your original cells.

Answer 4

Debra Dalgleish provides both

• Manual SpecialCells Method (which refers to Excellll)
• The VBA SpecialCells method, which seems to be more relevant to your focs area.

on your site under Excel Data Entry - filling in blank cells in an Excel column

Answer 5

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

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

• Create a new column next to C.
• The previous column D will now be called 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 only Paste-Special. Only values paste the results of column D on top of the existing data in column C.
• Now you can delete column D

This is a bit more “work”, but since you claim that Excel is not experienced, writing a macro can be difficult.

Hello,

Robert Ilbrink

Answer 6

use vlookup

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

• Replace D1 with the address of the starting cell to search for
• Replace G7 with the address of the Ending cell to search
• Replace with 1 column in which you want to return the value from

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