A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Comparing multiple columns



 
 
Thread Tools Display Modes
  #1  
Old December 22nd, 2005, 09:17 PM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default Comparing multiple columns


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

  #2  
Old December 22nd, 2005, 11:12 PM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default Comparing multiple columns

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


  #3  
Old December 22nd, 2005, 11:14 PM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default Comparing multiple columns

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


  #4  
Old December 23rd, 2005, 02:17 PM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default Comparing multiple columns

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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Add New Field to DB Karen Database Design 7 October 19th, 2005 08:03 PM
Convert 1 row of data into Multiple columns Mohoney General Discussion 1 August 25th, 2005 12:36 PM
Multiple columns in a pivot table lyric2002 General Discussion 2 May 26th, 2005 05:53 PM
Can't select multiple columns across screenview Rick Charnes General Discussion 4 May 13th, 2005 05:07 PM
Sumif over multiple columns Josh O. Worksheet Functions 1 February 15th, 2005 05:33 PM


All times are GMT +1. The time now is 08:10 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.