• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to compare values from 2 Excel files and update changed value

ThrottleWorks

ThrottleWorks

Excel Ninja
Hi,
I have two excel files.
First is parent file, second is child file.
Both files have same structure. Same number of worksheets.
Child file is replica of parent file. Only difference is parent file has all the records whereas child file has few records.
In each worksheet there is one unique record.
Child files gets updates manually. Parent file needs to have updated record from the child file.
I need to update only few columns from each worksheet,
for example, from sheet1 I need to update column Y:Z only, for sheet 2 it might be X:Y in that sensce
I used vLookUp but it is too time consuming and Excel is getting crashed.

Please see below example for your reference.
Parent file = worksheet 1
unique key = Range A10 = ABC123
Range Y2 = 100
Range Z2 = 1000

Child file = worksheet 1
unique key = Range A2 = ABC123
Range Y2 = 200
Range Z2 = 2000

When we run the macro, output should be like below
Parent file = worksheet 1
unique key = Range A10 = ABC123
Range Y2 = 200
Range Z2 = 2000

Can anyone please help me in this.
 
I have tried below loop too. However loop is more time consuming.

Code: 
TempLr = Sht_Child.Range("A" & Rows.Count).End(xlUp).Row
    Set TempRng = Sht_Child.Range("N2:N" & TempLr)
    TempLr = Sht_Master.Range("A" & Rows.Count).End(xlUp).Row
    For Each TRng In TempRng
        FoundKey = ""
'        TRng.Select
        FoundKey = Sht_Master.Range("N2:N" & TempLr).Find(what:=TRng, LookIn:=xlValues, lookat:=xlWhole)
        If FoundKey <> "" Then
            MyRow = Sht_Master.Range("N2:N" & TempLr).Find(what:=TRng, LookIn:=xlValues, lookat:=xlWhole).Row
            Sht_Master.Cells(MyRow, 32) = Sht_Child.Cells(TRng.Row, 32)
        End If
    Next TRng
 
ThrottleWorks
Only You can help with this ...
As You know...
... without a sample Excel-files,
It will be some challenges to verify as well as test.

When we run the macro, output should be like below ...
... do that output really look like Your given layout?
Your #2 reply ... could give a different image of that.
 
You must log in or register to reply here.
Back
Top