Woofy_McWoof_Woof
Board Regular
- Joined
- Oct 7, 2016
- Messages
- 60
- Office Version
- 365
- Platform
- 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]
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]