Eliminating duplicated data completely or something similar

  • Thread starter Thread starter NickySams
  • Start date Start date
N

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!
 
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
 
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
 
Back
Top