How can you tell a spreadsheet to replace blank cells with the data from cell immediately above?

[dbwiz]

I have a spreadsheet where Column A contains group numbers and Column B contains names. The other columns show dates and payments. Occasionally the same group number and name pays for multiple dates, and unfortunately, only shows on the first row. We are manually going down the list and copy/pasting the group number and name from the row right above it in order to be able to sort by date or amount without losing the name - but I am sure you guys have a much better way of doing this - perhaps as a macro?

As always any help is greatly appreciated.

 

[jmthompson]

Is there a column that always has data?

 

[Norie]

1. Select the column with the blanks.
2. Goto Edit>Goto...Special and select blanks.
3. Goto the formula bar, enter =A1 (or whatever the first cell with data is) and then press CTRL+ENTER.
4. Select the column, copy and paste special values.

 

[Domski]

Maybe try this:

- Select your whole range of data

- Press F5 and click on Special and then Blanks.

- Type = and then press the up cursor once and a formula should appear in your first blank cell referencing the complete cell above it.

- Press ctrl+enter

- Select the whole list and Copy...paste special...values

Hope it helps,

Dom

 

[hatepoppy]

I have a spreadsheet where Column A contains group numbers and Column B contains names. The other columns show dates and payments. Occasionally the same group number and name pays for multiple dates, and unfortunately, only shows on the first row. We are manually going down the list and copy/pasting the group number and name from the row right above it in order to be able to sort by date or amount without losing the name - but I am sure you guys have a much better way of doing this - perhaps as a macro?

As always any help is greatly appreciated.

if all else fails, this is an easy macro. just specify the correct cells and thisll do'er.

Sub Replace_Blanks_With_Above_Value()
    For i = 1 To 10
 
        If Len(Cells(i, 10).Value) < 1 Then
 
            Cells(i, 10).Value = Cells(i - 1, 10).Value
 
        End If
 
    Next
End Sub

 

[jmthompson]

After re-reading your request, I have another question:
Will your blank cells always have a cell with data immediately above or are there situations where that information is in one row and then left off for several rows?