Filtering Two Lists in Excel...

    Howdy group...maybe you can help me?


    SPECIFIC SITUTATION:
    I've sent a fax to a list of 1,000 companies. I have 'fax numbers only' in a Column in Excel.


    I have now been given another list of 2,000 companies to fax to. I have 'fax numbers only' in a different Column in the same Excel spreadsheet.


    How do I filter these two lists to ensure that the resulting fax # list does NOT contain any of the original 1,000 list that I faxed to already?


    At issue is that this new list of 2,000 fax #'s definitely has some of the same fax #'s as my original 1,000 list.


    I'm thinking I need to make an "exclude these #'s" type of filter...and have it scan the new '2,000 list' for any of the old '1,000 list' numbers and have it remove any of those '1,000 list' numbers from the new '2,000 list'...but I have no clue actually...and no clue how to do it. :)


    Any ideas?
    Thanks.
    Matthew Miller
    [email protected]
    403.345.3318

    may be missing the point as im having a bad day, but a vlookup could help remove identical entries.


    Also if you know what you need to delete i.e area code 201 then do a find and replace or custom filter i.e equal to 201 etc


    probably of no help at all but thought i would put some input in

    Hi Matthew,


    If the first list is in column A and the second in column B then enter this formula in C1 and copy down (amend A range to suit):


    =IF(COUNTIF($A$1:$A$10,B1)>0,"Duplicate","Unique")


    Then just filter by column C.


    HTH

    Hi Mathew,


    Assuming that your first list of 1,000 numbers are in Column A, and your second list of 2,0000 numbers are in Column B...


    C1, copied down until you get an error:


    =INDEX($B$1:$B$2000,SMALL(IF(COUNTIF($A$1:$A$1000,$B$1:$B$2000)=0,ROW($B$1:$B$2000)),ROW(1:1)))


    to be entered using CONTROL+SHIFT+ENTER.


    Hope this helps!

    Thanks everyone!


    I got it working...using Domenic's formula...took me a bit to figure out that I had to 'type it out', couldn't just copy it and CTL, SHFT, ENTER...


    Thanks so much group for your help and ideas...I'm sure they'd ALL have worked if I was competent enough to figure out how to implement them and make them work! :)


    Take care,
    Mattman

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login