Eliminating duplicated data completely or something similar

[NickySams]

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!

 

[Ashish Mathur]

Hi,

You may try this. Suppose Citizens is in range D311 (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 D311. In the criteria, select D1516 and in the copy
to box , select any blank range, say D2027 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

 

[Max]

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

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