Comparing two cells

M

Mandeep Dhami

Hi,

I have same data in column A and column B, but in column B the data in not
in the same sequence as that in column A.

I want a formula which identifies if there is any difference in data of
column A with that of column B, if there is any difference then the same
should be highlighted.

Hope to here the formula for the same soon......

Cheers,
Mandeep
 
M

Max

Mandeep Dhami said:
.. same data in column A and column B,
but in column B the data in not in the same sequence
as that in column A.
.. a formula which identifies if there is
any difference in data of column A with that of column B,
if there is any difference
then the same should be highlighted.
Click to expand...

Here's one way to highlight items in col A
which are not in col B via conditional formatting

Select col A
Click Format > Conditional Formatting
Under Condition 1, make the settings as:
Formula is:
=AND(NOT(ISNUMBER(MATCH(A1,$B:$B,0))),A1<>"")
Click Format button > Patterns tab > Brown? > OK
Click OK at the main dialog

The above will return the required highlights in col A ..
 
T

Toppers

One way:

Put in column C and copy down:

=IF(COUNTIF(A1:A20,"="&B1)=0,"Missing",IF(COUNTIF(A1:A20,"="&B1)>1,"Duplicated",""))


HTH
 
Top