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

*********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

Try

in H2 and copy down

Formula:

=INDEX(Sheet1!$O$2:$O$6,MATCH(Sheet2!$A2,Sheet1!$A$2:$A$6,0))

You're welcome and thank you for the rep.

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.

Option Explicit

Sub demo()
Dim wb1 As Workbook, wb2 As Workbook
Dim ws As Worksheet
Dim a, b, ar
Dim loc1 As Long, loc2 As Long, i As Long, j As Long, n As Long
Dim str
Dim dic As Object

Set wb1 = ThisWorkbook
Set wb2 = Workbooks("file 1.xlsx")

With wb1.Sheets("Sheet1")
    a = .[A1].CurrentRegion
    loc1 = Application.Match("Location", .Rows("1:1"), 0)
End With

Set dic = CreateObject("Scripting.Dictionary")
    n = 0
    For i = 1 To 6
    
         With wb2
         
            Set ws = .Sheets("Sheet" & i)
            loc2 = Application.Match("Location", ws.Rows("1:1"), 0)
            b = ws.[A1].CurrentRegion
            
            For j = 2 To UBound(b, 1)
                str = b(j, 1)
                If Not dic.exists(str) Then
                    n = n + 1
                    dic.Add Key:=str, Item:=b(j, loc2)
                End If
            Next j
            
         End With
            
     Next i

    For i = 2 To UBound(a, 1)
        str = a(i, 2)
        If dic.exists(str) Then a(i, loc1) = dic.Item(str)
    Next i
    
wb1.Sheets("sheet1").[A1].Resize(UBound(a, 1), UBound(a, 2)) = a

End Sub

Assumes "File 1" is open.

I can't do anything without seeing the files.

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

Dim loc1 As Long, loc2 As Long, i As Long, j As Long, n As Long, ns As Long

ns = Worksheets.Count

For i = 1 To ns  ' Replaces i=1 to 6

     With wb2

For anything else, post files.

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!!!

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

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

Option Explicit


Sub demo()
Dim wb1 As Workbook, wb2 As Workbook
Dim ws As Worksheet, ws1 As Worksheet
Dim a, b, ar
Dim loc1 As Long, loc2 As Long, i As Long, j As Long, n As Long, ns As Long, nr As Long, lr As Long
Dim str, rng As Range
Dim dic As Object

Application.ScreenUpdating = False

Set wb1 = ThisWorkbook
Set ws1 = ThisWorkbook.Sheets("temp")
ws1.UsedRange.Offset(1, 0).ClearContents

With wb1
    ns = .Worksheets.Count
    For i = 1 To ns
          Set ws = .Sheets(i)
          If ws.Name <> "TEMP" Then
           With ws
             lr = .Cells(Rows.Count, "A").End(xlUp).Row
             a = .[A1].CurrentRegion.Offset(1, 0)
             For j = 1 To UBound(a, 1)
                If a(j, 15) = "" Then a(j, 15) = a(j, 17): a(j, 17) = "" ' Move "Employee" to "Lcation" and clear "Employee"
             Next j
             nr = ws1.Cells(Rows.Count, "A").End(xlUp).Row + 1
             ws1.Cells(nr, 1).Resize(UBound(a, 1), 17) = a
          End With
    End If
    Next i
End With

Application.ScreenUpdating = True

End Sub

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

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

Option Explicit
Sub demo()
Dim wb1 As Workbook, wb2 As Workbook
Dim ws As Worksheet, ws1 As Worksheet, ws2 As Worksheet
Dim a, b, ar
Dim loc1 As Long, loc2 As Long, i As Long, j As Long, n As Long, ns As Long, nr As Long, lr As Long
Dim str, rng As Range
Dim dic As Object

Application.ScreenUpdating = False

Set wb1 = ThisWorkbook
Set wb2 = Workbooks("File 2.xlsm")


Set ws2 = wb2.Sheets("Sheet1")

ws2.UsedRange.Offset(1, 0).ClearContents

With wb1
    ns = .Worksheets.Count
    For i = 1 To ns
          Set ws = .Sheets(i)
          If ws.Name <> "TEMP" Then
           With ws
             lr = .Cells(Rows.Count, "A").End(xlUp).Row
             a = .[A1].CurrentRegion.Offset(1, 0)
             For j = 1 To UBound(a, 1)
                If a(j, 15) = "" Then a(j, 15) = a(j, 17): a(j, 17) = "" ' Move "Employee" to "Location" and clear "Employee"
             Next j
             nr = ws2.Cells(Rows.Count, "A").End(xlUp).Row + 1
             ws2.Cells(nr, 1).Resize(UBound(a, 1), 17) = a
          End With
    End If
    Next i
End With

Application.ScreenUpdating = True

End Sub

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