comparing names in a list

[ferde]

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 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" <[email protected]> wrote in message
news:[email protected]...
> 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]

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

[JMB]

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

[ferde]

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

[ferde]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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]

Glad you got it working !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"ferde" <[email protected]> wrote in message
news:[email protected]...
>
> 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.