Matching columns and outputting lists of corresponding values

Hi, all. I'm new and am stuck with a problem in Excel 2007 with a lot of information (45,000 to 60,000 rows). Here's my problem:

From one machine, I have the date, hour, minute, second, and a measured numerical value, and from another machine, I also have the date, hour, minute, second, and a measured numerical value.

Whenever both machines took a measurement on the same day, at the same time (same hour, minute, and second), I need to know the corresponding numbers that both machines measured. I have an example below.

First machine (columns A-E):

Date Hour Min Sec Number
2009-09-22 0 14 39 90
2009-09-22 10 37 11 36
2009-09-22 11 55 15 42
2009-09-22 12 6 36 21
2009-09-22 13 18 58 96
2009-09-22 13 49 45 68
2009-09-22 15 1 46 93
2009-09-23 6 13 27 38
2009-09-23 18 21 52 27
2009-09-23 18 39 0 91
2009-09-23 20 16 17 26


Second machine (columns G-K):

Date Hour Min Sec Number
2009-09-22 5 59 39 18
2009-09-22 9 3 17 9
2009-09-22 10 8 41 39
2009-09-22 11 55 15 61
2009-09-23 13 15 58 28
2009-09-23 13 12 56 66
2009-09-23 15 1 46 48
2009-09-23 20 16 17 2
2009-09-24 18 21 38 31
2009-09-24 23 34 0 78
2009-09-25 3 1 58 46
2009-09-25 14 34 7 75
2009-09-25 14 59 26 57


Ideal output would be this:

42 61
26 2


I tried to make it as similar to my file as possible. One machine has more measurements than the other, so the lists are longer. Also, there may be more measurements taken on one day for the first machine, but less measurements taken on the same day by the other machine, so dates and times will not match up next to each other in rows.

Thank you so much if anyone can help! I've been working on this file for practically 7 hours every day since last Thursday.

Try this:

Insert a column between the 2 tables so that the second table now starts in column H.

In F2 enter formula:

=A2+TIME(B2,C2,D2) and copy down (this make the date and time in one cell)

in M2, similar formula:

=H2+TIME(I2,J2,K2) copied down

in G2 enter formula:

=IF(ISNUMBER(MATCH(F2,M:M,0)),COUNT(G$1:G1)+1,"") copied down

This compares value in F with values in M and returns a consecutive count of matches.

similar formula in N2:

=IF(ISNUMBER(MATCH(M2,F:F,0)),COUNT(N$1:N1)+1,"") copied down

Then in new column, say P1 enter:

=MAX(G:G)

and in Q1:

=MAX(N:N)

these give counts of matches for each set.

then in P2:

=IF(ROWS($A$1:$A1)>$P$1,"",INDEX(E:E,MATCH(ROWS($A$1:$A1),$G:$G,0)))

and in Q2:

=IF(ROWS($A$1:$A1)>$Q$1,"",INDEX(L:L,MATCH(ROWS($A$1:$A1),$N:$N,0)))

both copied down as far as you want...

these return the numbers at the matching points.

Here is your book back with my exact instructions added...

See results in P and Q columns.