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 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. 
Ads 
#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. > > > > > . > 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Combine 2 sets of data  Rajula  Excel Discussion (Misc queries)  0  October 22nd 07 05:22 PM 
is it possible to combine data sets, eliminating duplicates?  [email protected]  Excel Worksheet Functions  1  April 25th 07 05:01 PM 
Two Sets of Time Data  One Chart  lmeg  Charts and Charting in Excel  0  August 7th 06 12:10 PM 
Overlapping or Duplicate Date & Time  Mike  Excel Worksheet Functions  1  August 17th 05 10:22 PM 
How to combine two data sets with one category of variable in com.  Brown  New Users to Excel  2  March 22nd 05 11:14 PM 