Mapping Data between 2 sheets based on iteration time - VBA

Greetings All!

I’m here to seek some assistance on a task that I need to perform daily, but due to the magnitude of the data it simple takes a lot of time, and therefore was hoping if I could get help from this kind and brilliant forum.

Task requires: Mapping Data from one sheet to another. Here’s a step-by-step process (sample data files enclosed):

Step 1: Sort both sheets in ascending order using column A (Lowest to Highest)
Step 2: In column P of the sheet named Attempts concatenate data present in E, F and G columns to create a unique identifier
Step 3: In column Z of the sheet named Conversations concatenate data present in E, F and G columns to create a unique identifier
(The unique identifiers created in both sheets to be used to map the data from the Conversation sheet into the Attempts sheet)
Step 4: Mapping logic:
Only records tagged as “Conversation” in Column N of the Attempts sheet to be mapped with the Disp listed in column Q of the conversation sheet. The respective mapping from the “Conversation” sheet is to be populated in Column Q of the Attempts sheet

In cases, where there are several iterations of the same unique identifier tagged as Conversation in the Attempts sheet, I need to be able to map the respective Disp listed in column Q of the conversation sheet for every iteration w.r.t to the closest time provided in column A. To cite an example:
The unique identifier “RajivSharmaCS” in the enclosed has 3 iterations in “Attempts” sheet tagged as Conversation

• Iteration 1 occurred at 6/1/2015 2:01:28 PM
• Iteration 2 occurred at 6/1/2015 3:01:28 PM
• Iteration 3 occurred at 6/1/2015 4:01:28 PM

On the other hand, there are only 2 iterations of the unique identifier “RajivSharmaCS” in the Conversations sheet:

• Iteration 1 occurred at 6/1/2015 2:05:28 PM
• Iteration 2 occurred at 6/1/2015 4:03:28 PM

In the above scenario, I need to be able to map the Disp listed in Column Q with the closest time of the recorded tagged as “Conversation” in the Attempts sheet. As a result, you’ll see in the dummy sheet attached that the mapping I’ve done is as follows:

• Iteration 1 of Attempts mapped with Iteration 1 of Conversation sheet
• Iteration 3 of Attempts mapped with Iteration 2 of Conversation sheet

In addition to the above, there will be some unique identifiers in the Conversation sheet that will NOT map against the Unique Identifiers in the Attempts sheet. For these cases, I would simply like them to stay in the Conversation sheet, but would like the rows of those to be colored and column AA labeled as Log to distinguish them by stating “Unique Identifier - Mis-Match”

My expected process:

One folder to contain 2 sheets – Attempts and Conversation. VBA to be present in tab 2 of the attempts sheet. I’ll change the data in the attempts sheet for every set of data that needs to be run.

Apologies for the extensive text, but I felt it was imperative to jot every thing down for ease of understanding. Sorry, in case it sounds confusing. I’m more than happy to provide further explanation, if required.

Thank you in advance. Looking forward to see my life getting simplified

Best regards,

Spi