A comparison function

[Mary K. Bullock]

I have a list of hundreds of candidate names on sheet 1 of a spreadsheet;
sheet 2 has the names of the people who attended an interview session. Is
there a formula that I can run that can compare sheet 1 with sheet 2 and
dump all of the names that are on sheet 1 but not on sheet 2 onto sheet 3?
(In other words, tell me which candidates have not yet interviewed?)

 

[JE McGimpsey]

Take a look at

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

(the "Extracting Values On One List And Not Another" section)

 

[Stephen Dunn]

Hi Mary,

Use this formula in the first cell of sheet 3 where you want the results.
It is an array formula (so hold Ctrl+Shift when you press Enter)

=IF(ROW()-CELL("ROW",list3)<SUM(--ISNA(MATCH(list1,list2,0))),INDEX(list1,SM
ALL(IF(ISNA(MATCH(list1,list2,0)),ROW(INDIRECT("1:"&ROWS(list1)))),ROW()-CEL
L("ROW",list3)+1)),"-")

Copy it down to each cell that you require results in (I would recommend the
same length as list1).

list1 is the list on sheet 1, list2 is the list on sheet 2, I'll let you
guess what list3 is...

HTH
Steve D.