How do I create a uniform hourly timeseries with blanks where data is missing?

[glowfish]

Hello
I am working with a time series of current speed and direction. The problem is that there are gaps in my data.

Example:

ID Date Time Direction Speed
1 23/09/2010 12:00 227 74.2
2 23/09/2010 18:00 232 81.1
3 23/09/2010 19:00 229 87.8
4 23/09/2010 20:00 227 87
5 23/09/2010 21:00 229 81.3
6 23/09/2010 22:00 229 78.1
7 23/09/2010 23:00 230 77.6
8 24/09/2010 00:00 227 79.2
9 24/09/2010 01:00 219 72.4
10 24/09/2010 02:00 64 49.5
11 24/09/2010 06:00 227 85.3
12 24/09/2010 07:00 228 89.2
13 24/09/2010 12:00 221 66

How do I get the date and time to be uniformly spaced as hourly intervals with blanks where there is missing data. The id is not important and will be left out in my spreadsheet.

Lisa

 

[Weaver]

How about if you build another table in another sheet with all the times in and then use vlookup to pull out the relevant data into this table from your original data.

You can get blanks rather than error values by:

if(iserror(vlookup(myvalue,myrange,mycolumn,0)),"",vlookup(myvalue,myrange,mycolumn,0))

or you could perform the lookup, copy and paste the results as values and then use find/replace to replace all the #n/a's with blanks

HTH

 

[Ruddles]

If you're happy with a VBA solution, this piece of code appears to do the job, even if it's a bit 'clunky':-

[FONT=Courier New][SIZE=1]Option Explicit[/SIZE][/FONT]
[FONT=Courier New][SIZE=1][/SIZE][/FONT] 
[FONT=Courier New][SIZE=1]Public Sub InsertMissingValues()[/SIZE][/FONT]
[FONT=Courier New][SIZE=1][/SIZE][/FONT] 
[FONT=Courier New][SIZE=1]  Dim iLastRow As Long
  Dim iRow As Long
  Dim dtPrevious As Date
  Dim dtCurrent As Date
  Dim dtOneHour As Date
  
  iLastRow = Cells(Rows.Count, 1).End(xlUp).Row
  dtOneHour = TimeValue("01:00:00")
  iRow = 3
  Do Until iRow > iLastRow
    dtPrevious = Cells(iRow - 1, 2) + Cells(iRow - 1, 3)
    dtCurrent = Cells(iRow, 2) + Cells(iRow, 3)
    If Format(dtCurrent, "dd/mm/yyyy hh:nn") <> Format(dtPrevious + dtOneHour, "dd/mm/yyyy hh:nn") Then
      Rows(iRow).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
      Cells(iRow, 2) = Int(dtPrevious + dtOneHour)
      Cells(iRow, 3) = dtPrevious - Int(dtPrevious + dtOneHour) + dtOneHour
      iLastRow = iLastRow + 1
    End If
    iRow = iRow + 1
  Loop[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]  [/SIZE][/FONT]
[FONT=Courier New][SIZE=1]End Sub[/SIZE][/FONT]

Make a copy of your workbook and paste this code into the worksheet's code module, then run it.

Any good?

 

[Ruddles]

*** Re-posted because my machine threw a wobbly and the first post didn't appear at first! Ignore that code and use this version instead. ***

If you're happy with a VBA-based solution, this will do the job even if it's a bit on the 'clunky' side. I'm not brilliant at handling dates & times!

[FONT=Courier New][SIZE=1]Option Explicit[/SIZE][/FONT]
 
[FONT=Courier New][SIZE=1]Public Sub InsertMissingValues()[/SIZE][/FONT]
 
[FONT=Courier New][SIZE=1]Dim iLastRow As Long[/SIZE][/FONT]
[SIZE=1][FONT=Courier New]Dim iValues As Long[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]Dim iRow As Long[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]Dim dtPrevious As Date[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]Dim dtCurrent As Date[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]Dim dtOneHour As Date[/FONT][/SIZE]
 
[SIZE=1][FONT=Courier New]iLastRow = Cells(Rows.Count, 1).End(xlUp).Row[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]iValues = iLastRow - 1[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]dtOneHour = TimeValue("01:00:00")[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]iRow = 3[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]Do Until iRow > iLastRow[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]  dtPrevious = Cells(iRow - 1, 2) + Cells(iRow - 1, 3)[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]  dtCurrent = Cells(iRow, 2) + Cells(iRow, 3)[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]  If Format(dtCurrent, "dd/mm/yyyy hh:nn") <> Format(dtPrevious + dtOneHour, "dd/mm/yyyy hh:nn") Then[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]    Rows(iRow).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]    Cells(iRow, 2) = Int(dtPrevious + dtOneHour)[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]    Cells(iRow, 3) = dtPrevious - Int(dtPrevious + dtOneHour) + dtOneHour[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]    iLastRow = iLastRow + 1[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]  End If[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]  iRow = iRow + 1[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]Loop[/FONT][/SIZE]
 
[SIZE=1][FONT=Courier New]MsgBox CStr(iValues) & " found in source data" & Space(10) & vbCrLf & vbCrLf _[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]     & CStr(iLastRow - iValues - 1) & " values inserted" & Space(10), vbOKOnly + vbInformation[/FONT][/SIZE]
 
[FONT=Courier New][SIZE=1]End Sub[/SIZE][/FONT]

Make a copy of your workbook and paste this code into the code module of the worksheet containing the data, then run it.

Any good?

 

[glowfish]

Okay, Im still struggling..dont know VBA and the vlookup function is not working .

I am using the following formula

=VLOOKUP(D2,A$2:B$347,2,FALSE)

The first two entries are correct and then I only get #N/A, even if the time in column D exist is column A (??). The value in E4 should be 228. What am I doing wrong?

<table width="452" border="0" cellpadding="0" cellspacing="0"><col style="width: 81pt;" width="108"> <col style="width: 48pt;" width="64"> <col style="width: 81pt;" width="108" span="2"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; width: 81pt;" width="108" align="center" height="17">DateTime</td> <td class="xl25" style="width: 48pt;" width="64" align="right">Dir</td> <td class="xl25" style="width: 81pt;" width="108">
</td> <td class="xl25" style="width: 81pt;" width="108" align="center">DateTime</td> <td class="xl25" style="width: 48pt;" width="64" align="right">Dir</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" align="right" height="17">22/09/2010 10:00</td> <td align="right">227</td> <td>
</td> <td class="xl24" align="right">22/09/2010 10:00</td> <td align="right">227</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" align="right" height="17">22/09/2010 11:00</td> <td align="right">229</td> <td>
</td> <td class="xl24" align="right">22/09/2010 11:00</td> <td align="right">229</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" align="right" height="17">22/09/2010 12:00</td> <td align="right">228</td> <td>
</td> <td class="xl24" align="right">22/09/2010 12:00</td> <td align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" align="right" height="17">22/09/2010 13:00</td> <td align="right">228</td> <td>
</td> <td class="xl24" align="right">22/09/2010 13:00</td> <td align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" align="right" height="17">22/09/2010 14:00</td> <td align="right">226</td> <td>
</td> <td class="xl24" align="right">22/09/2010 14:00</td> <td align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" align="right" height="17">22/09/2010 15:00</td> <td align="right">227</td> <td>
</td> <td class="xl24" align="right">22/09/2010 15:00</td> <td align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" align="right" height="17">22/09/2010 17:00</td> <td align="right">228</td> <td>
</td> <td class="xl24" align="right">22/09/2010 16:00</td> <td align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" align="right" height="17">22/09/2010 19:00</td> <td align="right">227</td> <td>
</td> <td class="xl24" align="right">22/09/2010 17:00</td> <td align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" align="right" height="17">22/09/2010 20:00</td> <td align="right">228</td> <td>
</td> <td class="xl24" align="right">22/09/2010 18:00</td> <td align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" align="right" height="17">22/09/2010 21:00</td> <td align="right">230</td> <td>
</td> <td class="xl24" align="right">22/09/2010 19:00</td> <td align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" align="right" height="17">23/09/2010 00:00</td> <td align="right">228</td> <td>
</td> <td class="xl24" align="right">22/09/2010 20:00</td> <td align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" align="right" height="17">23/09/2010 02:00</td> <td align="right">228</td> <td>
</td> <td class="xl24" align="right">22/09/2010 21:00</td> <td align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" align="right" height="17">23/09/2010 03:00</td> <td align="right">239</td> <td>
</td> <td class="xl24" align="right">22/09/2010 22:00</td> <td align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" align="right" height="17">23/09/2010 04:00</td> <td align="right">225</td> <td>
</td> <td class="xl24" align="right">22/09/2010 23:00</td> <td align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" align="right" height="17">23/09/2010 07:00</td> <td align="right">228</td> <td>
</td> <td class="xl24" align="right">23/09/2010 00:00</td> <td align="center">#N/A</td> </tr> </tbody></table>
Pls help!

 

[jasonb75]

Okay, Im still struggling..dont know VBA

Test on a copy of your file if you are unsure.

Open the (copy of) the sheet with the source data.

Press Alt F11. In the menu select Insert then Module.

Copy the code ruddles provided in #4 then paste that to the VBA editor.

Press F5, then return to excel, your sheet should be updated.

 

[Ruddles]

Make a copy of your workbook (in case things go haywire). Open your worksheet and press Alt-F11 to open Microsoft Visual Basic, press
Ctrl-R to view the Project Explorer, then go Insert > Module.

A new 'general' code module will appear under Modules, probably called Module1. Double-click the name of this new module to open it. Remove any code you find in the code window (probably only the words Option Explicit, if anything) and paste my code in its place - everything from Option Explicit down to and including End Sub.


Return to your worksheet. Click Developer > Macros, select the macro called InsertMissingValues from the dialog box and click Run. Stand back in amazement as one of three things happens:-

• Nothing at all
• Your worksheet is completely screwed up
• The missing values are inserted faster than the eye can follow

Then tell me which one.

 

[Weaver]

Relating to the vlookup formula, are the dates/times exact? i.e. are there any hidden 'seconds' that might be preventing a match?

Or perhaps some of the dates are stored as strings, which might also prevent a match. To check, select the column and set the format to 'general'. Proper dates will change to numbers, strings won't.

 

[glowfish]

Managed to get the VBA code working. Had some problems first with formatting, but managed to get it working.

Thanks! You guys are great.

 

[Ashp142]

Managed to get the VBA code working. Had some problems first with formatting, but managed to get it working.

Hi, I realize this post is old but I am going to take a chance -
Do you remember what changes you made to get the code working? I don't understand VBA and am having a hard time troubleshooting the error I am getting with this code.

Thanks!