Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Leesburg, Virginia, USA
    Posts
    618
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Finding duplicate values (Excel 2003)

    I have two workbooks each with three columns - email address, first name, last name. I would like to identify duplicate email addresses. Then duplicate last names. I have tried combining these into one workbook with 6 columns and then using the Tools>Data>Advance Filter Function, but I only get a list of what appears to be unique cells.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Finding duplicate values (Excel 2003)

    Hi Jim

    I used:

    =IF(ISNA(INDEX($A$2:$A$5,MATCH(G3,$B$2:$B$5,0)))," No Match","Duplicate")

    Where $A$2:$A$5 is my first list and $B$2:$B$5is my second list for comparing. You will obviously have to adjust this for your list. I have found that it is probably best to Name this list using Insert|Name|Define

    I have attached an example for you..enjoy
    Jerry

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Leesburg, Virginia, USA
    Posts
    618
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding duplicate values (Excel 2003)

    Thanks Jerry, I tried to replicate your work, but everything turns up a duplicate. Attached is what I am working on. Can you show me where I went wrong?

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Finding duplicate values (Excel 2003)

    Hi there,

    You just had the syntax sslightly wrong. I have used your data, best click ona cell and then onto the formula and you can see where all the various parts of the formula pull the data. What you will need to do is make the ranges absolute. Click on areference, say A3 and press F4 on the keyboard and the reference will look like A$3$, this makes it absolute, and when you copy down a cell it will remain the same.
    Jerry

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Finding duplicate values (Excel 2003)

    Why not combine them into only 3 columns, not 6?

    Just take one of the 3 column lists and add it to the end of the other

    Then use adv filter to extract out the unique items

    Steve

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Leesburg, Virginia, USA
    Posts
    618
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding duplicate values (Excel 2003)

    Thanks, Jerry's suggestion worked.

Posting Permissions

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