Match columns by second and duplicate values for each match

[james_y]

Hi all,

I have three columns: A (Time), B (Value), C (Time), D (duplicated values from column B)
What I want to do is duplicate a value to column D every time column A and C have the same time.

Column A and C have the same time range. However, there are two problems:
i) column A has 3000 rows for time, but column C has 40,000! That is because it has multiple entries for each second, as the example shows.
ii) column A is sometimes not sequential, i.e., some seconds are missing, 55, 56, 58 etc.

So, the steps that need to happen to solve this are:
1) Interpolate any missing seconds in column A, by inserting missing seconds and duplicating a neighbouring value from column B to fill the gap
2) Duplicate the value from column B and copy to D for every repeating/matching second in column C, i.e., in the example below column D would simply be 13.30 repeated, and so on.


Example of data:

Time Metres Time Metres
23:59:06 13.30 23:59:06 0.00
23:59:07 13.17 23:59:06 0.00
23:59:09 13.13 23:59:06 0.00
23:59:10 12.91 23:59:06 0.00
23:59:12 13.25 23:59:06 0.00
23:59:13 13.36 23:59:06 0.00
23:59:15 13.44 23:59:06 0.00
23:59:16 13.34 23:59:06 0.00
23:59:17 13.08 23:59:06 0.00


Thank you in advance!!