Mapping Data between 2 sheets based on iteration time - VBA

[spiwere]

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

[spiwere]

May I please request for assistance on this request please.....

Kindest regards,
Spi

[spiwere]

Sorry to bump this thread again, but I was really hoping to get some assistance on this please....

[spiwere]

I'm sorry to bump this one again. Could someone please kindly assist me on this.

Thanks a million in advance!

[spiwere]

Could anyone please assist me on this. I'll be obliged.

Thanks a million!

[LJMetzger]

Hi Spi,

See the attached .zip file which contains:
a. ExcelForumAttempts.xls
b. ExcelForumConversation.xls

I attempted to solve your problem using the following algorithm:

Please Login or Register  to view this content.

----------------------
Special Notes:
a. To change Sheet Names and/or 'Conversation' file name modify the following constants at the top of module ModMapData:

Please Login or Register  to view this content.

b. I thought it would be easier for you to verify results if temporary columns were not deleted, and if the data was not sorted. You can change this behavior by setting the values of the following CONDITIONAL COMPILATION constants to 'True' (no apostrophes) at the bottom of Sub ProcessSheetAttempts() in module ModMapData:
(1) NEED_FINAL_SORTING
(2) NEED_TEMPORARY_COLUMNS_CLEARED

Lewis

[spiwere]

Hi Spi,

I attempted to solve your problem using the following algorithm:

This is great. Thanks a zillion times. Please give me some time to test this out with my real data. I will get back to you by tomorrow. Thank you! You made my day!!!

[spiwere]

Hi Spi,

See the attached .zip file which contains:
a. ExcelForumAttempts.xls
b. ExcelForumConversation.xls

Lewis

Dear Lewis,

My apologies for taking time to get back to you. I've been testing the solution, and it seems there might be some modifications needed. It's to do with how the data is coming out of the system, and NOT your solution. But before I do that, I would like to test it a bit more. In all probability I should be able to get back to you by mid-next week. I hope it'll be okay!

Thanks as always, it is a masterpiece that I'll cherish. You are a magician -

Best regards,
Spi

[LJMetzger]

Hi Spi,

As always, it is no problem. Like I always say, the hardest part of the project is not writing the code, it's testing it properly.

I figured there would be some changes needed, especially since my speculation was that your real data files are probably very large. I'll do my best to help you reach a successful conclusion.

Lewis

[spiwere]

Hi Spi,

As always, it is no problem. Like I always say, the hardest part of the project is not writing the code, it's testing it properly.

I figured there would be some changes needed, especially since my speculation was that your real data files are probably very large. I'll do my best to help you reach a successful conclusion.

Lewis

Respected Lewis,

I do not have words to express how thankful I am. I'll continue testing, and get back to you. Thusfar, the test have run alright, the
issues I've spotted have to do with my data, and not your solution. I'm trying to determine the alternate approach to counter those.

Thanks a lot!

Best regards,
Spi

[LJMetzger]

the issues I've spotted have to do with my data

I expected we might have problems like that, especially since I had such a small amount of sample data to work with. When you're done testing, we should be able to work out the problems.

Lewis

[spiwere]

When you're done testing, we should be able to work out the problems.

Lewis

You always leave me astonished. I wonder how can someone be so helpful without any self interest. "THANK YOU"

Best regards,

Spi

[spiwere]

I expected we might have problems like that, especially since I had such a small amount of sample data to work with. When you're done testing, we should be able to work out the problems.

Lewis

Hi Lewis,

After testing it for over 40-hours, and applying several permutations and combinations, I’ve finally been able to overcome the data related challenges that were impacting the results. As it’s said, the “Devil is always in the Detail”. It appears that my problem can be solved through a simple fix; i.e. by altering the columns that were being used for concatenation. The issue was basically in the details that were getting updated in column E/F/G in the Conversation report, while no resulting update/change was applied in the Attempts report, which caused a disconnect in the mapping. That said, I sat and tested it for hours, and have in all probability identified that Column B and C are unique, and no changes can be made to them in any of the 2 reports. My interim results have fared out well. What I did was placed the data for B and C in Column E and F, and left G blank prior to running the magical solution.

In a nutshell, I believe that a fix by altering the concatenated columns will do the trick. However, owing to my very limited experience in coding, I did not alter anything in the code. If you could perhaps let me know the places where I need to change the concatenation, I can amend the code, and test it one more time. The rest of the logic's you’ve applied are awesome.

Last but not the least, please accept my sincere apologies for taking way too long on this, but it really required me to test it as thoroughly as I could.

Once again a very big Thank You for making this happen!

Best regards,
Spi

[LJMetzger]

Hi,

To do what you want, only small changes are probably needed. See the red items in the code below. There is a small change in:
a. Sub ProcessSheetAttempts()
b. Function SheetConversationPremiminaryProcessing()

I stripped out code that is not changing. The items in red are probably the only items that have to be changed:

Please Login or Register  to view this content.

Lewis

[spiwere]

[QUOTE=LJMetzger;4135058]Hi,

To do what you want, only small changes are probably needed. See the red items in the code below. There is a small change in:

[/CODE]

Awesome, thanks! Let the magic begin

Regards,
Spi

[spiwere]

First, my sincere apologies for taking too long, but wanted to test it thoroughly. This has made my life quite easy. Thank you a zillion times for your efforts and unmatched help.

I'm falling short of words as usual. Amazing solution

You rock. Please accept my most humble thank you and appreciation for all that you do. I wish I could learn from you someday.

Regards,
Spi