Fill in Blank Cell from Cell Above

Objective is to fill all the blank cells in a column with the Data from the above cell.  Below images can explain it in a better way:
To solve this problem there can be many ways, hardest and very time consuming being copying and pasting data manually specially when you're dealing with large data.  We can write 'n' number of formula's to solve this, but I am going to tell a very simple and quick method of doing it.
Here we go, 
  • First select all the columns containing your data
  • Press Ctrl+G or F5 this will open Go To dialogue box

  • Press Special... and a new dialogue box will pop out
 
  • This will select all the blank cell in your data and Type "=" and then hit up arrow for example A4: =A3
  • Press Cltr+Enter to input that formula into all currently selected blank cells.
  And we are done in a flash.
This appears simple to me, Let me know what do you think.

22 comments:

  1. what if two cells are blank (like cell A4 and A5 are both blank)?

    ReplyDelete
  2. Ohh u knw wat, it works!! thanks man!!

    ReplyDelete
  3. This comment has been removed by a blog administrator.

    ReplyDelete
  4. Awsome prakash. Thanks a lot... Keep bugging us...

    ReplyDelete
  5. Ohh thats great. Thanks for the help..

    ReplyDelete
  6. Its really good..wats next TEACHER JI

    ReplyDelete
  7. @Babita: Working on something will post sometime in this week!

    ReplyDelete
  8. Hi Prakash ...Can we include a check to enable this process. ... and more over can i use Range as well...
    Thanks in Advance

    ReplyDelete
  9. @Manish: You can use range in this method for sure. I am little bit confused about adding a check think. Can you explain what do you mean by that?

    ReplyDelete
  10. Prakash, you are genius. Cheers!!
    Can you help me inserting a blank row in two rows containing data? For e.g. Row 1 has information about a hospital and row 2 also has information about another hospital and so on and I want to insert a blank row in each set of 2 rows containing data.

    ReplyDelete
  11. @Sandeep: The solution will go like this...

    1. First, you need a blank column adjacent to your data. For instance, if data is in column A to Z so take column AA

    2. Enter 1 in the first cell adjacent to the data and 2 below it.

    3. Select these 1 & 2 and double click to auto-fill till the end of your data.

    4. Now copy the values entered in step 3 and go the first blank cell in this column AA and paste what you copied from above.

    5. Click Sort Ascending to sort the spreadsheet by the auto-fill values in column AA and when asked for expand the selection.

    6. Delete the column AA and you are done.

    ReplyDelete
  12. Hi Prakash: Regarding adding the check ...i mean based on value in the cell, can this method be automated to do the following action ???

    ReplyDelete
  13. @Manish: Really sorry man I'm not able to get through your point. Can you send me an example on gusainprakash@gmail.com? Hope it will be learning step for both of us!

    ReplyDelete
  14. Just worth noting that this technique also works with pretty much anything you want to enter. So you can enter text, numbers, formulas pointing left/right etc - and by varying whether they are absolute (eg =$A$3) or relative (eg =A3) you can be quite flexible with your output.

    ReplyDelete
  15. @Prakash,

    A very common problem encountered when extracting data from reports with headers and the need to normalize data into database record format for upload to ODBC compliant database

    Here is a brief snippet I use to fill missing data as I step through extracted data in the "cleansing" or normalization process.

    [code]
    'Fill
    For Each C In myRange
    If C.Value = 0 Then
    C.Value = myValue
    Else
    myValue = C.Value
    End If
    Next C
    [/code]

    ReplyDelete
  16. Hi..Sir. wishes for the day. and Very Very Thanks to you sir for yours valuable idea

    ReplyDelete
  17. I was shown this method a bout a year ago and use it every chance get and it would be my colder too solve this quandary. Before I knew of this great function, and for variety sake : Control H, find and replace, leave find field null, enter integer into replace (1). Insert column, if (A2=1, A1, A2), fill formula down to last record. Copy column B, paste values onto column A

    ReplyDelete
  18. Hi, great tip. May I suggest one aditional final step of pasting values the whole column, otherwise if you sort the data the values may change and then be incorrect.

    ReplyDelete
  19. Thank you guys for some amazing tips via comments!

    Lot of people will get benefit out of it.

    ReplyDelete
  20. @Anonymous great code. It also fills to the right if you select a range greater than one column...I modified your code as follows so user can select a range:

    Sub FillBlankswithcellAboveandtoLeft()
    'This code fills any blanks with what is immediately above and to left
    Dim C As Range
    Dim MyRange As Range
    Dim MyValue As Variant

    On Error Resume Next
    Application.DisplayAlerts = False
    Set MyRange = Application.InputBox(Prompt:= _
    "Select the range you want to step through and fill blanks with cell immediately above", _
    Title:="Specify Range now...", Type:=8)
    On Error GoTo 0
    Application.DisplayAlerts = True
    If MyRange Is Nothing Then
    Exit Sub
    Else
    MyRange.Select
    For Each C In MyRange
    If C.Value = 0 Then
    C.Value = MyValue
    Else
    MyValue = C.Value
    End If
    Next C
    End If
    End Sub

    John

    ReplyDelete
  21. @John Nicely done man! Thanks for sharing...

    ReplyDelete