Compare 2 range of data

[Guest]

Hi,
I have two set of data. One with all personnel and one with all personnel
that are union members. We know which personnel is union member as he is
paying a member fee. There is no way we can query the non-members from our
Personnel system. I now would like to compare the two sheets to extract the
non-members.
Example:
Sheet or range: All personnel Sheet or range: Union member
pers_nr name pers_nr name
101 Name1 102 Name2
102 Name2 104 Name4
103 Name3
104 Name4
105 Name5

So the non members would be 101, 103 and 105.
Is there an easier way to extract these non members? I now use
=IF(A2=G2,"Yes","No"). Whereever I see a "No" I'll insert one or more cells
to make the pers_nr align and again have to fill down the remaining fields.
Thanks in advance, Lupe

 

[Die_Another_Day]

=IF(ISERROR(MATCH(AllPersonel!A1,UnionMember!A:A,0)),"Non-Member","Member")

Charles Chickering

 

[Guest]

in C2 put in the formula

=if(countif(G:G,A2)>0,"Member","Non-Member")
then drag fill down the column

Select column C and do Data=>Filter=>Autofilter and then in the dropdown
select Non-Member. copy the data in columns A and B to another location if
if you want a list. (only the visible cells will be copied).

 

[Guest]

Charles,
Thanks so much, helped me a lot.
Regards, Lupe

 

[Guest]

Hi Tom,
Thanks for your formula. I tried it and it works as well.
In what case is it better to use your formula and in what case Charles'
formula. With both I get the same result.
Lupe

 

[Die_Another_Day]

I would use Tom's formula. Mainly because it's shorter to write, also
because Tom's a genius and he always has the best way to do things .
I just hadn't thought about that particular method yet.

Charles