0

Thanks to all the wonderful members of this forum. I need some help please. I want to compare few cells in one row with another row in the same sheet. eg. I want to see if C1:G1 contains the same value as C2:G2

Any help is highly appreciated.

Thanks again

Community
  • 1
  • 1
ichayan
  • 25
  • 1
  • 1
  • 9
  • 1
    possible duplicate of [How to compare two entire rows in a sheet](http://stackoverflow.com/questions/19395633/how-to-compare-two-entire-rows-in-a-sheet) – Dmitry Pavliv Jun 29 '14 at 13:39
  • @simoco he only wants certain cells though, not the whole row. – Tanner Jun 29 '14 at 16:46
  • 2
    @tannman357, it doesn't matter, logic would be the same.. just change entire row to range – Dmitry Pavliv Jun 29 '14 at 16:56
  • Oh...yeah that would work. I wrote some code that does his task anyways so I guess it doesn't matter.... – Tanner Jun 29 '14 at 17:03
  • Thank you for the replies, @tannman357 I have seen this and I tried range with my humble knowledge and it didn't work for me. Please be kind to convert the code for a range. – ichayan Jun 30 '14 at 16:47

2 Answers2

0

In cell C3 insert the formula: =IF(C1=C2, 1,0) and extend it to cell G3. "1" indicated the match, "0" correspondingly, a mismatch. You can do it vice versa, like: =IF(C1=C2,0,1) and then apply SUM operator to cells: SUM(C3:G3). "0" indicates full match, any non-zero indicates number of cells with mismatches.

Or you can do it in VBA:

Function IsEqual() As Boolean

If Range("C1").Value & Range("D1").Value & Range("E1").Value & Range("F1").Value & Range("G1").Value = Range("C2").Value & Range("D2").Value & Range("E2").Value & Range("F2").Value & Range("G2").Value Then
    IsEqual = True
Else
    IsEqual = False
End If

End Function

Rgds,

Alexander Bell
  • 7,842
  • 3
  • 26
  • 42
  • Thank you for your response, I need to loop through the entire worksheet to find matching rows, so inserting formula into a cell is not ideal for this situation. – ichayan Jun 30 '14 at 16:48
  • I've extended my answer pertinent to your requirement: see the modified post. Rgds, – Alexander Bell Jun 30 '14 at 17:02
0

This will do what you want!

Sub check()
Dim checkArr1, checkArr2 As Variant
Dim l As Long
Dim ElementsSame As Boolean

checkArr1 = Array(Range("C1"), Range("D1"), Range("E1"), Range("F1"), Range("G1"))
checkArr2 = Array(Range("C2"), Range("D2"), Range("E2"), Range("F2"), Range("G2"))

ElementsSame = True
For l = 0 To 4 'C to G is 5 (shifted left because array)
    If checkArr1(l) <> checkArr2(l) Then
        ElementsSame = False
        Exit For
    End If
Next l

If ElementsSame = True Then
    'do the stuff you want to do
    Range("A1") = 1 'I did this to error trap
Else
    Range("A1") = 0 'I did this to error trap
End If

End Sub
Tanner
  • 548
  • 8
  • 20
  • I will try this code. I need to loop through the whole sheet to how many rows are there with the same values in this range. – ichayan Jun 30 '14 at 16:52