Compare data changes in 2 workbook.

[ADAMC]

Hi all,

Thank you in advance for any help!

I have 2 workbooks. ‘Book 1’ and ‘Book 2’

What I need to do is compare the same 2 columns on each row but the rows aren’t I. The same order in each workbook. So

So run through column A in Book 1 find that value in column A Book 2 then compare the value of column G in the same row and if it’s different to the value in column g of Book 1 highlight it in Book 1

Is this something that can be done maybe with a formula? I don’t know but I am not great with VBA.

Thanks

 

[Roderick_E]

Hi all,

Thank you in advance for any help!

I have 2 workbooks. ‘Book 1’ and ‘Book 2’

What I need to do is compare the same 2 columns on each row but the rows aren’t I. The same order in each workbook. So

So run through column A in Book 1 find that value in column A Book 2 then compare the value of column G in the same row and if it’s different to the value in column g of Book 1 highlight it in Book 1

Is this something that can be done maybe with a formula? I don’t know but I am not great with VBA.

Thanks

Do you have a UID (unique identifier) reference that can be used to determine which row should be compared to which? This way, order doesn't matter.

 

[ADAMC]

Do you have a UID (unique identifier) reference that can be used to determine which row should be compared to which? This way, order doesn't matter.

To be honest I am not sure. Column A book 1 is a reference number in this format A123456 this will be the same in column A of book 2 but new lines are often added in the second book so I can’t say the row will be the ale as book 1 and book 2.
Column G Is formatted like this in both books ABC123 if that’s any help ?

 

[mumps]

Place this macro in a regular module in Book1. Save the workbook as a macro-enabled file so that its extension changes to "xlsm". Book2 should be saved with an "xlsx" extension. I assumed that the sheets in both workbooks are named "Sheet1". Change the sheet names and workbook names in the code to suit your needs. Make sure that both workbooks are open and run the macro.

Sub CompareCols()
    Application.ScreenUpdating = False
    Dim Rng As Range, RngList As Object
    Dim shWB1 As Worksheet
    Set shWB1 = Workbooks("Book1.xlsm").Sheets("Sheet1")
    Dim shWB2 As Worksheet
    Set shWB2 = Workbooks("Book2.xlsx").Sheets("Sheet1")
    Set RngList = CreateObject("Scripting.Dictionary")
    Dim foundVal As Range
    For Each Rng In shWB2.Range("A2", Range("A" & shWB2.Rows.Count).End(xlUp))
        If Not RngList.Exists(Rng.Value) Then
            RngList.Add Rng.Value, Nothing
        End If
    Next Rng
    For Each Rng In shWB1.Range("A2", Range("A" & shWB1.Rows.Count).End(xlUp))
        If RngList.Exists(Rng.Value) Then
            Set foundVal = shWB2.Range("A:A").Find(Rng, LookIn:=xlValues, lookat:=xlWhole)
            If shWB2.Range("G" & foundVal.Row) <> shWB1.Range("G" & Rng.Row) Then
                shWB1.Range("G" & Rng.Row).Interior.ColorIndex = 3
            End If
        End If
    Next Rng
    RngList.RemoveAll
    Application.ScreenUpdating = True
End Sub

 

[ADAMC]

Thank you. I’ll give this a try now and get back to you.

Many thanks!!