Find nearest match date and time for a particular user

[cormalu]

Hi,

I have two different data sources which gather information on users. One is a telephony recording system and the other is a CRM type system. I have generated CSV dumps from both systems. I would like to build a formula which would use a name and date/time from the CRM sheet and then find an exact match name and the closest date/time match in the telephony sheet.

EG: =function("John Smith", 13/04/2016 17:54) should return either

1. "" if John Smith is not in the telephony sheet
2. 17971652 -- the unique id for the closest matching record to the passed date/time in the telephony system

Thanks in advance

Cormac

 

[Special-K99]

What is the layout of both files?
e.g.
File 1 Columnn A First Name
File 1 Column B Surname
etc

 

[cormalu]

Telephony file example
[TABLE="width: 768"]
<colgroup><col width="128" span="6" style="width:96pt"> </colgroup><tbody>[TR]
[TD="width: 128"]Call Start Time[/TD]
[TD="width: 128"]Call End Time[/TD]
[TD="width: 128"]Call Duration[/TD]
[TD="width: 128"]Group[/TD]
[TD="width: 128"]Extension Name[/TD]
[TD="width: 128"]Suppressed Call[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]03/01/2017 08:09[/TD]
[TD="class: xl63, align: right"]03/01/2017 08:09[/TD]
[TD]00:00:17[/TD]
[TD]Business Support[/TD]
[TD]Paula Cleary[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]03/01/2017 08:09[/TD]
[TD="class: xl63, align: right"]03/01/2017 08:11[/TD]
[TD]00:01:33[/TD]
[TD]IT Operations[/TD]
[TD]Stephen Waite[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]03/01/2017 08:09[/TD]
[TD="class: xl63, align: right"]03/01/2017 08:11[/TD]
[TD]00:01:33[/TD]
[TD]Business Support[/TD]
[TD]Paula Cleary[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]03/01/2017 08:20[/TD]
[TD="class: xl63, align: right"]03/01/2017 08:21[/TD]
[TD]00:01:02[/TD]
[TD]IT Operations[/TD]
[TD]Stephen Waite[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]03/01/2017 08:31[/TD]
[TD="class: xl63, align: right"]03/01/2017 08:34[/TD]
[TD]00:03:24[/TD]
[TD]QuotesClaims[/TD]
[TD]Claire Louise Murphy[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]03/01/2017 08:34[/TD]
[TD="class: xl63, align: right"]03/01/2017 08:36[/TD]
[TD]00:02:19[/TD]
[TD]Health Claims[/TD]
[TD]Philip Knight[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]03/01/2017 08:38[/TD]
[TD="class: xl63, align: right"]03/01/2017 08:38[/TD]
[TD]00:00:54[/TD]
[TD][/TD]
[TD]Mark George[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]03/01/2017 08:39[/TD]
[TD="class: xl63, align: right"]03/01/2017 08:40[/TD]
[TD]00:00:29[/TD]
[TD]IT Operations[/TD]
[TD]Stephen Waite[/TD]
[TD]No





[/TD]
[/TR]
</tbody>[/TABLE]

 

[cormalu]

CRM file example

[TABLE="width: 270"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Start Date[/TD]
[TD]Staff Name[/TD]
[/TR]
[TR]
[TD="align: right"]05/01/2017 11:26[/TD]
[TD]Amy Rafferty[/TD]
[/TR]
[TR]
[TD="align: right"]30/01/2017 11:01[/TD]
[TD]Amy Rafferty[/TD]
[/TR]
</tbody>[/TABLE]

 

[cormalu]

So I am looking up "Amy Rafferty" for the indicated date in file 1 -- hope that makes sense??

 

[cormalu]

Not sure if I am following the right protocols -- I really appreciate your help