Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 21st 10, 02:20 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2010
Posts: 1
Default 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   Report Post  
Old April 21st 10, 02:26 PM posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default 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   Report Post  
Old April 21st 10, 04:44 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2010
Posts: 2
Default 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.



.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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


All times are GMT +1. The time now is 11:36 AM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017