How to compare two differnet cell ranges to see if they are the sa

[Tom]

I have two data charts on the same worksheet with each chart containing 14
columns and 79 rows. Is there a way to compare the two cell by cell and then
output a "same" or "different" if and cell in the a row is different between
the two data charts? Does this have to be done cell by cell or can it be done
by ranges?

 

[Per Jessen]

Hi

As I don't know your data layout I have created this example, which
hopefully can help you.

Sub CompareRng()
Dim Test As Boolean
Dim Same As String
Dim Diff As String

Test = True

' Both data sets start in row 1
' Data set A start in column A
' Data set B start in column T

For r = 1 To 79
For c = 1 To 14
If Cells(r, c).Value <> Cells(r, c + 19).Value Then
Test = False
End If
Next
If Test = True Then
If Same = "" Then
Same = r
Else
Same = Same & ", " & r
End If
Else
If Diff = "" Then
Diff = r
Else
Diff = Diff & ", " & r
End If
End If


Test = True
Next
msg = MsgBox("Rows " & Same & " are the same" & vbLf _
& vbLf & "Rows " & Diff & " are different", _
vbInformation, "Row check")
End Sub


Regards,

Per

 

[Paul C]

I have done something like this in two different ways

1 - Create a third chart and use this formula
=if(A1=K1,"Same","Different")

Use the upper left cell of each chart in place of A1 and K1

You could also use Conditional Formatting on one or both chart

for example in cell A1 use the Conditional Format

Condition 1
Cell Value - is equal to - =K1
(pick some format like Green text or shading)

Condition 2
Cell Value - is not equal to - =K1
(pick some format like Red text or shading)

If you make sure that there are no absolute references ($) in the formula
you can use the format painter to copy this to the entire table.

 

[Bernie Deitrick]

Tom,

1)

Use a formula like

=IF(A2=M2,"Same","Different")

were A2 is the upper left cell of table 1, and M2 is the upper left cell of table two.

Then copy that over 14 columns and down 79 rows.

2)

Use conditional formatting.

To highlight the cells that are different, select the first table, then use Format / Conditional
Formatting... "Cell Value is" "Not equal to" and then select the upper left cell of the second
table, and press F4 until the $s disappear, like =M2. Then set the fill to red or some other
highlighting color.

HTH,
Bernie
MS Excel MVP

 

[Aviashn]

I know this has been answered, however, an alternate solution that
accounts for different workbooks, ranges, or relative positions is
below. Another benefit is that it only takes one cell. Just enter as
a normal function in any cell.

I'd appreciate any comments as I've never attempted to solve this
problem before.

Public Function CompareTwoRanges(rOne As Range, rTwo As Range) As
String

Dim lRdiff As Long
Dim lCdiff As Long
Dim arOne As Variant
Dim arTwo As Variant
Dim rCell As Range

If rOne.Row > rTwo.Row Then
lRdiff = rOne.Row - rTwo.Row

Else
lRdiff = rTwo.Row - rOne.Row
End If

If rOne.Column > rTwo.Column Then
lCdiff = rOne.Column - rTwo.Column

Else
lCdiff = rTwo.Column - rOne.Column
End If
CompareTwoRanges = "They match."

For Each rCell In rOne
If rCell.Value <> rTwo.Parent.Cells(rCell.Row + lRdiff,
rCell.Column + lCdiff).Value Then
CompareTwoRanges = "Discrepancies Exist."
Exit Function
End If
Next rCell

End Function