1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Compare Help

Discussion in 'Business Applications' started by Peaker, Sep 23, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. Peaker

    Peaker Thread Starter

    Joined:
    Nov 23, 2001
    Messages:
    249
    Hi folks,

    I need help once again with Excel.

    I'll set out the scenario first of all and then explain what I want to do.

    I have one spreadsheet that I receive everyday with a list of policy numbers and other information etc. at work.

    I have another spreadsheet that I maintain myself that contains info I have copied and pasted from the first spreadsheet that I work on.

    Each day, if the system updates I put through work. Cases should clear from the first spreadsheet.

    What I would like to do is compare the two sheets. We could go by policy number. Ideally what I would like to happen is to compare both sheets automatically and any policy numbers on sheet two that do not appear on sheet one would be highlighted so I could remove these.

    Any idea how to do this? Are there any packages out there I could use to do this?

    Thanks for your help.

    Peaker.
     
  2. Nic Cunliffe

    Nic Cunliffe

    Joined:
    Sep 18, 2008
    Messages:
    22
    Hi,

    You can do a vlookup from sheet1 to sheet2.

    If your policy data is in col A row 1 to 777 in sheet 2, the formula would be:

    =if(isna(vlookup(a1,sheet2!a1:a777,1,false)),"no policy",vlookup(a1,sheet2!a1:c777,1,false))

    The result of not finding the policy number will return "no policy" as text. If the policy number is present, the policy number will be returned.

    Regards - Nic
     
  3. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    Post a sample with some dummy data of both sheets (the one you receive and the one you are working with). We need to view the structure!
    It can be done by a macro, that will remove any policy that is in one sheet and not in another!
     
  4. Nic Cunliffe

    Nic Cunliffe

    Joined:
    Sep 18, 2008
    Messages:
    22
    Is this what you mean? see col I on sheet 1
     

    Attached Files:

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/752551

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice