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
---

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.

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