Interpolated values referenced to time.

[rosscu]

Hi,


I have data in columns B and C that are referenced to time in column A. Time in Column A is in format hh:mm:ss and starts at 00:00:00. I have approx 5000 rows of data.


The data in B and C are Easting and Northings that have been acquired at randomly increasing time increments between 1 and 25 seconds, for example:


[TABLE="width: 261"]
<tbody>[TR]
[TD]Acquired time[/TD]
[TD]Easting[/TD]
[TD]Northing[/TD]
[/TR]
[TR]
[TD]00:00:04[/TD]
[TD]712876[/TD]
[TD]9545869[/TD]
[/TR]
[TR]
[TD]00:00:07[/TD]
[TD]712871[/TD]
[TD]9545875[/TD]
[/TR]
[TR]
[TD]00:00:11[/TD]
[TD]712865[/TD]
[TD]9545882[/TD]
[/TR]
[TR]
[TD]00:00:14[/TD]
[TD]712860[/TD]
[TD]9545887[/TD]
[/TR]
[TR]
[TD]00:00:17[/TD]
[TD]712855[/TD]
[TD]9545893[/TD]
[/TR]
[TR]
[TD]00:00:21[/TD]
[TD]712848[/TD]
[TD]9545900[/TD]
[/TR]
[TR]
[TD]00:00:24[/TD]
[TD]712843[/TD]
[TD]9545905[/TD]
[/TR]
[TR]
[TD]00:00:27[/TD]
[TD]712838[/TD]
[TD]9545911[/TD]
[/TR]
</tbody>[/TABLE]



I would like Easting and Northings every second, interpolated linearly.


I would very much appreciate it if anyone could tell me how to do this in excel!


Thank you,
Ross

 

[wideboydixon]

Start data:

Book1
	A	B	C
1	Acquired time	Easting	Northing
2	00:00:04	712876	9545869
3	00:00:07	712871	9545875
4	00:00:11	712865	9545882
5	00:00:14	712860	9545887
6	00:00:17	712855	9545893
7	00:00:21	712848	9545900
8	00:00:24	712843	9545905
9	00:00:27	712838	9545911
Sheet1

End data:

Book1
	A	B	C
1	Acquired time	Easting	Northing
2	00:00:04	712876	9545869
3	00:00:05	712874.3	9545871
4	00:00:06	712872.7	9545873
5	00:00:07	712871	9545875
6	00:00:08	712869.5	9545877
7	00:00:09	712868	9545879
8	00:00:10	712866.5	9545880
9	00:00:11	712865	9545882
10	00:00:12	712863.3	9545884
11	00:00:13	712861.7	9545885
12	00:00:14	712860	9545887
13	00:00:15	712858.3	9545889
14	00:00:16	712856.7	9545891
15	00:00:17	712855	9545893
16	00:00:18	712853.3	9545895
17	00:00:19	712851.5	9545897
18	00:00:20	712849.8	9545898
19	00:00:21	712848	9545900
20	00:00:22	712846.3	9545902
21	00:00:23	712844.7	9545903
22	00:00:24	712843	9545905
23	00:00:25	712841.3	9545907
24	00:00:26	712839.7	9545909
25	00:00:27	712838	9545911
Sheet1

Macro:

Public Sub InterpolateEastingNorthing()

Dim lastRow As Long
Dim thisRow As Long
Dim lastDate As Date
Dim lastEasting As Double
Dim lastNorthing As Double
Dim thisDate As Date
Dim thisEasting As Double
Dim thisNorthing As Double
Dim missingRows As Long
Dim newRow As Long

' Find the last row of data
lastRow = Cells(Rows.Count, "A").End(xlUp).Row

' Record the values from the second row as the starting values
lastDate = Range("A2").Value
lastEasting = Range("B2").Value
lastNorthing = Range("C2").Value

' Start processing from row 3
thisRow = 3

' Process all rows
Do While thisRow <= lastRow
    ' Record the values from the current row
    thisDate = Cells(thisRow, "A").Value
    thisEasting = Cells(thisRow, "B").Value
    thisNorthing = Cells(thisRow, "C").Value
    
    ' Calculate how many rows are missing (86400 is the number of seconds in a day)
    missingRows = (thisDate - lastDate) * 86400 - 1
    
    ' Are we missing some rows?
    If missingRows > 0 Then
        ' Insert new rows and shift the data down
        Rows(CStr(thisRow) & ":" & CStr(thisRow + missingRows - 1)).Insert Shift:=xlDown
        
        ' Populate data into each new row based on difference between the numbers
        For newRow = 1 To missingRows
            Cells(thisRow + newRow - 1, "A").Value = lastDate + TimeSerial(0, 0, newRow)
            Cells(thisRow + newRow - 1, "B").Value = lastEasting + (thisEasting - lastEasting) / (missingRows + 1) * newRow
            Cells(thisRow + newRow - 1, "C").Value = lastNorthing + (thisNorthing - lastNorthing) / (missingRows + 1) * newRow
        Next newRow
        
        ' We've moved the last row and the current row
        lastRow = lastRow + missingRows
        thisRow = thisRow + missingRows
    End If
    
    ' Save the current values
    lastDate = thisDate
    lastEasting = thisEasting
    lastNorthing = thisNorthing
    
    ' Move to the next row
    thisRow = thisRow + 1
Loop

End Sub

WBD

 

[rosscu]

Wow. That was very quick and works perfectly. I am not worthy.

I'm pushing my luck here, but do you know how can I add more pairs of Easting/Northings to the input data? For example, pairs of Easting/Northings up to columns BX/BY? They would be referenced to the same time in column A as the originals were in columns B and C.

Thank you very much you absolute legend.

 

[wideboydixon]

Hey @rosscu,

This should do as many columns as you have headings for in the first row:

Public Sub InterpolateEastingNorthing()

Dim lastRow As Long
Dim thisRow As Long
Dim lastCol As Long
Dim thisCol As Long
Dim lastDate As Date
Dim thisDate As Date
Dim lastValues As Variant
Dim thisValues As Variant
Dim missingRows As Long
Dim newRow As Long

' Find the last row and column of data
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
lastCol = Cells(1, Columns.Count).End(xlToLeft).Column

' Record the values from the second row as the starting values
lastDate = Range("A2").Value
lastValues = Range(Cells(2, 2), Cells(2, lastCol)).Value

' Start processing from row 3
thisRow = 3

' Process all rows
Do While thisRow <= lastRow
    ' Record the values from the current row
    thisDate = Cells(thisRow, "A").Value
    thisValues = Range(Cells(thisRow, 2), Cells(thisRow, lastCol)).Value
    
    ' Calculate how many rows are missing (86400 is the number of seconds in a day)
    missingRows = (thisDate - lastDate) * 86400 - 1
    
    ' Are we missing some rows?
    If missingRows > 0 Then
        ' Insert new rows and shift the data down
        Rows(CStr(thisRow) & ":" & CStr(thisRow + missingRows - 1)).Insert Shift:=xlDown
        
        ' Populate data into each new row based on difference between the numbers
        For newRow = 1 To missingRows
            Cells(thisRow + newRow - 1, "A").Value = lastDate + TimeSerial(0, 0, newRow)
            For thisCol = 2 To lastCol
                Cells(thisRow + newRow - 1, thisCol).Value = lastValues(1, thisCol - 1) + (thisValues(1, thisCol - 1) - lastValues(1, thisCol - 1)) / (missingRows + 1) * newRow
            Next thisCol
        Next newRow
        
        ' We've moved the last row and the current row
        lastRow = lastRow + missingRows
        thisRow = thisRow + missingRows
    End If
    
    ' Save the current values
    lastDate = thisDate
    lastValues = thisValues
    
    ' Move to the next row
    thisRow = thisRow + 1
Loop

End Sub

WBD