Say I have 2 lists. One of which we'll call citizens, the other we'll call
enemies. everyone on the citizens list should also be included in the Enemies
list, but they're not. I'm trying to have it list the names that do not show
on the enemies list. How would I go about doing this? Is it an INDEX function
or a MATCH function? Been tearing my hair out on this one. Thanks!

One way is to use VLOOKUP
Assuming Citizens are in Col A of Sheet1 and Enemies in Col A of sheet2
Enter this in B1 of Sheet1 and copy down till end of your data
=VLOOKUP(A1,Sheet2!A:A,2,False)

Now filter Col B on #N/A... You will get the list of Citizens who are NOT in
Enemies list.

"NickySams" wrote:

Say I have 2 lists. One of which we'll call citizens, the other we'll call
enemies. everyone on the citizens list should also be included in the Enemies
list, but they're not. I'm trying to have it list the names that do not show
on the enemies list. How would I go about doing this? Is it an INDEX function
or a MATCH function? Been tearing my hair out on this one. Thanks!

Hi,

You may try this. Suppose Citizens is in range D3:D11 (heading included)
and enemies is in range E3:E11 (heading included). In cell D15, type
Condition. In cell D16, use the formula =COUNTIF($E$4:$E$11,D4)=0. Now go
to filter Advanced Filter and select "Copy to another location". In the
list range, select D3:D11. In the criteria, select D15:D16 and in the copy
to box , select any blank range, say D20:D27 and now click on OK.

Please note that this is not a dynamic solution I.e. if any entries change
in the 2 lists, you will have to rerun the advanced filter.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"NickySams" wrote in message
...
Say I have 2 lists. One of which we'll call citizens, the other we'll call
enemies. everyone on the citizens list should also be included in the
Enemies
list, but they're not. I'm trying to have it list the names that do not
show
on the enemies list. How would I go about doing this? Is it an INDEX
function
or a MATCH function? Been tearing my hair out on this one. Thanks!

Another play ..
Citizens listed in A1 down, Enemies in B1 down
In C1: =IF(A1="","",IF(COUNTIF(B:B,A1),"",ROW()))
In D1: =IF(ROW()COUNT(C:C),"",INDEX(A:A,SMALL(C:C,ROW()) ))
Copy C1:D1 down to cover the max expected extent of data in col A.
Hide/Minimize col C. Col D auto-returns the list of col A (citizens) NOT
found in col B (Enemies), with results all neatly packed at the top.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:370 Subscribers:68
xdemechanik
---
"NickySams" wrote:
Say I have 2 lists. One of which we'll call citizens, the other we'll call
enemies. everyone on the citizens list should also be included in the Enemies
list, but they're not. I'm trying to have it list the names that do not show
on the enemies list. How would I go about doing this? Is it an INDEX function
or a MATCH function? Been tearing my hair out on this one. Thanks!

Amazing. Thank you. I knew it was something not too difficult.

"Sheeloo" wrote:

One way is to use VLOOKUP
Assuming Citizens are in Col A of Sheet1 and Enemies in Col A of sheet2
Enter this in B1 of Sheet1 and copy down till end of your data
=VLOOKUP(A1,Sheet2!A:A,2,False)

Now filter Col B on #N/A... You will get the list of Citizens who are NOT in
Enemies list.

"NickySams" wrote:

Say I have 2 lists. One of which we'll call citizens, the other we'll call
enemies. everyone on the citizens list should also be included in the Enemies
list, but they're not. I'm trying to have it list the names that do not show
on the enemies list. How would I go about doing this? Is it an INDEX function
or a MATCH function? Been tearing my hair out on this one. Thanks!