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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 04:33 PM |