Matching data between tables with a column being time values

**kostas** · 03-21-2007, 08:14 AM

Hi all and congratulations for all the help you are providing

here is my problem

I have two tables

one which has two columns and the another with three columns

It is basically like this
Table 1 __________ _________________________ Table 2
A_______________ B ____________ F _______________G___________ H
Number_________Time__________Number ________ Time _________ Value
1 __________ 14:02:00 __________ 2 __________ 14:03:30 _________ 7
2 __________ 14:05:00 __________ 5 __________ 14:04:00 _________ 10
3 __________ 14:08:00 __________ 1 __________ 14:07:00 _________ 15
4 __________ 14:10:30 __________ 4 __________ 14:11:30 _________ 4
5 __________ 14:12:00 __________ 3 __________ 14:13:00 _________ 2

As you noticed the time columns in the two tables are not exactly matching.

What I want to do is the following

match the number columns from table 1 with the number columns from table 2 AND match the Time column from table 1 with the Time column in Table 2 and then get whatever is in the Value column of table 2 as a result.

My main problem is that the times are not absolute matches. Therefore I have to implement a check that matches the Number columns and then matches the Time columns based on their proximacy and afterwards returns the value on the Value column (table 2).

I tried the sumproduct function but I can't seem to be able to implement the > or < signs so that I could possible have something like this:

=SUMPRODUCT(--(A1:A5=F1:F5),--(H1:H5<C1:C5),G1:G5)

or

=SUMPRODUCT(--(A1:A5=F1:F5),--(H1:H5<C1+TIME(0,5,0)),G1:G5

I tried the second formula to add some slack time in the table 2 Time column in case it would help the calculations (sometimes the Time value in Table 1 might be equal or slightly higher than the Time value in Table 2, so I want to be able to add an extra -standard- time in minutes to the Time column in Table 2).

So basically I want something that calculates this

IF the Number column in Table 1 is equal to the Number column in Table 2 _AND_ the Time column in Table 1 is between a 15 minute proximity with the Time column in Table 2, give me the value in the Value column in Table 2.

So , any ideas ? I would be grateful!

**NBVC** · 03-21-2007, 08:31 AM

Should the answer for this example be 4?

If so, =SUMPRODUCT(--(A2:A6=F2:F6),--((B2:B6-G2:G6)*24<=0.25),H2:H6)

**kostas** · 03-21-2007, 10:02 AM

Hi,
thank you for answering but I feel I have not made clear what I want

I need to create a new column in table 1 (e.g column C) where I can place the corresponding values.

So I want to match Number 4 from T1 with Number 4 from T2 AND the Time match between T1 and T2 to extract the corresponding value from T2 and put it in T1 C column.

Therefore as an example I would like to see in C1 the value 15.

Take into account that I might have the same Number values repeating in Table 2 at different times, but I want the VALUE column's value that corresponds to the one with the greatest proximity in regards to the Time column's values.

Hope this make it a bit more clear.

Another example would be:

Table 1 __________ _________________________ Table 2
A_______________ B ____________ F _______________G___________ H
Number_________Time__________Number ________ Time _________ Value
1 __________ 14:02:00 __________ 2 __________ 14:03:30 _________ 7
2 __________ 14:05:00 __________ 5 __________ 14:04:00 _________ 10
3 __________ 14:08:00 __________ 1 __________ 14:07:00 _________ 15
4 __________ 14:10:30 __________ 4 __________ 14:11:30 _________ 4
5 __________ 14:12:00 __________ 3 __________ 14:13:00 _________ 2
6 __________ 14:12:00 __________ 1 __________ 14:33:00 _________ 22

In this table (I just added an extra line to the previous one to illustrate what I want) I need to still get the Value 15 in a potential Table 1 C column cell C1.

**NBVC** · 03-21-2007, 10:10 AM

so then something like this in C2 copied down?

=SUMPRODUCT(--($F$2:$F$6=A2),--(($G$2:$G$6-B2)*24<=0.25),$H$2:$H$6)

**kostas** · 03-21-2007, 10:43 AM

This seems to give a result, although wrong.

Please see the attached file to get an idea of what kind of data I have and what I am looking to achieve.

Please note that Table 1 and Table 2 have different number of rows.

Thank you VERY much for trying to help.

**NBVC** · 03-21-2007, 12:33 PM

Try this formula, copied down:

=IF(ISNUMBER(MATCH(A3,$E$3:$E$569,0)),MIN(IF($E$3:$E$569=$A3,IF(ABS($F$3:$F$569-($B3-INT($B3)))*24<=0.25,$G$3:$G$569))),"N/A")

Formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER

See attached file

Hope this helps!