I have GPS data that represents the movement of an animal underwater (so it is really the GPS position of a boat that followed the animal).
I also have a spreadsheet of recorded behaviors from the animal that was carrying a camera, e.g. when they were feeding.
The two data sets were taken at the same time and I can synchronize them so I know what behavior the animal was doing when the boat was in a certain position.
When I merge the two data sets in a spreadsheet I can sort it so that all the data is combined in chronological order. This leaves me with my 'behaviors' mixed in between my 'positional' data but with no lat or long positions themselves.
I would like to assign lat and long data to these empty cells based on the 'closest' time to this row's time that does have lat-long data. This way the recorded behaviors of my animal will be assigned a position in the most accurate way possible. These 'closest' values may be several cells above or below this row.
Can Excel compare the 'timestamp' in this row to the timestamp in the closest cell above or below that includes lat and long data and then calculate which time value is closest to its time value and then use the lat and long data from this row and insert these into its own row?
When I map this in a GIS this may look a bit messy as the GPS data does have some gaps in it. I.e. There might be several behaviors associated with one lat-long location, but this is the best I can do.
Does anyone have any experience of merging positional data with no positional data and assigning positional data based on the timestamp?
An example of this problem is attached. Rows 4 and 5 would be auto-filled with the 'closest time' to them which is the lat and long data from row 3. At the same time row, 6 is closer to the time in row 7 and where therefore be filled with the lat and long data from there. There are hundreds of these gaps throughout my data sets of about 16 animals so I would like a formula to apply to each data set.