Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing two worksheets
I have seen that there are already other discussions regarding this and
I have gone through quite a few but couldn't quite make sense of it or use the assistance provided there. I have two worksheets. They have multiple columns and I need to compare the two sheets to look for any data thats missing in either sheet or any row that has different info for an employee and then highlight where the difference is arising. Can anyone help about me with this? Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing two worksheets
look at Chips stuff on duplicates at www.cpearson.com
best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email wrote in message ps.com... I have seen that there are already other discussions regarding this and I have gone through quite a few but couldn't quite make sense of it or use the assistance provided there. I have two worksheets. They have multiple columns and I need to compare the two sheets to look for any data thats missing in either sheet or any row that has different info for an employee and then highlight where the difference is arising. Can anyone help about me with this? Thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing two worksheets
The answer to your question will depend on the fact whether a row in one
worksheet represents the same entity as the row in the second worksheet. If this is so you can simply use a formula. For instance if column C in Sheet1 has to be compared with Column G in Sheet2 then you can use an empty column in Sheet2 and use the formula in Row2 (that is in X2 assuming X is the unused column) you will enter. =if(Sheet1!C2=Sheet2!G2,"","Error") Then copy this formula in as many rows as you like. Alok " wrote: I have seen that there are already other discussions regarding this and I have gone through quite a few but couldn't quite make sense of it or use the assistance provided there. I have two worksheets. They have multiple columns and I need to compare the two sheets to look for any data thats missing in either sheet or any row that has different info for an employee and then highlight where the difference is arising. Can anyone help about me with this? Thanks in advance. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing two worksheets
I have a similar problem. But the rows do not match. My very informally
written program takes the first record first field in the first spreadsheet and reads down the same field in the second spreadsheet and simply colors the errored sheets. The problem is that the fields are alpha numeric and I have about 27000++ records on each sheet. I did a test run on one record and it took 15 minutes to run down the corresponding column's 27000++ records on the other spreadsheet. At this rate it would take over a year to do one column. I have ten such columns. It seems the if A.cells(J,1) = B .cells(k,1), j = 27000, k = 27000 comparison in my program is doing a character by character comparison. Is there any alternative to the if A.Cells= B.cells statement? -- VSExcel "Alok" wrote: The answer to your question will depend on the fact whether a row in one worksheet represents the same entity as the row in the second worksheet. If this is so you can simply use a formula. For instance if column C in Sheet1 has to be compared with Column G in Sheet2 then you can use an empty column in Sheet2 and use the formula in Row2 (that is in X2 assuming X is the unused column) you will enter. =if(Sheet1!C2=Sheet2!G2,"","Error") Then copy this formula in as many rows as you like. Alok " wrote: I have seen that there are already other discussions regarding this and I have gone through quite a few but couldn't quite make sense of it or use the assistance provided there. I have two worksheets. They have multiple columns and I need to compare the two sheets to look for any data thats missing in either sheet or any row that has different info for an employee and then highlight where the difference is arising. Can anyone help about me with this? Thanks in advance. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing two worksheets
One trick I have used in the past is to read a range into memory by
v = range(...).value this helps very much since reading a cell by cell is a much slower process. So you could try reading both the ranges into memory and see if that speeds up the process and by how much. "VSExcel" wrote: I have a similar problem. But the rows do not match. My very informally written program takes the first record first field in the first spreadsheet and reads down the same field in the second spreadsheet and simply colors the errored sheets. The problem is that the fields are alpha numeric and I have about 27000++ records on each sheet. I did a test run on one record and it took 15 minutes to run down the corresponding column's 27000++ records on the other spreadsheet. At this rate it would take over a year to do one column. I have ten such columns. It seems the if A.cells(J,1) = B .cells(k,1), j = 27000, k = 27000 comparison in my program is doing a character by character comparison. Is there any alternative to the if A.Cells= B.cells statement? -- VSExcel "Alok" wrote: The answer to your question will depend on the fact whether a row in one worksheet represents the same entity as the row in the second worksheet. If this is so you can simply use a formula. For instance if column C in Sheet1 has to be compared with Column G in Sheet2 then you can use an empty column in Sheet2 and use the formula in Row2 (that is in X2 assuming X is the unused column) you will enter. =if(Sheet1!C2=Sheet2!G2,"","Error") Then copy this formula in as many rows as you like. Alok " wrote: I have seen that there are already other discussions regarding this and I have gone through quite a few but couldn't quite make sense of it or use the assistance provided there. I have two worksheets. They have multiple columns and I need to compare the two sheets to look for any data thats missing in either sheet or any row that has different info for an employee and then highlight where the difference is arising. Can anyone help about me with this? Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
comparing two worksheets | Excel Worksheet Functions | |||
Comparing on 2 worksheets | Excel Worksheet Functions | |||
comparing from two worksheets | Excel Discussion (Misc queries) | |||
Comparing worksheets | Excel Worksheet Functions | |||
Comparing worksheets | Excel Programming |