Need Help to speed-up the process using Macros

[jai.anand@aol.in]

Hi All,
First thing first, I would like to convey thanks to Sixthsense for assisting me on my previous post. The inputs provided have been extremely helpful.
However, I'm struggling to deal with a large chunk of data that needs to be processed on a daily basis, and was hoping for a Macro solution that can be used to speed up the process and alongside ensure accuracy.

Initial Step:

I need to populate the Max time for each row (exact match – case sensitive) in the Output sheet from the Base Sheet (refer to the attached sheet - Max Time Solution)

Step 1).

The biggest challenge is that the number of entries between the 2 aren’t the same and my Base sheet does not have any Unique ID like Output Sheet.

Final Step:

I finally pick the results attained from the “Initial Step” – Output Sheet and paste it on to another xls file to get my Final Result based on these conditions (refer to the The Final Step_Solution - Query - Step 2)

In this step: I create 2 files:

The row with the latest time needs to be exported to a CSV file after calculating the sum of breaks / visits in column E of The Final Step_Solution - Query - Step 2.

However, there is an exception to this in case the status is “Official Work” then this row should be exported for the exact match – case sensitive even though this may not be the latest time for the same match / row. The remaining exported to another XLS / CSV as batch file.

One additional point my original data has several other columns and the placement of some of the columns shown in the sample may be different but, the placement in the original file will always remain consistent. They don't have a role to play but are Exported or Batched accordingly.

I’ll be obliged if someone can assist me on this. The current solution is working accurately but my files tend to crash frequently due to size of the data. To be honest I don’t have expreience in writing Macros but I’m learning. I also use few in my other tasks. But for sure this needs some expert advise.

Please help!

[jai.anand@aol.in]

Hello All,

I need your assistance. Could you please look into my request.

Look forward to your inputs.

Thank you!

[jai.anand@aol.in]

No response on this...:-(

[jai.anand@aol.in]

This won't get help.

[jai.anand@aol.in]

Viewers of this post. Please note this isn't solved but, I'm closing this thread as solved.

Sorry!

[OllieB]

Jai,

Please explain why in step 1 on your output worksheet on row 9 there is a different time shown for Jim (i.e. 09:50:46) while there is no distinguishing information when compared to rows 7 and 8. I would also like to know why the time shown for Alice on row 5 is 00:00:00 whereas the key information for Alice on row 4 is identical, should not 16:03:43 be shown?

[OllieB]

Jai,

For step 2 there is no base file provided so the import step cannot be build. There also appears to be a discrepancy in the algorithm:

Employee WasanT9916M
- export row 11 because of match on "Official Work"
- export row 10 because of the highest timestamp when we exclude the Official Work row

Employee ManniC3636L
- export row 21 because of match on "Official Work"
- export row 28 because of match on "Official Work"
- NO export row 23 even though it has the highest timestamp of all rows if you ignore the Official Work rows (see example for previous employee) - Why?

[jai.anand@aol.in]

Thanks OllieB!

I'm greatly thank for your ineterset in helping me!

Answers:

In row 9 there is a different time shown for Jim (i.e. 09:50:46) while there is no distinguishing information when compared to rows 7 and 8 because in this step I need to pick the time from the Base sheet for all the corresponding enteries in the Ouput sheet. The currently formula solution picks it in that manner.

Alice: Yes, you are absolutely right on that. It should 16:03:43. Seems to be some Formula glitch.

Thanks again

[jai.anand@aol.in]

Do you think? It would be better If we take it step by step. I can post my somewhat original file with dummy data. That way it will take care of everything.

Ideally, the first step is to get the time populated from my Base sheet before moving on the the second phase.

Please advise...

Thanks again!

[OllieB]

Check this one

[jai.anand@aol.in]

Superb. My preliminary test works out great.

Here’s my original file. The columns on which the logic is based are colored in “Yellow”.

Could you please help me on the changes that code will require to append the time.

You are a genius Sir. You’ve made my day

Thanks!

[OllieB]

Jay,

What is your requirement for this one?

- Copy from Base to Output?
- All records, aggregate, update time in Output?

Please explain for example the first two rows in Output, all columns are identical except for the 'expected result'

PS.
- and what about your initial requirement to also match in case 4 out of five columns? Which one is the optional column now?
- if matching entries based on output worksheet, what if time entry exists for an entry type that does not yet exist on the output worksheet?

[jai.anand@aol.in]

Dear OllieB,

This is the original file format.

My request is to be able to populate time from Column F of the Base Sheet for the exact match (case sensitive) against the exact match (case sensitive) row in Ouput Sheet in Column A.

Column T "Expected Result" is added by me to be able to give the result to help understand the required outcome in Column A.

Please let me know if I may not be clear.

Thanks!

[OllieB]

So the requirement for full/partial matching is no longer necessary? And please explain first two rows in data provided for the output worksheet

[jai.anand@aol.in]

Dear OllieB,

The 1 and the 2 rows are identical. Yes, the expected time is different, as the individual went out twice during the day for the same purpose. So if the enteries are identical then and available in Base then seprate time needs to be updated in Ouput as in Base, but if there is only one entry in Ouput but multiple in Base (excatly identical) then the latest time needs to be updated in Output.

Thanks!

[jai.anand@aol.in]

The enteries need to match exactly. The columns highlighted in yellow are the basis to determine if it is an exact match or not.

Do let me know if you have more queries.

Best regards,
Jai

[jai.anand@aol.in]

The enteries need to match exactly. The columns highlighted in yellow are the basis to determine if it is an exact match or not.

Do let me know if you have more queries.

Best regards,
Jai

[OllieB]

Jai, sorry but I do not understand how I am supposed to populate different values for the first two rows as they have identical key values. The logic you had me built was to determine the maximum timestamp for a given key (combination of columns). Now I am supposed to have two values for the same key?

[jai.anand@aol.in]

I agree on that OllieB.

I was thinking if it would be possible to built in some logic that can populate time in Ouput Sheet Column A from Base i.e. if the 2 entries are exactly identical in Output but there are 2 different times in the Base sheet for the same exact match (case sensitive)

Example: In case of Row 2 and 3 Output sheet the Exact enteries (case sensitive) also appear in Row 26 and Row 27 of Base sheet with different times. Would it be possible to pull the times for these exact cases in Row 2 and 3 or Row 3 or 2?

Either Row 2 in Output Sheet can get 13:11:56 PM as the time and Row 3 can get 07:28:43 AM as the time or vice versa (Order isn't important) as long as the enteries exactly match (case sensitive).

Please advise!

Manny thanks!

[OllieB]

Jai,

Everything is possible, but I am slightly annoyed to be asked to first build solution A, and then learn that the final requirement is something different (no partial key matching needed and only find the max value if the source worksheet does not contain consolidated lines, else assign details values sequentially). The logic needed for the two solutions is quite different

[jai.anand@aol.in]

I’m terribly sorry OllieB. Will keep this in mind. Sorry about the disconnect!

I'll put everting clear here again:


• Base - Has higher number of entries than Output.
• Output - All the row entries are present in the Base sheet as well

I need your assistance to be able to populate time in Column A of the Output sheet from Column F of the Base sheet. The time per row entry has to be populated based on the exact match (case sensitive).

1. The columns in Base and Output Sheet highlighted in yellow need to be the basis for matching the exact rows
2. The required result has been updated in the attachment manually in Colum S of the Output sheet (highlighted in Green). Column S is not a part of the report
3. If exact values per highlighted rows appear we need to assign details values sequentially

Hope this defines my request better, and will help you put in your efforts in the right direction.

Once again thanks a lot for your help and patience. Sorry about any confusion that I may have caused!

Look forward to your assistance!

I promise I’ll be more careful once we progress to the second step. Hope you understand!


Best regards,
J

[OllieB]

I have written a solution based on the previous version of the workbook.

If for a given key only one row exists on the Output worksheet the time shown in column A will be the highest value encountered on the Base worksheet for that particular key. If for a given key multiple rows exist on the Output worksheet, values from the Base worksheet for the same key are assigned sequentially (firs value to first row, second value to second row, etc).

If for an entry on the BASE worksheet no corresponding entry can be found on the Output worksheet an error message will be displayed to the user showing the key value and the record will be skipped (processing continues), similarly for a multi-row type of key, if not enough detail lines are provided on the output worksheet an error message will be displayed, the record from the base worksheet will be skipped, and processing continues.

I have also found three errors which I think are wrong in your example
row 126 - no matching row on BASE, yet expected result shown
row 131 - no matching row on BASE, yet expected result shown
row 132 - no matching row on BASE, yet expected result shown

The number of rows found on the BASE worksheet without a matching line on the Output worksheet is substantial.

[jai.anand@aol.in]

Awesome OllieB!

You are a superstar:-) This is out of this world!

I'll run this now and I'm more than 2000% sure this will be absolutely in line with my request.

I'm assuming that we can now also move on the last phase of this. Please suggest if I can post the last bit with logics.

God your are amazing. No words are enough!

My sincere thanks and apologies for the disconnect earlier.

Best reagrds,
J

Thanks, Thanks, Thanks, Thanks, Thanks, Thanks, Thanks, Thanks....

[OllieB]

Jai,

Ok to post the second part of your request. Please be concise and specific. Examples are very much appreciated.

[jai.anand@aol.in]

Sure Sir!

Give me sometime. I'll bring out everything as clearly as possible with examples. Really appreciate your help and paitence!

Thanks again!

[jai.anand@aol.in]

Dear OllieB.

This is working fantastically!

Just one piece of advice that I need on this one…

My data is huge 8700 rows in Base and 5525 in Ouput. I’m pressing enter to speed up the process. Do you think it may be the reason for it skipping some of the entries. I’ve double checked it and sometimes it is ignoring a few rows.

Example 2 exactly same rows in the Base and Output sheet are present with different times. It is populating the time for the highest one but not the other.

Please advise if I’m doing something incorrectly.

I am also working on getting the exact steps for the last step in the interim.

Many, many thanks

[OllieB]

I will need to see the actual data (you can use zip files) to determine what is happening. High volumes should not be a problem.

[jai.anand@aol.in]

Hey OllieB.

I figured that out. It was because of the extra spacing in those enteries. So we are all good with the first phase!

You are magical man!

I'll post the second bit after careful examination by tomorrow. Thanks a lot

[OllieB]

Jai, we could add trim() statements to a avoid this problem in the future

[jai.anand@aol.in]

That would be beautiful!!!

I'm working on getting you absolutely correct logics for the last phase will share it by tomorrow morning.

Promise to keep it as accurate and clear as possible.

[OllieB]

revised version. added trim statements and parameter for text compare on dictionary

[jai.anand@aol.in]

Take a bow Sir!

How do you do that? I want to learn, learn, learn...Be my coach please...

I don't have access to a computer . First thing tomorow will look at this superb piece.

Repying using phone.

Regards,
Jai

[jai.anand@aol.in]

Dear OllieB!

I’ve tested this and it is working absolutely fine. The trim Function did the job fantastically. The processing speed is tremendous. It looks like some magic is happening in front of ones eyes. WOW…

I’m afraid though there are 2 small concerns not because of the solution but because of the data:

• Reference Row 2 & 3 in the Output Sheet – During testing I added another row in the Base Sheet with exactly the same details. It as rightly coded populated time sequentially, but what it did was ignored the latest time in Row 4 in the Base Sheet. The required output in Row 2 & 3 in the Output Sheet is 08:22:03 & 13:11:56. I think the logic will need to be in descending sequentially. Please advise your thoughts...
•Secondly, is due to some special characters that appear between the reports. This is a issue at the system end because of which the special characters get added. Reference Row 5 in Output Sheet Ško ted is not matching Sko ted in Row 5 of Base Sheet. Can the special characters be ignored somehow?

I’ve attached the sheet with the rows mentioned above highlighted. That is it for this one. I’m all good after this. Have extensively tested your “Awesome Solution”. (Note: Please use the attachment in post #35. The one on this one appears to have some format issues)

Thanks a lot!

I'm posting the last phase with Logics after this one!

[jai.anand@aol.in]

The Final Step: Last Phase – 100% Sure of the Logics required on this one:

Tab 1: The Ouput Sheet is then used to derive my "Final Report" based on the following logics:

1. A column to sum the values of breaks (C_T_S) is to be added and it needs to sum the # in the C_T_S column for each case sensitive entry – basis (Column O). Expected results populated in Column U for first couple of entries.
2. Once this is done then from the output sheet (case sensitive entry – basis Column O) rows with the latest time need to be exported to a separate tab of the same excel or a new CSV / XL file whichever is easy – I can later convert it into a CSV.

3. However, there is an exception to bullet 2:

A. In case the status is “Official Work” then this should be exported for the exact match (case sensitive entry – basis Column O) even though this may not be the latest time for the exact case sensitive Column O.
B. If there are multiple “Official Work” for the same exact (case sensitive entry – basis Column O) then only the one with the latest “TIME” should be exported.

4. The remaining needs to be exported to a separate tab of the same excel / macro or a new CSV / XL file whichever is easy as “Batch”
Technically, the Base Data isn’t required after the time population in Step 1.

I ‘m extremely thankful for all your help and expertise to help me execute this task!

Kindest regards,
Jai

[jai.anand@aol.in]

Dear OllieB,

Just noticed there were some format issues with the last attachment in the time column. Here is the latest one with proper time format.

Hope to hear from you!

Thanks!

[OllieB]

Jai,

A few problems. First of all I did not have any date formatting problems in the version I submitted to you (all were hh:mm:ss), but I do have problems in the version you submitted in post #5, as I now have AM/PM formatted dates for some rows whereas hh:mm:ss for others?

The second problem I now see is the entry for Jan Mas Ree. On the Data worksheet you have three rows whereas on the Output worksheet you have two rows. I noticed that in the prediction column you only show the two highest timestamps. This is not a logic we agreed on or discussed, as we did agreed on sequentially assigning values in case there was more than one row on the output worksheet. Due to an error in the code this did not show up in previous runs.

I furthermore feel that the values shown in column T (predicted) now frequently do not match with the values my routine populates in column A. For example the timestamp shown by you in column T for row 23 is not a value I can find for this user.

[jai.anand@aol.in]

Dear OllieB.

Give me 15 mins I'll go to a friends house to check it. Its. 6:40am here and I don't have a system. I'll get back right now.

Thank you for everything.

Best regards,

Jai

[jai.anand@aol.in]

Dearest OllieB.

Point 1 - That is absolutely right. Your version is the correct one. There were no issues with it. The correct format is hh:mm:ss as in your submitted version. Re-exporting of the data may be the reason for the disconnect. So lets use the version you submitted to avoid any further disconnects.

Point 2 - I did create a new entry while testing Jan Mas Ree on the Output Sheet. The prediction column does show the 2 highest stamps as my data needs to get time sequentially in descending order (if there are exactly same matches) like in this case. This will give me the highest time for the exactly same match. If this isn't done, and the time is assigned the way it is now then I'll end up ignoring the highest time stamp for the exact same match like it was happening earlier. That is the key for my final report.

Point 3 - The values in Column T (predicted) will not match due to the changes in the data I made manually while testing. My bad, I should have removed / updated it while sharing.

In all scenarios, I've never seen such quality of "Work" Have been think about it all weekend!

Thank you so very much OllieB. Your are a genius by all means. I'm a newbie but learning, I quite well understand how you would be feeling for these hiccups that are getting in, but believe me I'm making a lot of effort to learn and will surely be more particular on all my future posts.

In short I'm truly amazed seeing your superb piece of work. Hope you'll understand and accept my apologies.

Best Regards,
J

[OllieB]

Jai,

So what are going to do with the fact that there were 3 rows for a given key, and only two rows on the output sheet? Are we just going to ignore the detail row with the lowest time by sorting the DATA worksheet on timestamp?

If so:
- why do we then have two detail rows and not just one summary row like we have for other keys?
- as we are aggregating CTD values in step 2, would we not miss an entry if we just ignore one of the detail lines?

With respect to the diacritic characters that are sometimes in the text, I unfortunately have no solution for you. These characters are specific per language of origin and I would not know what to replace them with.

Let me know how you want to proceed.

[jai.anand@aol.in]

Dear OllieB,

1) Yes, so technically, I'll only need the latest time sequentially in the descending order i.e. if there are 4 entries for the exact match in Base but only 3 in the Output then we'll need to ignore the entry with the lowest time stamp in the Base.

2) The reason for the details rows is that I get this data form several places I then compile it and some of these have 2 or more entries for the exact match in Output and even higher number of entries in the Base. I need to get the latest time calculations done based on the entries in the Output sheet, which unluckily doesn't have time and hence I'm trying to get this solved with your assistance.

3) Since the Final Report is Based on the Output sheet all the data that needs to be covered is available in the Output sheet. The Base has some extra data that needs to be ignored. The base sheet always has higher number of rows the extra ones aren't required. Moreover, the Base sheet has no role after the time has been populated.

4) No worries for the special characters. If they can't be ignored I can take a look at them manually, as I have been doing. These can be filtered after running your solution by filtering blanks as the time won't get populated.

Also I think by aggregating CTD you are referring to sum the Values in Column Q.

I can't thank you enough for your patience and help!

Thank you!

[OllieB]

Jai, have a look at the attached workbook

[OllieB]

Jai,

we can replace the strange character automatically but we would have to loop through every cell that may contain this value, for every row and for every strange character you want to replace. Do you want this, and if so can you provide a list of the character substitutions that you are currently performing manually?

[jai.anand@aol.in]

Super!!! I'm on the highway, just stopped by to check. I'll be infront of the comp in the next 30 mins.

Super excited to look at it:-) Thank you Sir!

[jai.anand@aol.in]

Awesome OllieB.

I tried this it is fantastic and working superbly! Speed is tremendous… The solutions works well with the data I provided.

However, I faced one error when I put the data from today’s import On the following line:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Request you to please advise why this may be happening.

[jai.anand@aol.in]

I Think we can do without it for now. There aren't many cases. So I'll manage on this front.

Thank you!

[OllieB]

However, I faced one error when I put the data from today’s import On the following line:

Please Login or Register  to view this content.

Request you to please advise why this may be happening.

Hi Jai,

the above line attempts to set some some sort variables for the BASE worksheet. The error message appears to imply that object xlsBASE is not valid. Is the worksheet still called BASE. Have you tried closing/opening the workbook? I will have a look on my side as I am a bit dubious about the AutoFilter part and now think it should not be there.

[jai.anand@aol.in]

Yes, I have made no changes to your worksheet. The names are still the same. I only changed the data from my import today. Yes, I did try closing/opening the workbook but the same error.

It works well with the data already in there in your versions. But on my run of the new data it doesn't.

Thank you!

[OllieB]

Jai,

I removed the AutoFilter part of the sort statement and added a full range selection. It appears to be working. New version attached.

PS. with your new import there was probably no filter set on the data, hence the error message on the AutoFilter part.

[jai.anand@aol.in]

Dearest OllieB,

The first part is working seamlessly. Awesome will be a very small word!!! Thank you can never be enough…Everything is falling perfectly in place with it.

On the second part I’m facing a little error. I’m currently studying the script to determine how it is working. My sample data seems to be working alright. (the one in your version V3.1) However, when I added today’s import it worked but only moved few rows as Batch and did not sum them up. May be its to do with the import of data again.

I’ll check and study it further and comeback by tomorrow to seek any further advice or close the thread as “Solved”. Hopefully, it will be the latter…

You Rock Sir, You Rock!

Thanks a lot!!!!

[OllieB]

Ok. Will wait

[jai.anand@aol.in]

Hey OllieB,

I'm currently testing the last bit. Here are my observations:

1). I think we'll need to introduce a "Trim" in Column O. The imported data has some spaces which is why all the data isn't getting Exported / Batched correctly in StageTwoProcess. This again is a problem with the data import. No probs with your solution.

2). Secondly, I think this part needs your expertise: The output sheet (case sensitive entry – basis Column O). Rows with the latest time need to be exported to a separate tab. However, this exceptions needs a relook:

In case the status is “Official Work” then this should be exported for the exact match (case sensitive entry – basis Column O) even though this may not be the latest time for the exact case sensitive Column O. The others need to be batched even if they have a later time. - For now I see both getting populated as EXPORT. May be I'm wrong. Please advise.

Thanks again & Good night!

[OllieB]

Trim on column O is no problem. With regards to your second point I believe this is exactly how I have written the code. Is the data in the Output worksheet still sorted on Column O? If not, that would cause a problem and we would need to introduce a sort, if it is, then i would need an example if the error.

[OllieB]

Ps spaces in column O might also cause the symptom mentioned at point 2 as this would cause a fake level break and a reset of finding the highest time. Let's add the Trim processing first and then check if the error is still there

[jai.anand@aol.in]

The data isn't sorted. I pasted it as it was recieved from my direct import. So that's probably the reason. I've manually sorted it and seems to work well.

Great simply Great!!!

[OllieB]

Jai, ok I will add trim processing and a sort of the output worksheet. A new version will follow in a little bit

[jai.anand@aol.in]

You are out of this world OllieB. Simply great!

Unfortunately, I'll have to leave as this is my friends computer and I have been encroaching it since morning. I'll look out for the your new version and will check it througly.

It's 12:36am here and got to vacate this place.

You have a great time ahead. My best wishes

I'll be on the phone though, just in case you have any questions...

[OllieB]

Jai, and here it is.

Added:
- trim$ processing
- forced sorting in both stage 1 and stage 2
- found and solved a bug in determining which row should go to export and which one to batch

[jai.anand@aol.in]

AWESOME!!!

Thank you so very much OllieB.

I'll run this today against my data import today, and come back with the outcome which I'm sure will be "Solved". I wish this Forum had the option of marking threads as "Solved to Perfection"

I'm thinking its also time for me to buy a Computer. I can't wait to check this magical solution out. Guess will need to ensure an early access to a machine. Wish I had one could have been on it real time 24X7...

Such amazing work. Yet again a request...Be my coach please...

Cheers!!!

[OllieB]

Hi Jai,

Please remember to mark your thread as SOLVED and to click on the star icon (bottom of my posts on the left side) if you are happy with my contribution.

[jai.anand@aol.in]

Dearest OllieB.

Happy would be a very small word! I'm Super Duper Happy. I'll commence testing now and will get back in a few hours. As I mentioned earlier, I seriously wish there was an option of marking threads as "Solved to Perfection" I would have done that

I can’t thank you enough for this help!

Best regards,
Jai

[jai.anand@aol.in]

Dearest OllieB.

My test has been running fine. Just 2 points that need your assistance:

1) In stage 2 after the sorting is done the last row is getting Batched even when it should be an Export. May be its to do something with the sorting of the data.
2) One slight enhancement – can we introduce a “Proper Case” function in Column F of Output and Column O of Base.

I think we’ll be all good with this one then. Cheers!

Thanks a ton!

[OllieB]

- Conversion to Proper Case added for both columns as requested
- Added closing level break processing for last rows on Output worksheet

new version attached

[jai.anand@aol.in]

WOW, WOW, WOW!!!!!!

Running my final test and will come back in the next 2-hours to mark this as "SOLVED"

Thank you Sir!

[jai.anand@aol.in]

Dearest OllieB,

THIS IS IT....I am extremely thankful and over joyed with the solution provided. This is simply out of this world. A superb piece of work.

I have no words to express how thankful I am. You are a genius by all means Sir!

It’s been a great learning. I also would like to apologies for a couple of changes that became inevitable, and appreciate all your help and patience to help me solve this monster

Closing this thread as Solved, and taking home a lot of learning.

You know what except those few special characters in some of the lines everything is working absolutely brilliantly. All I need to do is compile data for the 2 (Base and Output) and run your amazing piece of work. I am extremely happy! Thank you, Thank you!

Best regards,
Jai