If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Combine two sets of overlapping Date/Time data into 3 columns.
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. |
#3
|
|||
|
|||
Combine two sets of overlapping Date/Time data into 3 columns.
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 -- Max Singapore --- "Tom Langley" wrote: 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. |
#4
|
|||
|
|||
Combine two sets of overlapping Date/Time data into 3 columns.
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 "Luke M" wrote: 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))) -- Best Regards, Luke M "Tom Langley" Tom wrote in message ... 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. . |
Thread Tools | |
Display Modes | |
|
|