Need to compare 2 excel files for differences

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

Answer 1

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.

Answer 2

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.

Answer 3

Very simple solution. Love it.

Thank you.

Answer 4

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

Answer 5

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.

Answer 6

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

Answer 7

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.

Answer 8

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.   

Answer 9

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.")

Answer 10

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