compare all data in one worksheet to another

  • Thread starter Thread starter duketter
  • Start date Start date
D

duketter

Excel 2007 - I would like to compare all the data in one worksheet
(50+columns and 800 rows) to another worksheet and identify any differences.
The worksheets are set up the exact same way regarding column names, number
of columns, etc.

I know there is at least one difference in one of the cells in this entire
worksheet but there may be more. How can I do this?
 
duketter said:
Excel 2007 - I would like to compare all the data in one worksheet
(50+columns and 800 rows) to another worksheet and identify any
differences.
The worksheets are set up the exact same way regarding column names,
number
of columns, etc.

I know there is at least one difference in one of the cells in this entire
worksheet but there may be more. How can I do this?
Click to expand...

Go to cell A1 in Sheet3 and enter =Sheet1!A1-Sheet2!A1 (obviously the names
of the sheets will be different in your case, but the principle remains the
same).

If the data in Sheet1 cell A1 is the same as Sheet2 cell A1 you will see a
'0'.

Select this cell and copy it to as many others on the sheet as you require.
All identical cells will show '0'.

If this makes the differences hard to spot, you may want to use conditional
formatting to make the cells appear blank when equal to zero - or perhaps
format them red when not equal to zero etc. That's what I'd do!

Hope this helps.

V
 
Back
Top