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.
what if two cells are blank (like cell A4 and A5 are both blank)?
ReplyDeleteOhh u knw wat, it works!! thanks man!!
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteAwsome prakash. Thanks a lot... Keep bugging us...
ReplyDeleteOhh thats great. Thanks for the help..
ReplyDeleteIts really good..wats next TEACHER JI
ReplyDeleteCool. It makes work easy.
ReplyDelete@Babita: Working on something will post sometime in this week!
ReplyDeleteHi Prakash ...Can we include a check to enable this process. ... and more over can i use Range as well...
ReplyDeleteThanks in Advance
@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?
ReplyDeletePrakash, you are genius. Cheers!!
ReplyDeleteCan 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.
@Sandeep: The solution will go like this...
ReplyDelete1. 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.
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@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!
ReplyDeleteJust 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@Prakash,
ReplyDeleteA 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]
Hi..Sir. wishes for the day. and Very Very Thanks to you sir for yours valuable idea
ReplyDeleteI 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
ReplyDeleteHi, 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.
ReplyDeleteThank you guys for some amazing tips via comments!
ReplyDeleteLot of people will get benefit out of it.
@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:
ReplyDeleteSub 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
@John Nicely done man! Thanks for sharing...
ReplyDelete