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

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 C1:D1, 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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"ferde" wrote in message
...
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

oops, typo in line:
Select C1:D1, fill down to A500

should read as:
Select C1:D1, fill down to D500
(fill down to the last data row in col A)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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.


"ferde" wrote:

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...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 :)

"Max" wrote:

oops, typo in line:
Select C1:D1, fill down to A500

should read as:
Select C1:D1, fill down to D500
(fill down to the last data row in col A)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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" wrote:

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 C1:D1, 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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"ferde" wrote in message
...
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

Glad you got it working !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"ferde" wrote in message
...

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.