How to find unmatched values?

[GN0001]

I have to lists in two sheets:

Sheet A
Sheet B

In Sheet A, I have employee Number
In Sheet B, I have employee Number

I want to find out what employees are in Sheet A but they are not Sheet B and vice versa,
What method do we need to use?

Regards,
GN0001

 

[Somendra Misra]

Hi,

Considering your first list in A2:A11 on Sheet1 & second list on Sheet2 A2:A8, try belwo array formula in F2 and copy down till you get an error.

=INDEX($A$2:$A$11,SMALL(IF(ISNA(MATCH($A$2:$A$11,Sheet2!$A$2:$A$8,0)),ROW($A$2:$A$11)-ROW($A$2)+1),ROWS(F$2:F2)))

Regards,

 

[Deepak]

Or Simply use!!!
Assume in first sheet A2:A10 onward having employee number & sheet2 having same in columns A.

use on D2:D10=ISNUMBER(MATCH(A2,Sheet2!A:A,0))

Where true are match & false are non-match.

 

[ThrottleWorks]

Hi,

I think, this will also work.

=IFERROR(VLOOKUP(A2,Ducati!A1:A20,1,0),A2&" is only in Yamaha")

=IFERROR(VLOOKUP(Ducati!A1,Yamaha!A2:A21,1,0),Ducati!A1 & " is only in Ducati")

=IFERROR(VLOOKUP(A2,Yamaha!A:A,1,0),"Only in Ducati")

 

[SirJB7]

Hi, GN0001!

Check this:
http://chandoo.org/wp/2010/06/17/compare-2-lists-in-excel/
http://chandoo.org/wp/2010/07/01/compare-lists-excel-tip/

And remember:
If you haven't performed yet the search herein, try going to the topmost right zone of this page (Search...), type the keywords used in the title when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.

Regards