VBA to update Sheet 2 the Location from Sheet 1. Thank you very much

[jojo4santa]

*********THANKS MILLIONS JohnTopley for your SUPER POWERFUL, QUICK CODES. You're Super Genius. Your code works like a MIRACLE. Thanks a lot for your time & talent.
I tried to add the reputation for your kind, quick help but the system does not allow me as it says the same post while it is actually different issues.
I wish I could add millions stars to reputation. BIG THANKS JOHN TOPLEY******

The problem is now SOLVED BY JOHN TOPLEY. THANKS MILLIONS JOHN !

Hi Super Coders

Hope you have a great new week.
I urgently need your talent please.

Attached are 2 Excel files with different numbers of Column Headings.
However, both have 2 common Column Headings: ClientID (Primary Key) & Location.
The number of Column Headings in both File 1 and File 2 could be changing more over time.

File 1 (>300,000 records) - downloaded file that comes with 6 Protected Sheets which contain the Location data that File 2 needs.
File 2 (>3,000 records) - where The column named Location needs to be UPDATED.

My job is to update column Location of File 2 for the matching Client ID.

*******SPECIAL THANKS TO JohnTopley FOR THE 1ST POST. THIS IS RE-POST with additional info. thanks again John ***********


Thank you so much for your time & help

[JohnTopley]

Try

in H2 and copy down

Please Login or Register  to view this content.

[jojo4santa]

Thank you so much John Topley for your super quick, magic code. It works great. I had added reputation to you. thanks millions

[JohnTopley]

You're welcome and thank you for the rep.

[jojo4santa]

Hi John

Hope you have another easy day
Thank you so much John for always helping people. Your time & knowledge are greatly appreciated.
I forgot to mention that Sheet1 is actually one of the 6 Sheets (>300,000) that originallly downloaded from the database. Let's call it File 1
In the other word, my job is download the 300,000 records into Excel that comes with 6 sheets together
I have to compare with another file with about 3,000 records. Let's call it File 2.
File 1 & File 2 has different numbers of headings (as the original attached sample) with 2 common headings Client ID, Location.
I need to update the Location of File 2 from File 1 for the matching Client ID

So instead of compare the 2 Sheets (Sheet 1 to Sheet 2), now compare 6 Sheets (File 1 with >300,000 files) to Sheet7 (File 2 >3,000 records, smaller file)

Thanks a lot again John.
I will post it the new one.

[JohnTopley]

I forgot to mention that Sheet1 is actually one of the 6 Sheets (>300,000 files [.. rows in TOTAL?])

... somewhat of an "oversight" !

Please post sample files as this is likely to require a VBA rather than a simple formula solution.

And remove "SOLVED" from thread as others may address the problem.

[jojo4santa]

Hi Super Coders

Hope you have a great new week.
I urgently need your talent please.

Attached are 2 Excel files with different numbers of Column Headings.
However, both have 2 common Column Headings: ClientID (Primary Key) & Location.
The number of Column Headings in both File 1 and File 2 could be changing more over time.

File 1 (>300,000 records) - downloaded file that comes with 6 Protected Sheets which contain the Location data that File 2 needs.
File 2 (>3,000 records) - where The column named Location needs to be UPDATED.

My job is to update column Location of File 2 for the matching Client ID.


Thank you so much for your time & help

[JohnTopley]

Please Login or Register  to view this content.

Assumes "File 1" is open.

[jojo4santa]

Hi John
Ali requested me not to repost as new thread. So I modify the old post. Hopefully, you see the OLD post with additional info.
Hope you have another wonderful day John.
Thanks a lot for your time & kind, quick help.

[jojo4santa]

*********THANKS MILLIONS JohnTopley for your SUPER POWERFUL, QUICK CODES. You're Super Genius. Thanks a lot for your time & talent. I wish I could add millions stars to reputation. BIG THANKS JOHN TOPLEY******
I tried to add reputation but the message says You can't add reputation to the same post twice.
John, I can't thank you enough for keeping helping people. You're such great gifted coder. God bless !

[jojo4santa]

Hi John
Hope you are well.
My File 1, File 2 have changed in total columns. I tried to figure out with the codes but it did not work for me. Your code is miracle but when the total of columns change, I dont know what part of the code i should modify. I tried to change 6 to 5 because of the Total Sheets had changed.
Could you please kindly help me? thanks a lot. I attached the

[jojo4santa]

Hi John
Hope you are well. I am testing the email

[JohnTopley]

I can't do anything without seeing the files.

[JohnTopley]

If number of sheets in "File 2" has changed then amend code as:

Please Login or Register  to view this content.

For anything else, post files.

[jojo4santa]

Hi John

Thank you so much again for your kind, quick help as always. I have your code modified as instructed but received run-time error pointing at:
dic.Add Key:=str, Item:=b(j, loc2)
Files attached.

How could I fix this?
Thanks a lot

[JohnTopley]

Please Login or Register  to view this content.

Problem caused by heading blank entries in row 1 of "File 1"

And there was an error in the code!

[jojo4santa]

Thanks millions for your super quick help again John. Your modified code works like a MIRACLE. I tried to add reputation to your hard work but receive the message "You must spread some Reputation around before giving it to JohnTopley again."
Wishing you a wonderful, prosperous week. God bless you!

[JohnTopley]

Glad to know all is O.K.

[jojo4santa]

Hi John

Hope you are well.
Your code for the 2 files sample work like miracle BECAUSE file 1 sample is small.
However, in reality my File 1.xlsx > 300,000 records (in protected view with 5 Sheets after downloaded from our server) and the code could not work.
Do you know any solution to make the code work for the large file?

Thank you so much John

[jojo4santa]

Hi John

Hope you are well.
Your code is such a miracle. However, I have difficulty as my file has 5 sheets with >300,000 records. Each sheet contains 65528 records and everytime, the code can't be executed. I had to cut down the records. Is there a better way John?
Thanks a lot for your time & kind help as always

[JohnTopley]

the code can't be executed

.... meaning it takes a long time to execute?

The key issue here (I think!) is the number of clients: the macro uses Scripting Dictionary, which in my limited experience of using, is not very effecient when there are many "keys" i.e clients in this case.

How many unique clients are there? From your intitial post, just over 3000 (?)

Question: why are we searching through 300,000 records each time when the 3000+ clients are going to be relative static ???

Please post a file with (at least) one sheet with a large number of rows.

[JohnTopley]

A different approach:

The Client ID and Location from each sheet in "File 1" is copied to sheet "Temp" in "File 2 V2". Duplicate "Client ID" are removed, sorted by "Cllent ID" and added to "Scripting Dictionary" which is then used to populate Sheet1 in "File 2 V2".

Please try this and let me know the result

Still happy to test with a large file if required.

Please Login or Register  to view this content.

[jojo4santa]

Hi Super Genius John

Thank you so much again for your SUPER POWERFUL CODES. I have tested your codes on the files with > 300,000 records (this is growing every single hour). Both Module1, Module2 codes are working PERFECTLY LIKE MIRACLES. You can't imagine how you dry up my tears each day.
Both Files come with different Headings, Total of Columns, Total of Records. File 2 (close to 40,000 records) & File 1 (>300,000 records) that I download from server. However, both Files have 2 Identical Columns with the Headings (ClientID & Location)
My job is updating the Location from File 1 to Location in File 2.
Now with your miracles, my nightmare job turns to heaven. Thank you millions for your kindness for always quickly helping me. May God bless you and yours John. Thanks a lot. I just added millions * to your reputation. YOu're the BEST!!!

[JohnTopley]

Glad everything is now OK. Thank you for the rep.

[jojo4santa]

Hi John

I hope you have a restful weekend.
You have helped me with the powerful code.
However, our users entered data in the wrong column. Data should be entered in Location found in column Employee.
Could you please kindly help me add in your previous code with additional lines.

We have 2 Excel Files. Both have different headings but have 2 same columns : Column ClientID (Primary key) & Location.
File 1.xlsx (>3000,000 records) with 5 Sheets with 3 important Headings: ClientID, Location, Employee.
File 2.xlsm - 1 Sheet only

What I need the program to do is :

Copy all Location from File 1 to File 2 once ClientID matches.
If Location is blank then copy data from column Employee instead.
I attach the 2 files again with your previous working codes.

Thanks a lot again John

[JohnTopley]

Please Login or Register  to view this content.

Output in sheet "TEMP" in "File 1" but equally could update the sheets 1:5.

[jojo4santa]

Hi John

I wish your day goes well.
I stayed up late last night trying to figure out how to run your code.
Attached are the 2 Excel Files & the screen shot of the BEFORE & AFTER running your previous miraculous code.

My task is filling in the Location for the ClientIDs in File 2.
As you can see from the attached. Both Files have different Column Headings but have the same 2 Headings Columns which are ClientID & Location.

File 1 - has 5 Sheets with > 300,000 records.
File 2 - has only 1 Sheet with approximately 40,000 records.

------Please try to run your previous code in File 2 attached. It works perfect.
However, the Location of ClientID #4 is left blank due to users' data entry errors. Instead of entering data in the Location, they entered it into Employee.
Is it possible if you can edit your previous PERFECT code (attached in File 2) that when Location is blank, copy data from Employee into Location in File 2?
We are only interested in obtaining finding the Location for the MATCHING ClientIDs in File 2 - which are ClientIDs: 1, 2, 3, 4, 5 hilited yellow.
FILE 2 is the File we need to complete the missing data for Location.
File 1 is where the data extracted from for the Location in File 2 once ClientID matches. Only the Location for Yellow hilted ClientIDs are needed to be filled.


Thank you so much again for your kind help

[JohnTopley]

Please Login or Register  to view this content.

Writes data for "File 2": both files open.

[jojo4santa]

Hi John

I added your reputation for your quick help.