Comparing multiple columns

  • Thread starter Thread starter daarun
  • Start date Start date
D

daarun

hi all,

I need to compare two excel sheets(sheet1,sheet2) where in i have the
employee hours worked for a fortnight. One sheet is generated using an
exe file and the other is the one that has got uploaded to backend.
Need to find whether the hours worked match one to one.

For e.g

My sheet 1 would have data like this....(without the header)

0 4 8 6 8
6 0 5 3 0
8 8 8 8 0
4 4 4 2 0
8 8 8 8 0
11 11 10 9 0
0 0 0 0 0


(55 records)

Sheet 2

0 4 8 6 8
6 0 5 3 0
8 8 8 8 0
4 4 4 2 0
8 8 8 8 0
11 11 10 9 0
0 0 0 0 0
(55 records)

How do i make sure the datas that have got uploaded to db and the one
created by the exe are the same, if there is any difference it should
have an non-numeric value.

Thanks
Arun
 
The following macro will do this for you.

Dim rng1 As Range, rng2 As Range
Dim i As Long, i2 As Long, j As Integer, j2 As Integer
Dim nr As Long, nr2 As Long, nc As Integer, nc2 As Integer

Sub compare()
Dim msg As String
' set the ranges to compare
Set rng2 = Range("A1").CurrentRegion
Set rng1 = Sheets("Sheet1").Range("A1").CurrentRegion
nr2 = rng2.Rows.Count
nc2 = rng2.Columns.Count
nr = rng1.Rows.Count
nc = rng1.Columns.Count
' xhack thet the number of
If nr <> nr2 Then
MsgBox "The number of rows is different"
Exit Sub
ElseIf nc <> nc2 Then
MsgBox "The number of Columns is different"
Exit Sub
End If
For i = 1 To nr
For j = 1 To nc
If Cells(i, j) <> rng1.Cells(i, j) Then
'Display cells that do not agree
msg = msg & " " & Cells(i, j).Address
Cells(i, nc2 + 2) = msg
End If
Next
Next
End Sub

It compare that both range are the same size, and tells you if they are not
equal.
Then it compares each cell in sheet 2 with the same cell on sheet 1. If
therre is a discrepancy then the cell(s) are shown two columns right of the
range in sheet2.

Press Alt + F11, then choose Insert, Module and copy the code into the
module. You can press F8 to step through the code, or F5 to run Quickly
through.

To use the code again (in the same workbook) choose Tools, Macro, select the
macro and click run.

Regards
Peter
 
Back
Top