none
Need to compare 2 excel files for differences RRS feed

  • Question

  • Hello,

    I have one large excel with multiple columns of data in it. we are maitaing some data in to this. one person updated this on April 12th with some data. and with this latest data somebody updated again on April 15th so we have 2 copies now April12th and April 15th(which has 12th and latest data). so i just want to compare 2 excel files to find out what changes have meen made in April 15th..

    Can anyone please help me on this?

    Thank you. 


    Diddi

    Wednesday, April 17, 2013 1:21 PM

Answers

  • Put copies of the two sheets into one workbook, name them April12 and April15. Then insert a third sheet. In cell A1 of the third sheet, type the formula

    =April12!A1=April15!A1

    and copy down and across to match the size of your data. You could apply conditional formatting to show cells with the value False as red to help you visually identify differences.

    If the order of the columns or rows has changed (or rows or columns have been inserted/deleted), large blocks of cells will be FALSE, so that will help you identify what is going on.

    Wednesday, April 17, 2013 1:55 PM

All replies

  • Put copies of the two sheets into one workbook, name them April12 and April15. Then insert a third sheet. In cell A1 of the third sheet, type the formula

    =April12!A1=April15!A1

    and copy down and across to match the size of your data. You could apply conditional formatting to show cells with the value False as red to help you visually identify differences.

    If the order of the columns or rows has changed (or rows or columns have been inserted/deleted), large blocks of cells will be FALSE, so that will help you identify what is going on.

    Wednesday, April 17, 2013 1:55 PM
  • Here are a few ideas to compare SHEETS (not files).

    Sub Compare2Shts()
    For Each Cell In Worksheets("CompareSheet#1").UsedRange
    If Cell.Value <> Worksheets("CompareSheet#2").Range(Cell.Address) Then
    Cell.Interior.ColorIndex = 3
    End If
    Next

    For Each Cell In Worksheets("CompareSheet#2").UsedRange
    If Cell.Value <> Worksheets("CompareSheet#1").Range(Cell.Address) Then
    Cell.Interior.ColorIndex = 3
    End If
    Next
    End Sub


    Sub CompareAnother2Shts()
    For Each Cell In Worksheets("CompareSheet#1").Range("A1:J50")
    If Cell.Value <> Worksheets("CompareSheet#2").Range(Cell.Address) Then
    Cell.Interior.ColorIndex = 3
    End If
    Next

    For Each Cell In Worksheets("CompareSheet#2").Range("A1:J50")
    If Cell.Value <> Worksheets("CompareSheet#1").Range(Cell.Address) Then
    Cell.Interior.ColorIndex = 3
    End If
    Next
    End Sub

    ********  ********  ********  ********  ********  ********  ********  ******** 

    Sub FindDupes() 'assuming both sheets are in same book and book is open
    Dim sht1 As Worksheet
    Dim sht2 As Worksheet
    Dim cell1 As Range
    Dim cell2 As Range
    Dim str As String
        str = InputBox("Type name of first sheet")
        Set sht1 = Worksheets(str)
        str = InputBox("Type name of second sheet")
        Set sht2 = Worksheets(str)


        sht1.Range("A65536").End(xlDown).Activate
        Selection.End(xlUp).Activate
        LastRowSht1 = ActiveCell.Row

        sht2.Activate
        sht2.Range("A65536").End(xlDown).Activate
        Selection.End(xlUp).Activate
        LastRowSht2 = ActiveCell.Row

        sht1.Activate
        For rowSht1 = 1 To LastRowSht1
            If sht1.Cells(rowSht1, 1) = "" Then Exit Sub
            For rowSht2 = 1 To LastRowSht2
                If sht1.Cells(rowSht1, 1).Value = sht2.Cells(rowSht2, 1).Value Then
                    sht1.Cells(rowSht1, 1).Interior.ColorIndex = 3
                    sht2.Cells(rowSht2, 1).Interior.ColorIndex = 3

                End If
            Next
        Next
        sht1.Cells(1, 1).Select
    End Sub

    ********  ********  ********  ********  ********  ********  ********  ******** 

    Sub checkrev()

    With Sheets("Sheet1")
    Sh1LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    Set Sh1Range = .Range("A1:A" & Sh1LastRow)
    End With
    With Sheets("Sheet2")
    Sh2LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    Set Sh2Range = .Range("A1:A" & Sh2LastRow)
    End With

    'compare sheet 1 with sheet 2
    For Each Sh1cell In Sh1Range
    Set c = Sh2Range.Find( _
    what:=Sh1cell, LookIn:=xlValues)
    If c Is Nothing Then
    Sh1cell.Interior.ColorIndex = 3
    Sh1cell.Offset(0, 1).Interior.ColorIndex = 3
    Else
    If Sh1cell.Offset(0, 1) <> c.Offset(0, 1) Then
    Sh1cell.Interior.ColorIndex = 6
    Sh1cell.Offset(0, 1).Interior.ColorIndex = 6
    End If
    End If
    Next Sh1cell
    'compare sheet 2 with sheet 1
    For Each Sh2cell In Sh2Range
    Set c = Sh1Range.Find( _
    what:=Sh2cell, LookIn:=xlValues)
    If c Is Nothing Then
    Sh2cell.Interior.ColorIndex = 3
    Sh2cell.Offset(0, 1).Interior.ColorIndex = 3
    Else
    If Sh2cell.Offset(0, 1) <> c.Offset(0, 1) Then
    Sh2cell.Interior.ColorIndex = 6
    Sh2cell.Offset(0, 1).Interior.ColorIndex = 6
    End If
    End If
    Next Sh2cell

    End Sub

    ********  ********  ********  ********  ********  ********  ********  ******** 

    Sub TestCompareWorksheets()
        ' compare two different worksheets in the active workbook
        CompareWorksheets Worksheets("Sheet1"), Worksheets("Sheet2")
        ' compare two different worksheets in two different workbooks
    '    CompareWorksheets ActiveWorkbook.Worksheets("Sheet1"), _
            Workbooks("WorkBookName.xls").Worksheets("Sheet2")
    End Sub



    Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
    Dim r As Long, c As Integer
    Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
    Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
    Dim rptWB As Workbook, DiffCount As Long
        Application.ScreenUpdating = False
        Application.StatusBar = "Creating the report..."
        Set rptWB = Workbooks.Add
        Application.DisplayAlerts = False
        While Worksheets.Count > 1
            Worksheets(2).Delete
        Wend
        Application.DisplayAlerts = True
        With ws1.UsedRange
            lr1 = .Rows.Count
            lc1 = .Columns.Count
        End With
        With ws2.UsedRange
            lr2 = .Rows.Count
            lc2 = .Columns.Count
        End With
        maxR = lr1
        maxC = lc1
        If maxR < lr2 Then maxR = lr2
        If maxC < lc2 Then maxC = lc2
        DiffCount = 0
        For c = 1 To maxC
            Application.StatusBar = "Comparing cells " & Format(c / maxC, "0 %") & "..."
            For r = 1 To maxR
                cf1 = ""
                cf2 = ""
                On Error Resume Next
                cf1 = ws1.Cells(r, c).FormulaLocal
                cf2 = ws2.Cells(r, c).FormulaLocal
                On Error GoTo 0
                If cf1 <> cf2 Then
                    DiffCount = DiffCount + 1
                    Cells(r, c).Formula = "'" & cf1 & " <> " & cf2
                End If
            Next r
        Next c
        Application.StatusBar = "Formatting the report..."
        With Range(Cells(1, 1), Cells(maxR, maxC))
            .Interior.ColorIndex = 19
            With .Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .Weight = xlHairline
            End With
            With .Borders(xlEdgeRight)
                .LineStyle = xlContinuous
                .Weight = xlHairline
            End With
            With .Borders(xlEdgeLeft)
                .LineStyle = xlContinuous
                .Weight = xlHairline
            End With
            With .Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .Weight = xlHairline
            End With
            On Error Resume Next
            With .Borders(xlInsideHorizontal)
                .LineStyle = xlContinuous
                .Weight = xlHairline
            End With
            With .Borders(xlInsideVertical)
                .LineStyle = xlContinuous
                .Weight = xlHairline
            End With
            On Error GoTo 0
        End With
        Columns("A:IV").ColumnWidth = 20
        rptWB.Saved = True
        If DiffCount = 0 Then
            rptWB.Close False
        End If
        Set rptWB = Nothing
        Application.StatusBar = False
        Application.ScreenUpdating = True
        MsgBox DiffCount & " cells contain different formulas!", vbInformation, _
            "Compare " & ws1.Name & " with " & ws2.Name
    End Sub

    ********  ********  ********  ********  ********  ********  ********  ******** 

    Sub Match()

    r1 = Worksheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row
    r2 = Worksheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Row

    Set r3 = Worksheets("sheet1")
    Worksheets("sheet2").Range("B2").Select
    For a = 2 To r2
    For i = 2 To r1
    If Cells(a, "A") = r3.Cells(i, "A") Then
    temp = r3.Cells(i, "B")
    te = te & "," & temp
    Else
    End If
    Next i
    Cells(a, "B") = te
    te = ""
    Next a
    End Sub


    Sub Match2()
    Dim myCon As String
    Dim myCell As Range
    Dim cell As Range
    For Each cell In Sheet2.Range("A2:A10")
    myCon = ""
    For Each myCell In Sheet1.Range("A1:A15")
    If cell = myCell Then
    If myCon = "" Then
    myCon = myCell.Offset(0, 1)
    Else
    myCon = myCon & ", " & myCell.Offset(0, 1)
    End If
    End If
    Next myCell
    cell.Offset(0, 1) = myCon
    Next cell
    End Sub

    ********  ********  ********  ********  ********  ********  ********  ******** 

    Sub Duplicates()
    ScreenUpdating = False

    'get first empty row of sheet1

    'find matching rows in sheet 2
    With Sheets("Masterfile")
    RowCount = 1
    Do While .Range("A" & RowCount) <> ""
    ID = Trim(.Range("A" & RowCount))
    'compare - look for ID in Sheet 2
    With Sheets("List")
    Set c = .Columns("A").Find(what:=ID, _
    LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
    End With
    If c Is Nothing Then
    .Range("B" & RowCount) = "No"
    Else
    .Range("B" & RowCount) = "Yes"
    End If

    RowCount = RowCount + 1
    Loop
    End With

    ScreenUpdating = True

    End Sub


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Saturday, May 11, 2013 4:26 AM
  • Very simple solution. Love it.

    Thank you.

    Wednesday, December 18, 2013 6:18 PM
  • It really worked for me.  It was quick and easy.  When it was done I filtered the columns and checked each column in turn for False.  When the column showed a false I uncjeched the True value and only the differences were shown.  I got the row numbers went back to the 2 source spreadsheets to spot the differences.

    Thanks

    Thursday, February 27, 2014 11:23 PM
  • So, can you guys click 'propose as answer' if Bernie or I helped you out??!!!

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, February 28, 2014 2:41 AM
  • Hi,

    Can this code will pick the sheets from two different files.

    If so, Where I need to change in the Code. Please suggest!

    Thanks!

    Kiran


    Wednesday, February 4, 2015 12:26 AM
  • Did you get this straightened out, or not?  If you still need a solution, try this.

    http://download.cnet.com/Beyond-Compare/3001-2242_4-10015731.html?hlndr=1


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Wednesday, February 4, 2015 4:59 AM
  • Hi there

    I know this solution should work for me - but I am such a novice that I want to ask for some help on my problem!!

    I have a sheet with 3 colomns. 

    A = Document numbers

    B = Document names

    C = Dates documents will be delivered to me

    Every month the contractor does a filter (they extract from prima vera into an exel sheet) for documents coming up to be delivered and gives me an indication of which documents will come and what date. ( I then have an enormous sheet of people that need to review the documents, and I have if statements and comparisons to create a graphic representation of when groups can expect documents an how big they are etc)  My problem comes in when a new revision of the project schedule is issued and the dates for document delivery is updated. 

    The problem is, that some documents have been delivered (so they dont appear on the new sheet), some dates change and sometimes the doc description change!

    I want to right something that :

    Sheet 1 contains old info with old dates, Sheet 2 new info

    1. A sub that can - Take each doc number in sheet 2, and check that the date and title is the same as docs in sheet 1, if not, then paste the info from sheet 1 into sheet 2. Colour these green to show they have been updated.

    2. A sub that can check wich document numbers doesnt appear in sheet 2, and colour those red in sheet 1.  I can then go away to see why the red ones are red - probably because it has been delivered.

    3. Sheet 1 will then be the up to date list of all docs.   

    Friday, November 4, 2016 7:20 AM
  • You can use simple formulas to do the check.

    On Sheet2, use a formula like this in D2, copied down.

    =IFERROR(TRIM(IF(B2<>VLOOKUP(A2,Sheet1!A:C,2,FALSE),"Name Changed","") & " " & IF(C2<>VLOOKUP(A2,Sheet1!A:C,3,FALSE),"Date Changed","")),"Document not listed in old sheet.")

    Friday, November 4, 2016 4:07 PM
  • You can Also use some Dedicated Tool like Synkronizer for Compare 2 Excel Columns or Sheets. It is Provide 100% Accurate Result.

    Try This:

    https://www.synkronizer.com/compare-excel-tables-features/compare

    Wednesday, January 9, 2019 6:06 AM