COMPARING TWO COLUMNS OF INFORMATION, ACROSS TWO WORKSHEETS

[Susan]

I want to compare for accuracy two coloums of information that should be
identical, but on two different worksheets within the same workbook. So, for
example, I want to make sure that worksheet 1 columns a & b are identical to
worksheet 2 columns a & b, and if not highlight or identify discrepansies.

 

[Luke M]

In columns c and d respectively
=EXACT(Sheet1!A1,Sheet2!A1)
=EXACT(Sheet1!B1,Sheet2!B1)

Copy down as needed.

Anything that returns a false means there is a discrepency.

 

[Ashish Mathur]

Hi,

You can do so by conditional formatting. However, conditional formatting
cannot work across sheets. Therefore, please use the following steps:

1. Go to sheet 2 and highlight column A;
2. Press Ctrl+F4 (Define name) and define a name for the range such, say
compare
3. Now go back to sheet 1
4. On the first cell of column A (say A4), go to Format > Conditional
formatting > Formula Is
5. In the formula is box, type the following formula =countif(compare,A4)>0
6. Select the format of your choice
7. Click OK

Repeat the process for column 2

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com