comparing names in a list

[Guest]

I have two very long lists of student names in one worksheet. The names in
A1:A500 is the master list. The student names in B1:B200 have successfully
passed their exam. I would like to know which student names are NOT in the
second list by comparing it to the master .
A1:A500 B1:B200 Answer= John and Steve
Joe Joe
John Sally
Steve
Sally

 

[Max]

One way ..

Put in C1:
=IF(A1="","",IF(ISNUMBER(MATCH(A1,B:B,0)),"",ROW()))

Put in D1:
=IF(ISERROR(SMALL(C:C,ROW())),"",
INDEX(A:A,MATCH(SMALL(C:C,ROW()),C:C,0)))

Select C11, fill down to A500

Col D will return the required results,
all neatly bunched at the top
(items in col A not in col B)

 

[Max]

oops, typo in line:

Select C11, fill down to A500

should read as:

Select C11, fill down to D500

(fill down to the last data row in col A)

---

 

[Guest]

I would insert a helper column to the left of column A and enter
=COUNTIF(B1,C$1:C$200)

Then copy down to row 500. This will return 0 for anyone who does not
appear in column B.

You could then use a filter to identify the 0's in column A. You'll
probably need column headers to use the autofilter. You could then
copy/paste your filtered list to another sheet.

 

[Guest]

Max...I cut and pasted the formulas and corrected for the typo and the
results are bundled nicely like you promised all at the top of the column D
...but the results are inaccurate . I get names in Column D from the master
list A1:A500 but their names are not in column B because they did not pass
their exam.

I am wondering if there is an error in the formula but I dont understand how
you figured it out. please help

 

[Guest]

Max... My error... I did it right this time , forgot to remove the headers.
Thank you so much for this wonderful formula. It is going to save me alot of
time.

 

[Max]

Glad you got it working !
Thanks for the feedback ..