# 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..

Thank you.

Diddi

Wednesday, April 17, 2013 1:21 PM

• 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
Cell.Interior.ColorIndex = 3
End If
Next

For Each Cell In Worksheets("CompareSheet#2").UsedRange
Cell.Interior.ColorIndex = 3
End If
Next
End Sub

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

For Each Cell In Worksheets("CompareSheet#2").Range("A1:J50")
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..."
While Worksheets.Count > 1
Worksheets(2).Delete
Wend
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.

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