Comparing two worksheets

[Chaitanya.Kalapala]

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.

 

[Bernard Liengme]

look at Chips stuff on duplicates at www.cpearson.com
best wishes

 

[Guest]

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

 

[Guest]

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?

 

[Guest]

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.