Compare fields in two sheets ... return associated data to third sheet

[delirium]

Spreadsheet attached ...

Object of worksheet <Compiled> is to place Remedy!G (Modified By) into Compiled!H (Remedy Ticket) when Remedy!F (Created By) & Remedy!G (Modified By) match Transfers!B (Login) & Transfers!E (Destination Agent)

I was attempting to modify the code suggestion in the following thread http://www.excelforum.com/excel-prog...copy-data.html Solved by ratcat and DonkeyOte.

Because this data set could become very large, I opted for DonkeyOte's solution:

I put the following code in Compiled!G

Please Login or Register  to view this content.

and this in Compiled!H:

Please Login or Register  to view this content.

I am confused about the value in the Lookup function ... honestly i'm not convinced the Lookup function is the right choice. There is also one catch. There may be multiple matches ... and although there is another reference column the Date Time columns will not match precisely.

FROM TRANSFERS TAB
AbHernan 7/17/2010 23:00
AbHernan GCohen 7/19/2010 20:07
AbHernan RMejia 7/19/2010 23:27
AbHernan JMarisca 7/22/2010 19:41
AbHernan RMejia 7/19/2010 19:36
AbHernan JAhmadni 7/17/2010 19:36
AbHernan RMarquez 7/17/2010 19:52

FROM REMEDY TAB
AbHernan RMEJIA 7/19/2010 19:35
AbHernan JAHMADNI 7/17/2010 19:35
AbHernan RMARQUEZ 7/17/2010 19:51
AbHernan INAV 7/17/2010 22:59
AbHernan JUAPENA 7/18/2010 17:18
AbHernan RMEJIA 7/19/2010 23:26
AbHernan JMARISCA 7/22/2010 19:41

Thanks in advance for any advice you can offer. Just please don't make fun of me cuz i mucked up the code completely. After about an hour of looking at it i start losing comprehension.

emily

[delirium]

I'm wondering if the INDEX function wouldn't be the solution to finding a match on the Names and the nearest time within those matches?

[DonkeyOte]

I came across this whilst conducting a vanity search

I confess I can't quite follow what it is you're trying to retrieve... you state:

place Remedy!G (Modified By) into Compiled!H (Remedy Ticket) when Remedy!F (Created By) & Remedy!G (Modified By) match Transfers!B (Login) & Transfers!E (Destination Agent)

but the above does not make much sense me to given Remedy!G is just Transfers!E and you then go on to mention significance of Date which is not mentioned above.

What I would suggest you do is outline the expected results for the given sample.
This helps people get to grips with the unwritten requirements that may exist whilst also ensuring they validate their logic to return correct results.

[delirium]

I apologize if i was not clear ... but the goal is to return a "YES" to Compiled!H where Transfers!B (login) = Remedy!F (Created By) and Transfers!E (Destination Agent) = Remedy!G (Modified By). Where there are multiple matches I want it to compare the one with the closest time (Remedy!E and Transfers!H). Say ... within 15 mins? That number may be reduced as we see that most times are within a minute or two of one another.

The point of the report is to determine the calls that were transferred WITHOUT a Remedy ticket. So i will be pulling ALL the rows from the Transfers tab into the Compiled Tab and only those rows that match the criteria above from the Remedy tab.

[delirium]

Ok ... I'm getting closer ... Have been able to verify the rows that have a match in both sheets, but I am having one last issue. Comparing Remedy Tab column A with Compiled tab Column G (both concatenations) I have been able to return the line # or the ticket # from the Remedy tab.

I am using the following code:

Please Login or Register  to view this content.

The only issue remaining is that, when there are multiple matches, I need to figure out how to tell it to pick the match that is closest in time. The time on the Remedy tab (column F) will be slightly earlier than the time on the Transfers tab (column H). The difference should be mere seconds to a couple of minutes. I know that this topic hasn't been very popular ... probably because I'm having a hard time expressing what it is I am trying to do. but if anyone could help me, I would be eternally grateful.

~M

*** Updated spreadsheet attached ... an example of a duplicate value is highlighted on all tabs in red ***