Comparing multiple columns

[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

[PeterAtherton]

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

"daarun" wrote:

>
> 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
>
>
> --
> daarun
> ------------------------------------------------------------------------
> daarun's Profile: http://www.excelforum.com/member.php...o&userid=28954
> View this thread: http://www.excelforum.com/showthread...hreadid=495607
>
>

[PeterAtherton]

By the way if the range is increased later the macro adjusts it self

Regards
Peter

"daarun" wrote:

>
> 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
>
>
> --
> daarun
> ------------------------------------------------------------------------
> daarun's Profile: http://www.excelforum.com/member.php...o&userid=28954
> View this thread: http://www.excelforum.com/showthread...hreadid=495607
>
>

[CLR]

If this is something you might have to do frequently, I would recommend using
Jim Cone's fine commercial Add-in called XL Companion. It's available at

http://www.realezsites.com/bus/primitivesoftware

Vaya con Dios,
Chuck, CABGx3




"daarun" wrote:

>
> 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
>
>
> --
> daarun
> ------------------------------------------------------------------------
> daarun's Profile: http://www.excelforum.com/member.php...o&userid=28954
> View this thread: http://www.excelforum.com/showthread...hreadid=495607
>
>