VLookup comparing two sheets..please

[Bonny]

hi,
Please..need help, I am new a VLoopUp..

How can I use VLookUp to compare to dif. sheets (say sheet1 and sheet
in a project). I need to compare all names that I have under field A o
sheet1 , versus all names that I have under field A of sheet2.(sheet
and sheet2 have diferent amount of data).
I need to find a way of getting all names that are in sheet2 but not i
sheet1.
Is there any way to this using VLookUp and /or other excel formulas??.
and, How is it that it goes thru the whole column of a sheet, fo
example if I am talking about a very large amount of data( names)???
FOR EXAMPLE:


sheet1
A
smith
weiss
lee



sheet2
A
weiss
gray
ross
ron

how do I get an answer like gray, ross , ron ( people not found o
sheet1), taking in consideration that there is lots of data???

 

[Biff]

Hi!

Try something like this, entered as an array:
CTRL,SHIFT,ENTER

=INDEX(A$1:A$15,SMALL(IF(COUNTIF(Sheet1!A$1:A$10,A$1:A$15)
=0,ROW(A$1:A$15)),ROW(1:1)))

Copy down until you get #NUM! errors.

This will extract all the names in Sheet2 that do not
appear on Sheet1. Notice that in the COUNTIF function the
range and the criteria are different sizes as you said
that Sheet2 has more data than Sheet1.

Biff

 

[JulieD]

Hi Bonny

the VLOOKUP is not suitable for what you want to do, a good place to find
out all the different things you can do with duplicates and lists is Chip
Pearson's website -

http://www.cpearson.com/excel/duplicat.htm

Cheers
JulieD