Hello,

I have two worksheets which have similar values but are repeating. How do i
compare the two and get the items which are not reconciled. Eg

Sheet 1 Sheet 2
Column A Column A
50 50
100 25
25 50
50 100
100 300
25
25
100
200

I need to compare these two sheets and identify the reconciling items. I am
unable to use the vlookup function because of items being repeated.

Try this macro:
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

Also.........

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

Forgot where I got the second one; I think I picked it up on this DG a while
back...


Hope this helps you out.

Regards,
Ryan---

--
RyGuy


"Darshan" wrote:

Hello,

I have two worksheets which have similar values but are repeating. How do i
compare the two and get the items which are not reconciled. Eg

Sheet 1 Sheet 2
Column A Column A
50 50
100 25
25 50
50 100
100 300
25
25
100
200

I need to compare these two sheets and identify the reconciling items. I am
unable to use the vlookup function because of items being repeated.

Hi,
The macro is not running properly both 1 and 2. I get an error message for
debugging for (For Each Cell In Worksheets("CompareSheet#1").UsedRange).
Where am i going wrong.




"ryguy7272" wrote:

Try this macro:
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

Also.........

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

Forgot where I got the second one; I think I picked it up on this DG a while
back...


Hope this helps you out.

Regards,
Ryan---

--
RyGuy


"Darshan" wrote:

Hello,

I have two worksheets which have similar values but are repeating. How do i
compare the two and get the items which are not reconciled. Eg

Sheet 1 Sheet 2
Column A Column A
50 50
100 25
25 50
50 100
100 300
25
25
100
200

I need to compare these two sheets and identify the reconciling items. I am
unable to use the vlookup function because of items being repeated.

Hi!
for comparing spreadsheets you can try this add-in:
http://www.office-excel.com/excel-ad...eadsheets.html
Hope it helps!

Best regards,
Eugene
"Darshan" wrote in message
...
Hello,

I have two worksheets which have similar values but are repeating. How do
i
compare the two and get the items which are not reconciled. Eg

Sheet 1 Sheet 2
Column A Column A
50 50
100 25
25 50
50 100
100 300
25
25
100
200

I need to compare these two sheets and identify the reconciling items. I
am
unable to use the vlookup function because of items being repeated.

Try this (note: the range is specifically defined...adjust to suit your needs)
Sub Compare2Shts()
For Each Cell In Worksheets("CompareSheet#1").Range("A1:J10")
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:J10")
If Cell.Value < Worksheets("CompareSheet#1").Range(Cell.Address) Then
Cell.Interior.ColorIndex = 3
End If
Next
End Sub



Regards,
Ryan---
--
RyGuy


"Evgeny Grischenko" wrote:

Hi!
for comparing spreadsheets you can try this add-in:
http://www.office-excel.com/excel-ad...eadsheets.html
Hope it helps!

Best regards,
Eugene
"Darshan" wrote in message
...
Hello,

I have two worksheets which have similar values but are repeating. How do
i
compare the two and get the items which are not reconciled. Eg

Sheet 1 Sheet 2
Column A Column A
50 50
100 25
25 50
50 100
100 300
25
25
100
200

I need to compare these two sheets and identify the reconciling items. I
am
unable to use the vlookup function because of items being repeated.