Compare cells - diplay if not identical

Posted by Andonny on April 18, 2001 4:22 AM

Hi,
I would like to find a formula which displays in column C the result if the 2 compared cells are not identical.
Example:
On C I should return "Appl" because it does not have the same spelling in sheet1 compared to sheet2. I would like to detect if something is not spelt correctly. The numbers in column A alway belong to the correct Item. At the moment I use vlookup to display in C all whats in column B on sheet1 and then compare them once they are side by side. It takes up a lot of bytes and I was hoping to find a formula that I only need to display when there is a change.
sheet1
A.........B
55.....Appl
35.....Pear
21.....Kiwi
43.....Nashi

sheet2
A.........B.........C
35.....Pear
55.....Apple.....Appl
21.....Kiwi

Thank you very much
Andonny

Posted by David Hawley on April 18, 2001 5:16 AM

Hi Andonny

Have you tried the EXACT formula ?

Dave

OzGrid Business Applications

Posted by Aladin Akyurek on April 18, 2001 9:08 AM

Another route... Conditional Formatting

Hi Andoony

Your problem can also be handled by using conditional formatting.

Select the names on sheet1 and name it e.g., NAMES.
On sheet2, activate the first cell that contains a name (say B1) then activate Format|Conditional Formatting, and choose "Formula is". Enter the following formula:

=NOT(ISNUMBER(MATCH(B1,NAMES,0)))

Select a Format, for example a red background.

Activate cell B1, click on the icon for Format Painter. Now select the rest of the cells.

The cell that differ in spelling from the ones on Sheet1 will all have a red background.

Aladin

==================