Results 1 to 2 of 2

Thread: Highlighting cells which contain data listed in separate sheet

  1. #1
    Neophyte Plumster's Avatar
    Join Date
    Mar 2015
    Location
    Derby
    Posts
    1
    Articles
    0

    Highlighting cells which contain data listed in separate sheet



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello

    This is my first post on here and I'm not great at using excel! I'm looking for some assistance with a problem I have highlighting data. I am compiling a list of web links for manual analysis and possible disavow on Google, however a disavow has already been done on some of the domains and I do not wish to check these links on these domains again.

    I have a list (a very long list) 250,000 web links/URLs on 'sheet 1' example:

    7daybellyblastdietscam.net/who-is-josh-bezoni?replytocom=402
    7daybellyblastdietscam.net/who-is-josh-bezoni?replytocom=61
    818direct.com/business_and_service/construction?s=H&p=1
    83.12.3.107/~kg/index.php?option=com_easygb&Itemid=80&limit=30&limitstart=211470
    83.12.3.107/~kg/index.php?option=com_easygb&Itemid=80&limit=30&limitstart=211470%20
    abcnetdirectory.com/BUSINESS/Construction___Maintenance/page-1.html?s=P
    acecomments.mu.nu/?blog=139&post=276094
    acoustic-kerbs.de/sehr-geehrte-damen-und-herren/
    administracaoesucesso.com/2012/03/19/tempo-voce-utiliza-o-corretamente/
    administracaoesucesso.com/2012/03/19/tempo-voce-utiliza-o-corretamente/?replytocom=362
    advertiz.in/Arts/Arts/Kids_and_Teens/Arts/Reference/Business/Arts/Arts/Games/Reference/Business/Shopping?p=12
    affiloblueprint3launchbonus.com/contact-us
    afvallendoorcambridge.nl/?q=node/add&replytocom=1480
    afvallendoorcambridge.nl/?q=node/add&replytocom=151


    and I have a list of 435 domains on sheet 2 example:


    50plus-top-versorgt.de
    5fingervibramshoes.com
    70metres.com
    77.cap21.net
    7daybellyblastdietscam.net
    911cha.com
    abi12.csgb.de
    academy.e-agent.co.nz
    acclaimedrealtygroup.com
    accxpert.canadae.biz
    acoustic-kerbs.de
    adamrushlow.com
    adanaolgunlasma.k12.tr


    How can I highlight all links on sheet 1 that contain any of the domains listed on sheet 2?

    I found this formula which flags exact duplicates:

    =IF(ISERROR(MATCH(A1,Sheet2!$A$1:$A$10000,0)),"","Duplicate")

    But I'm clueless with formulas and have no idea how to amend this or write my own.

    Please help!!

    Thanks in advance

    Plumster

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,820
    Articles
    0
    Excel Version
    O365
    Try

    =SUMPRODUCT(--(ISNUMBER(FIND(Sheet2!$A$1:$A$14,A1))))>0

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •