Hi everyone,
I have been struggiling with this for a couple days now and I am *this* close but then the solution eludes me.
I have two spreadsheets with two columns each (there are more columns but only two are used at the moment):
Spreadsheet 1 (sample):
<tbody>
</tbody>
Spreadsheet 2 (sample):
<tbody>
</tbody>
What I need to do is take the first e-mail address from Spreadsheet 1 and verify whether they exist in Spreadsheet 2 but only in a range bounded by the corresponding timestamp in Spreadsheet 1 (lower bound) and the same timestamp + 1h (upper bound).
Example:
John Doe has a timestamp of "01.08.2014 03:10:07" in Spreadsheet 1 and two timestamps in spreadsheet 2. When I look up his e-mail address in spreadsheet 2, I would see two values but the second one doesn't match my lookup constraints, therefore I need to only lookup in the range expanding at most 1h after their Spreadsheet 1 timestamp.
What I tried:
- Match() to obtain the lower bound of the lookup array, and that works:
But I can't obtain the upper bound.
- INDEX combined with MATCH to get the actual value in, but for some reason I end up with a humongous formula which doesn't do much.
The output should be 0 if the e-mail address is not in the range and 1 if it is.
I can create a VBA script which would to what I need but then I'll have to share that file with other people (read: management) and they don't like macros. Sadly, I'm fairly bad at matching formulas together.
Thanks in advance for responding to my question!
I have been struggiling with this for a couple days now and I am *this* close but then the solution eludes me.
I have two spreadsheets with two columns each (there are more columns but only two are used at the moment):
Spreadsheet 1 (sample):
| e-mail address | timestamp | |
| john.doe@website.com | 01.08.2014 03:10:07 | |
| jane.doe@website.com |
<colgroup><col width="203"></colgroup><tbody> </tbody> | |
| someone.else@website.com |
<colgroup><col width="203"></colgroup><tbody> </tbody> | |
| another.user@website.com |
<colgroup><col width="203"></colgroup><tbody> </tbody> |
<tbody>
</tbody>
Spreadsheet 2 (sample):
| e-mail address | timestamp | |
| john.doe@website.com |
<colgroup><col width="203"></colgroup><tbody> </tbody> | |
| jane.doe@website.com |
<colgroup><col width="203"></colgroup><tbody> </tbody> | |
| luke.skywalker@website.com |
<colgroup><col width="203"></colgroup><tbody> </tbody> | |
| john.doe@website.com | 01.08.2014 06:44:11 |
<tbody>
</tbody>
What I need to do is take the first e-mail address from Spreadsheet 1 and verify whether they exist in Spreadsheet 2 but only in a range bounded by the corresponding timestamp in Spreadsheet 1 (lower bound) and the same timestamp + 1h (upper bound).
Example:
John Doe has a timestamp of "01.08.2014 03:10:07" in Spreadsheet 1 and two timestamps in spreadsheet 2. When I look up his e-mail address in spreadsheet 2, I would see two values but the second one doesn't match my lookup constraints, therefore I need to only lookup in the range expanding at most 1h after their Spreadsheet 1 timestamp.
What I tried:
- Match() to obtain the lower bound of the lookup array, and that works:
Code:
=MATCH(1;('S1'!$D$2:$D$3493>='S2'!D2)*('S1'!$D$2:$D$3493<'S2'!F2);0)- INDEX combined with MATCH to get the actual value in, but for some reason I end up with a humongous formula which doesn't do much.
The output should be 0 if the e-mail address is not in the range and 1 if it is.
I can create a VBA script which would to what I need but then I'll have to share that file with other people (read: management) and they don't like macros. Sadly, I'm fairly bad at matching formulas together.
Thanks in advance for responding to my question!
