Remember Me?

#1
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
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
April 21st 10, 02:26 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 9,221
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
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
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.

.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Rajula Excel Discussion (Misc queries) 0 October 22nd 07 05:22 PM [email protected] Excel Worksheet Functions 1 April 25th 07 05:01 PM lmeg Charts and Charting in Excel 0 August 7th 06 12:10 PM Mike Excel Worksheet Functions 1 August 17th 05 10:22 PM Brown New Users to Excel 2 March 23rd 05 12:14 AM

All times are GMT +1. The time now is 06:20 AM.