Combine two sets of overlapping Date/Time data into 3 columns.

[Tom Langley]

I have two sets of data with Date/Time. One set has a fixed interval, the
second is more sporadic. I am trying to combine into these sets into one
table with the data from one set next to the corresponding data from the
second set.

For example:
A B C D
4/22/2010 9:00 000 4/22/2010 9:01 777
4/22/2010 9:01 111 4/22/2010 9:03 888
4/22/2010 9:02 222 4/22/2010 9:06 999
4/22/2010 9:03 333
4/22/2010 9:04 444
4/22/2010 9:05 555
4/22/2010 9:06 666

Output:
A B C
4/22/2010 9:00 000
4/22/2010 9:01 111 777
4/22/2010 9:02 222
4/22/2010 9:03 333 888
4/22/2010 9:04 444
4/22/2010 9:05 555
4/22/2010 9:06 666 999

Output in column E would be fine, too. Then I could just copy and paste.

I've tried VLOOKUP and INDEX-MATCH to no avail and I haven't been able to
find a similar problem/solution in here. Is this possible, or do I need to
use a Macro or something else? Thanks.

 

[Luke M]

Assuming that the values in column A match values in column C EXACTLY (no
microseconds...) you should be able to do in column E:
=IF(ISERROR(MATCH(A2,C:C,0)),"",INDEX(D,MATCH(A2,C:C,0)))

 

[Max]

In E1: =IF(ISNA(MATCH(A1,C:C,0)),"",INDEX(D,MATCH(A1,C:C,0)))
Copy down. Tested here on your sample data, seems to work ok. Joy? hit the
YES below

 

[Tom Langley]

I do have seconds hidden in there. Is there any way to change the seconds to
"00" or to eliminate them from each column? I can't get ROUND to take care
of it and subtracting SECONDS changes the day.

Any suggestions? Thanks