Selecting names from list

[Closed]
Report
-
 GusMD -
Hello,

I have columns A and B with the first and last names of subjects. In columns C and D I also have the first and last names of a larger group of subjects, including all the ones from A/B. I need to automatically create a column with only the names from C/D that ARE NOT in A/D. How should I proceed?
Thanks

4 replies

Posts
190
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
155
Suppose you data is in first Row then copy this formula in Cell E and drag it down .... Hopefully it will solve your problem if not then please get back to me ........

=IF((A1&" "&B1)<>(C1&" "&D1),(C1&" "&D1),"")



Thank you. However, it seems like your formula just returned the names that were not matched at each row. For example, if there were a John Smith in C1,D1 but not in A1,B1 (but maybe somewhere else) it would not return.
Just to re-emphasize:
I have a smaller list of names in A+B, and a bigger list in C+D. I need a list with the names in C+D that ARE NOT in A+B.
Thanks for your attention
Posts
190
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
155
OK try this

E1=A1&" "&B1
F1=C1&" "&D1

G1=IF(ISERROR(MATCH(F1,E$1:$E$4,0)),F1,"")
Press Control+Shift+Enter as its an array formula and you will get { } around above formula in G1
Don't try to write {} manually

Now any values in column C&D which are not in A&B will appear in this column

If you want to apply on A&B then replace F1 with E and range with F1:F4

Hope it will solve your problem, don't hesitate to contact me.
I"ll be away on Sat & Sun as we have Off Days.
It worked!
Thank you very much!