+ Reply to Thread
Results 1 to 5 of 5

Using Match To Detect Duplicate Record

  1. #1
    Registered User
    Join Date
    11-19-2013
    Location
    Hong Kong
    MS-Off Ver
    Excel 2010
    Posts
    3

    Question Using Match To Detect Duplicate Record

    Hi All,

    There are 100 records in column A,B,C respectively.

    e.g)

    For row3,
    if A3 can be found in A1:A100-A3
    then check
    if B3 can be found in B1:A100-B3
    then check
    if C3 can be found in C1:C100-C3
    then D3 returns "YES" if all true
    Else
    D3 returns "No"

    Now, im using =IF(ISERROR(MATCH(A3,$A$1:$A$100-A3,0)),"No","Yes") for checking A3 and it works fine,,

    but i don't know how to keep on checking B3, C3

    Thanks for any help.
    Attached Files Attached Files
    Last edited by spku; 11-20-2013 at 02:47 AM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,007

    Re: Using Match To Detect Duplicate Record

    Hi and welcome to the forum

    1st, your ranges look very strange, what does the "-A3" do at the end of the "A1:A100-A3" do/mean? Ranges are normally the top-left:bottom-right cell references.

    2nd, I think it would help if you provided a sample workbook, showing samples of the data you are working with, and what your expected outcome would be?
    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    11-19-2013
    Location
    Hong Kong
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Using Match To Detect Duplicate Record

    Thanks FDibbins, a sample excel document is attached,,

    BTW, Isn't A1:A100-A3 = "From Cell A1 to A100 except A3 ???"

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,007

    Re: Using Match To Detect Duplicate Record

    Thanks for the file. Try this, copied down...
    =IF(COUNTIFS($A$2:$A$14,A2,$B$2:$B$14,B2,$C$2:$C$14,C2)>1,"YES","NO")

    I have never heard of "BTW, Isn't A1:A100-A3 = "From Cell A1 to A100 except A3 ???"", but that doesnt mean you are wrong lol

  5. #5
    Registered User
    Join Date
    11-19-2013
    Location
    Hong Kong
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Using Match To Detect Duplicate Record

    Oh cool ,, it works fine.

    But how about now I change to

    For row3,
    if A3 can be found in A1:A100-A3
    then check
    if B3 can be found in B1:A100-B3
    then check
    if C3 CANNOT be found in C1:C100-C3
    then D3 returns "YES" if all true
    Else
    D3 returns "No"

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Textbox to detect if there is a duplicate
    By siobeh in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-01-2013, 06:42 AM
  2. Goto Record when Duplicate Record Exists
    By rlsublime in forum Access Programming / VBA / Macros
    Replies: 6
    Last Post: 03-29-2012, 11:13 AM
  3. Detect if a cell is duplicate
    By Jinxedcookie in forum Excel General
    Replies: 2
    Last Post: 11-29-2011, 01:08 PM
  4. Need Formula to detect duplicate invoices
    By XYZ via OfficeKB.com in forum Excel General
    Replies: 1
    Last Post: 10-11-2005, 06:05 PM
  5. detect Duplicate Data
    By bdq17 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-10-2005, 04:07 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1