Macro to replace missing data from adjoining column

W

Woofy_McWoof_Woof

Board Regular
Joined
Oct 7, 2016
Messages
60
Office Version
  1. 365
Platform
  1. Windows
Hi, I'm working on a spread sheet that has 6 columns - Date, Half Hour, Actual Data and Date Half Hour and Estimated Data. There are occasions when the actual data is missing (blank cell), if this happens then I need to replace the Actual Data blank cell with the corresponding value from the Estimate Data cell. This would need to align with both the date and time in order for it to be synced correctly. There is an added complication in that the time for the actual data may not align with the half hour exactly (see example below where it is 07:42, in this case it would need to align to the half hour preceding the time i.e. 07:30.

In the example below I have copied in some data with the missing periods for the actual data, these would need to be replaced by the data in the estimated data column. I would also need to run a macro that would stop once it reaches the end of the data (there could be thousands of rows to search through).

I would appreciate any help with this as its driving me crazy. I could do it with a formula easily enough but it would take up too much memory and slow it all down (especially if I have a few years worth of data).

Thanks for your help :)

[TABLE="width: 698"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Time
[/TD]
[TD]Actual Data
[/TD]
[TD][/TD]
[TD]Date
[/TD]
[TD]Time
[/TD]
[TD]Estimated Data
[/TD]
[/TR]
[TR]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]00:30
[/TD]
[TD] 0.64
[/TD]
[TD][/TD]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]00:30
[/TD]
[TD] 0.64
[/TD]
[/TR]
[TR]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]01:00
[/TD]
[TD] 0.67
[/TD]
[TD][/TD]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]01:00
[/TD]
[TD] 0.67
[/TD]
[/TR]
[TR]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]01:30
[/TD]
[TD] 0.67
[/TD]
[TD][/TD]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]01:30
[/TD]
[TD] 0.67
[/TD]
[/TR]
[TR]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]02:00
[/TD]
[TD] 0.72
[/TD]
[TD][/TD]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]02:00
[/TD]
[TD] 0.72
[/TD]
[/TR]
[TR]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]02:30
[/TD]
[TD] 0.82
[/TD]
[TD][/TD]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]02:30
[/TD]
[TD] 0.82
[/TD]
[/TR]
[TR]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]03:00
[/TD]
[TD] 0.72
[/TD]
[TD][/TD]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]03:00
[/TD]
[TD] 0.72
[/TD]
[/TR]
[TR]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]03:30
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]03:30
[/TD]
[TD] 0.71
[/TD]
[/TR]
[TR]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]04:00
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]04:00
[/TD]
[TD] 0.71
[/TD]
[/TR]
[TR]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]04:30
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]04:30
[/TD]
[TD] 0.71
[/TD]
[/TR]
[TR]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]05:00
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]05:00
[/TD]
[TD] 0.71
[/TD]
[/TR]
[TR]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]05:30
[/TD]
[TD] 1.08
[/TD]
[TD][/TD]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]05:30
[/TD]
[TD] 1.08
[/TD]
[/TR]
[TR]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]06:00
[/TD]
[TD] 0.44
[/TD]
[TD][/TD]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]06:00
[/TD]
[TD] 0.44
[/TD]
[/TR]
[TR]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]06:30
[/TD]
[TD] 0.56
[/TD]
[TD][/TD]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]06:30
[/TD]
[TD] 0.56
[/TD]
[/TR]
[TR]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]07:00
[/TD]
[TD] 0.56
[/TD]
[TD][/TD]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]07:00
[/TD]
[TD] 0.56
[/TD]
[/TR]
[TR]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]07:42
[/TD]
[TD] 0.51
[/TD]
[TD][/TD]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]07:30
[/TD]
[TD] 0.51
[/TD]
[/TR]
[TR]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]08:00
[/TD]
[TD] 0.61
[/TD]
[TD][/TD]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]08:00
[/TD]
[TD] 0.61
[/TD]
[/TR]
[TR]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]08:30
[/TD]
[TD] 0.59
[/TD]
[TD][/TD]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]08:30
[/TD]
[TD] 0.59
[/TD]
[/TR]
[TR]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]09:00
[/TD]
[TD] 0.61
[/TD]
[TD][/TD]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]09:00
[/TD]
[TD] 0.61
[/TD]
[/TR]
[TR]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]09:30
[/TD]
[TD] 0.61
[/TD]
[TD][/TD]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]09:30
[/TD]
[TD] 0.61
[/TD]
[/TR]
[TR]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]10:00
[/TD]
[TD] 0.74
[/TD]
[TD][/TD]
[TD="align: right"]Sun 01-Jan-17
[/TD]
[TD="align: right"]10:00
[/TD]
[TD] 0.74
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
Hia
Give this a go
Code: 
Sub ReplaceData()
' woofy

    Dim Usdrws As Long
    Dim Cl As Range

    Usdrws = Range("A" & Rows.Count).End(xlUp).Row
    
    For Each Cl In Range("C2:C" & Usdrws)
        If Cl.Value = "" Then Cl.Value = Cl.Offset(, 3).Value
    Next Cl

End Sub
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0
Woofy_McWoof_Woof,

Here is another macro for you to consider, that does not do any looping in the active worksheet, thru the rows in column C, and, should be very fast.


Code: 
Sub ReplaceMissingData()
' hiker95, 08/24/2017, ME1020214
Application.ScreenUpdating = False
Dim lr As Long
With ActiveSheet
  lr = .Columns("A:G").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
  On Error Resume Next
  With .Range("C2:C" & lr)
    .SpecialCells(xlCellTypeBlanks).Formula = "=RC[3]"
    .Value = .Value
  End With
  On Error GoTo 0
End With
Application.ScreenUpdating = True
End Sub
 
Last edited:
Upvote 0
Here is another non-looping macro that you can also consider...
Code: 
[table="width: 500"]
[tr]
	[td]Sub ReplaceMissingData()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("C2:C" & LastRow) = Evaluate(Replace("IF(C2:C#="""",G2:G#,C2:C#)", "#", LastRow))
End Sub[/td]
[/tr]
[/table]
 
Upvote 0
Woofy_McWoof_Woof,

Here is updaated macro for you to consider, that does not do any looping in the active worksheet, thru the rows in column C, and, should be very fast.


Code: 
Sub ReplaceMissingData_V2()
' hiker95, 08/24/2017, ME1020214
Application.ScreenUpdating = False
Dim lr As Long
With ActiveSheet
  lr = .Columns("A:F").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
  On Error Resume Next
  With .Range("C2:C" & lr)
    .SpecialCells(xlCellTypeBlanks).Formula = "=RC[3]"
    .Value = .Value
  End With
  On Error GoTo 0
End With
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Rick Rothstein said:
Here is another non-looping macro that you can also consider...
Code: 
[table="width: 500"]
[tr]
	[td]Sub ReplaceMissingData()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("C2:C" & LastRow) = Evaluate(Replace("IF(C2:C#="""",G2:G#,C2:C#)", "#", LastRow))
End Sub[/td]
[/tr]
[/table]
Click to expand...

@Woofy_McWoof_Woof,

One note about my code above... when I copy/pasted what you posted into my copy of Excel, Column D remained blank and your last three columns of data went into Columns E, F and G. I notice in hiker95's code that he assumed your columns were contiguous ending at Column F, not G like I assumed. If hike95's layout is correct, then here is my code modified assuming that layout...
Code: 
[table="width: 500"]
[tr]
	[td]Sub ReplaceMissingData()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("C2:C" & LastRow) = Evaluate(Replace("IF(C2:C#="""",F2:F#,C2:C#)", "#", LastRow))
End Sub[/td]
[/tr]
[/table]



hiker95 said:
Here is updaated macro for you to consider, that does not do any looping in the active worksheet, thru the rows in column C, and, should be very fast.
Click to expand...

@hiker95,

First, let me state that in human terms, the difference in speed between the code you posted in Message #7 and the code I posted in Message #6 would not be noticeable. I am not sure how this will translate to other computer, but on my system, my code was some three times faster than your code BUT, as I said, that difference is not noticeable in human terms. I carried the OP's data layout down through 12000 rows where 4000 of them were blank. On average, my code took 0.02 second to execute whereas your code took 0.06 seconds. Notice how small both of those numbers are which is why I say a human observer would not be able to perceive the difference.
 
Upvote 0
@hiker95,

First, let me state that in human terms, the difference in speed between the code you posted in Message #7 and the code I posted in Message #6 would not be noticeable. I am not sure how this will translate to other computer, but on my system, my code was some three times faster than your code BUT, as I said, that difference is not noticeable in human terms. I carried the OP's data layout down through 12000 rows where 4000 of them were blank. On average, my code took 0.02 second to execute whereas your code took 0.06 seconds. Notice how small both of those numbers are which is why I say a human observer would not be able to perceive the difference.
Click to expand...


Rick,

My statements in my reply #5, and, #7, concerning "and, should be very fast.", was in reference to Fluff's reply #2.
 
Last edited:
Upvote 0
hiker95 said:
My statements in my reply #5, and, #7, concerning "and, should be very fast.", was in reference to Fluff's reply #2.
Click to expand...
As my code took under a second to run on approx 14000 rows, I would have to agree with Rick when he said
a human observer would not be able to perceive the difference
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Help Please
2 3
Replies
21
Views
532
Asbestos_Jen
Asbestos_Jen
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
423
dshtesy
D
R
  • Solved
Age Group Ranging by gender
Replies
2
Views
275
Raj1979
R
B
  • Question Question
Excel Pivot Table to see the number of "Lost Calls"
Replies
0
Views
70
Bernie Galve
B
D
  • Solved
Formula to extract data for multiple critera
Replies
2
Views
63
Trebor76
T

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top