Q:

Excel Compare two sheets and update sheet 1

Okay  - This has been asked multiple times, but asking again for best possible solution : 
I have two excel files (not sheets).  the first excel sheet is very huge and has close to 200,000 records. One of the column (Gender) is corrupted and i have to fix it.
I have a second excel file and it has only around 200 records - these have the correct value for those ones which are messed up. 
for eg:

and this is the file that has correct values with only around 200 records (only the corrupted ones). 

Now i need  a macro , where i need to find these exact 200 records out of 200,000 records (by employee id) and replace the Gender value with correct one.
i found something similar here.  but i dont want to loop 200,000 records 200 times. feels like a performance overhead. 
is there a better option?  
I am thinking an ideal solution would be  

Loop through 200 items and use employee id per loop
Take that employee id and do a "Find" operation in the Employee id column of the master excel 
If found, replace the Gender column value 

would there be any other better solution? Any inputs is gladly appreciated

A:

One way to do this through VBA is to just loop through the 200 corrections, comparing the ID with the MATCH function to find the row it belongs on, as opposed to a second loop (a second loop through 20000 would take ages like you say).
For the below sub I have copied and pasted the 200 table into columns 5:7 of the 20000 table, you can either automate this part easily enough, or just put in the correct sheet references for each part of the code.
I've also put in a checking line to make sure there IS a match for the current ID from the small table, otherwise it'd throw up an error. You could put an ELSE in front of the END IF in this error catch to highlight any ID's which weren't actually found. Here's the code, hope this method helps!
Sub replace_things()
With ActiveSheet
For x = 2 To 200 'Change this to however many is in the small table
    cur = .Cells(x, 5) 'Defined cur as ID from small table
    aMatch = Application.WorksheetFunction.CountIf(.Range("A:A"), cur) 'Check to see there's a match in large table
    If aMatch > 0 Then ' if there's a match then...
        theRow = Application.WorksheetFunction.Match(cur, .Range("A:A"), 0) 'get the row number the match is actually on
        .Cells(theRow, 3) = .Cells(x, 7) 'when row is found, replace with the relevant value from col7 (col3 of small table)
    End If
Next x
End With
End Sub

