Home 
Search 
Today's Posts 
#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 INDEXMATCH 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 INDEXMATCH 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 INDEXMATCH 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. . 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Combine 2 sets of data  Excel Discussion (Misc queries)  
is it possible to combine data sets, eliminating duplicates?  Excel Worksheet Functions  
Two Sets of Time Data  One Chart  Charts and Charting in Excel  
Overlapping or Duplicate Date & Time  Excel Worksheet Functions  
How to combine two data sets with one category of variable in com.  New Users to Excel 