Matching data between tables with a column being time values

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!

Should the answer for this example be 4?

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

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)

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!

You are most welcome!

The 0.25 represents 15 minutes (i.e. 1/4 hour). You can go smaller or larger if depending on the precision you need.

I look forward to your test results.

Have a good day.

Well,

I checked it today against many manually-made spreadsheets and it works like a charm!!

The only thing I need to do is to place a check on the column to set a flag when it returns "0" as a result so I can manually fix the specific cell and that I can do it easily. Of course this doesn't have to do with the formula itself but with the data which in some rare occasions they might be quirky enough to have a larger spread than the 15 minutes set in the formula. This can easily be mended by increasing the spread, right now I've put it to 20 mins and it works perfect!

I even spotted some mistakes I had done with the manual method!

A good thing that I noticed about the formula is that exactly because it calculates the spread of the difference you can even have higher values in the first table than those in the second and it will still give back a correct result! (sometimes I might have a value of 1 minute or 30 seconds higher than the one in the second table, although this happens rarely, still the formula caters for it!)

Thank you very much for all the help. The formula works perfect.

Kind regards,

Kostas

p.s I am wondering about something. With the formula we are able to pull the cell values from the table two and bring them to table 1. Would it be possible along with the value in the T2 VALUE column to grab the value in the T2 TIME column (the one we compare the TIME value in T1 to) as well and place it in a fourth column in T1?

So the result table in T1 would be something like (e.g)

A_____B_______C__________D
12__08:00:00__5________08:04:00

or would it become way too complicated? If it is too complicated never mind, cause I can just simply alter the formula and instead of grabbing the VALUE column values I can ask it to grab the TIME columb values from T2 and just copy the formula to a new column.

Thanx!

Originally Posted by kostas

Well,

I checked it today against many manually-made spreadsheets and it works like a charm!!

The only thing I need to do is to place a check on the column to set a flag when it returns "0" as a result so I can manually fix the specific cell and that I can do it easily. Of course this doesn't have to do with the formula itself but with the data which in some rare occasions they might be quirky enough to have a larger spread than the 15 minutes set in the formula. This can easily be mended by increasing the spread, right now I've put it to 20 mins and it works perfect!

I even spotted some mistakes I had done with the manual method!

A good thing that I noticed about the formula is that exactly because it calculates the spread of the difference you can even have higher values in the first table than those in the second and it will still give back a correct result! (sometimes I might have a value of 1 minute or 30 seconds higher than the one in the second table, although this happens rarely, still the formula caters for it!)

Thank you very much for all the help. The formula works perfect.

Kind regards,

Kostas

p.s I am wondering about something. With the formula we are able to pull the cell values from the table two and bring them to table 1. Would it be possible along with the value in the T2 VALUE column to grab the value in the T2 TIME column (the one we compare the TIME value in T1 to) as well and place it in a fourth column in T1?

So the result table in T1 would be something like (e.g)

A_____B_______C__________D
12__08:00:00__5________08:04:00

or would it become way too complicated? If it is too complicated never mind, cause I can just simply alter the formula and instead of grabbing the VALUE column values I can ask it to grab the TIME columb values from T2 and just copy the formula to a new column.

Thanx!

Well, thank you so much for this positive feedback. It makes me feel good to have helped you in this project. It is responses like yours that make it worthwhile to continue to frequent these forums and help out as much as possible. Again thank you!

For your last query, try this formula in D3:

=INDEX($F$3:$F$569,MATCH(1,($E$3:$E$569=A3)*($G$3:$G$569=C3),0))

Again, once you enter the formula, confirm it with CTRL+SHIFT+ENTER not just ENTER....you'll see curly {} brackets appear around the formula....

then copy the formula down the column....

Note: Any NA's or 0's in column C will correspond with an #N/A error to advise you of no matches found.

Hope this helps.

Yes, I guess that formula, tweaked the way you said would work too...but it returns 12:00 were your times were not found within the constraints....(i.e. where value in C is 0)...so beware....

To "merge" the 2 results into one column you can concatenate and minipulate the time result to return correctly:

Try this in C3, confirmed with CTRL+SHIFT+ENTER:

=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)))&" - "&TEXT(MIN(IF($E$3:$E$569=$A3,IF(ABS($F$3:$F$569-($B3-INT($B3)))*24<=0.25,$F$3:$F$569))),"h:mm:ss AM/PM"),"N/A")

No, you can't do that. There has to be a formula in each cell you want to display a result in.....

Unless you use VBA...but that will still need programming to do what you want.

Best bet is use the 2 formulas....

Try the alternative formula I gave you for column D....it might be faster. Not sure though.