[SOLVED] Compare two files with the same ID

Shark Man · #1 07-10-2009, 12:15 AM

Hello Experts
I have one xls sheet with employee’s information, at column A I have ID number -called Mater.
I have second xls sheet with update employee’s information, at column A i have the same ID number - called update file.

I need help with VBA code that update the Master file according to the update file (The ID at column A is the key between the files)and mark with color the update.

The file contain more the 20,000 records and with 10 columns

See example

Thanks Alot
Shark

JBeaucaire · #2 07-10-2009, 02:59 AM

Why are the two cells highlighted?
I see no discernable difference between the Master File and Update file. Have you oversimplified the example data to the point we can't see your need?
Can you provide a more varied Master data set showing ALL the hurdles that have to be dealt with.
Can you provide BEFORE/AFTER examples so it's clear what you really need this macro to do?
Will the update file really have ALL the same # of rows and IDs as the master file? If so, couldn't you just use the update file to replace the master file as is? If not, show a better example of how your update file might actually look.

Shark Man · #3 07-10-2009, 07:37 AM

Hello Thanks for your help

I need the update the file because there is same column that will not going to change.

I attached new xls file with the process, I hope it will be more helpful

Thanks

Shark

JBeaucaire · #4 07-10-2009, 11:47 AM

I ask a series of numbered questions and you don't address them all, so I'm left to figure it out on my own.

1. Why are the two cells highlighted?
So anytime something from the update files changes something in the Master File, you want the change highlighted in the master file?

5. Will the update file really have ALL the same # of rows and IDs as the master file? If so, couldn't you just use the update file to replace the master file as is?

6. You call these "Sheets"...but are they actually separate workbooks? If so, what is the actual name of the master workbook and the name of the update file?

Shark Man · #5 07-10-2009, 01:40 PM

Hello

Thanks for your time

1. you are right
5. Yes the update file contain the same ID and numbers of #.
I cant use the update file as the new Master becuase there is few columns that will not be update and located only at the Master.
6.these are seperate workbooks with the name "Master" and "Update"

Thanks

Shark

JBeaucaire · #6 07-10-2009, 03:03 PM

Run this macro from inside the Master file. It will open the UPDATE file from your default directory, update the values and close the update file. If you don't want the update file closed, take out that line of code near the bottom.

Code:

Option Explicit
Sub UpdateMasterFile()
'JBeaucaire  (7/10/2009)
Dim LR As Long, Rw As Long, i As Long
Application.DisplayAlerts = False
Application.ScreenUpdating = False

Workbooks.Open ("Update.xls")
LR = Range("A" & Rows.Count).End(xlUp).Row

    For i = 2 To LR
        Rw = 0
        On Error Resume Next
        Rw = WorksheetFunction.Match(Range("A" & i), Workbooks("Master.xls").Sheets("Sheet1").Range("A:A"), 0)
        
        If Rw > 0 Then
            With Workbooks("Master.xls").Sheets("Sheet1").Cells(Rw, "B")
                If Not .Value = Cells(i, "B") Then
                    .Value = Cells(i, "B")
                    .Interior.ColorIndex = 6
                End If
            End With
            With Workbooks("Master.xls").Sheets("Sheet1").Cells(Rw, "C")
                If Not .Value = Cells(i, "C") Then
                    .Value = Cells(i, "C")
                    .Interior.ColorIndex = 6
                End If
            End With
            With Workbooks("Master.xls").Sheets("Sheet1").Cells(Rw, "D")
                If Not .Value = Cells(i, "D") Then
                    .Value = Cells(i, "D")
                    .Interior.ColorIndex = 6
                End If
            End With
        
        End If
    Next i

Workbooks("Update.xls").Close False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Welcome to the Excel Forum

Forum Statistics