A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

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



 
 
Thread Tools Display Modes
  #1  
Old April 21st 10, 02:20 PM posted to microsoft.public.excel.worksheet.functions
Tom Langley
external usenet poster
 
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.

Ads
  #2  
Old April 21st 10, 02:24 PM posted to microsoft.public.excel.worksheet.functions
Luke M[_4_]
external usenet poster
 
Posts: 457
Default Combine two sets of overlapping Date/Time data into 3 columns.

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.
>



  #3  
Old April 21st 10, 02:26 PM posted to microsoft.public.excel.worksheet.functions
Max
external usenet poster
 
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  
Old April 21st 10, 04:44 PM posted to microsoft.public.excel.worksheet.functions
Tom Langley[_2_]
external usenet poster
 
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.
> >

>
>
> .
>

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 03:17 AM.


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