Comparing 2 columns if they are the same

[Faio]

I have two columns to compare. I first sort them out in the same order and
then use the IF function. The If function is OK but when the rows to compare
are not match due to the error in typing or spaces then there is no match.
Is there another way or function to use that instead of sorting, it will
just lookup the two columns and compare and then indicate that there are say
5 mismatched and indicate them?

Appreciate any help

JSSU0611220
JSSU0611220

JSSU0618924
JSSU0611339

JSSU0617893
JSSU0612227

JSSU0615170
JSSU 0614195

JSSU0614370
JSSU0614322

JSSU0614322
JSSU0614370

JSSU0614195
JSSU0615170

JSSU0612227
JSSU0617893

JSSU0611339
JSSU0618924

 

[Guest]

You could allow for the extra spaces in your compare function: instead of
a3=a2, use trim(a3)=trim(a2).

 

[Max]

One way

Assuming data is in cols A & B, from row2 down

Put in C2: =IF(A2="","",ISNUMBER(MATCH(A2,B:B,0)))
Copy down till last row of data in col A
(Above compares col A against col B)

Put in D2: =IF(B2="","",ISNUMBER(MATCH(B2,A:A,0)))
Copy down till last row of data in col B
(Above compares col B against col A)

Put 2 labels into C11, and do an autofilter on cols C & D in turn

Filtering out: FALSE in each case (col C, then col D) would then
locate the mismatches for the comparison

 

[Max]

And a nice closure note received from the OP ..

To: "Max" <[email protected]>
Subject: Re: Comparing 2 columns if they are the same
Date: Tue, 15 Nov 2005 14:30:45 +1200

Thanx a lot.
It works as expected.
Great

 

[Guest]

Check http://www.j-walk.com/ss/excel/usertips/tip027.htm

OR http://www.j-walk.com/ss/excel/usertips/tip048.htm