Remove Common Records Across 2 Workbooks

  • Hi there,
    I have tried to search a lot for this, but unfortunately havent come up with just the right solution yet.

    I do think its fairly simple:
    I have a daily sheet 'daily.xls' containing business names, adresses and phone numbers. Like this:

    IBM, 23889 IBM Road, +1 94448833
    Somecomp, 38223 Main Road, +34 99443

    In order to find the right businesses I have a huge 'blacklist' 'blacklist.xls' - in another sheet where I store company name, adresses and phonenumbers formatted in the same way.

    I would like the sheet 'daily.xls' to search in the Blacklist. Any company matching the Blacklist should be removed from the 'daily.xls' leaving only whitelisted companies.. you get the idea :)

    I hope its possible

    Thanks in advance,


  • Re: Remove Common Records Across 2 Workbooks

    Hi bodill,

    I can think of 3 ways to do this

    1. a macro that will - loop through your daily list and put a mark against all blacklisted entries - this is done by making the blacklist a named range in the blacklist sheet and being referred to in the daily sheet by the macro.

    The sort the list by the mark and delete them. Fairly complicated and requires the macro to be in the daily sheet

    2. insert a worksheet into your blacklist sheet and call it "daily" and andd a formula column that uses vlookup to determine if the entry exists in the blacklist, if it does mark as so - simple, quick, can sort it, no macros really required, cut a paste the list into an other sheet

    3. the 3rd way was ingenious but I forgotten what it is - probably the same as 2 but using macros

    I would suggest option 2 is the easiest and quickest



    But I always say luck is where preparation meets opportunity (Justin Langer 23/11/01)

Participate now!

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