Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Finding duplicates (2000)

    Hi,
    I have an Access database that has a primary number called URN.
    I am trying to import data from an excel sheet (an old database), into Access. An issue keeps arising where it is finding duplicate values in the URN field in the excel spreadsheet. Is there a formula I can do to highlight all duplicate values in the excel sheet, so as to amend them?

  2. #2
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding duplicates (2000)

    Hans,

    I think the formula has to be changed to work properly (change the A1 to A2).
    It should be =COUNTIF($A$2:$A$100,A2)>1.

    Neat formula.
    Regards,
    Chuck Reimer
    I'm from the Government and I'm here to help...

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding duplicates (2000)

    Yes, thanks. I will correct the formula.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding duplicates (2000)

    Edited to correct typo - thanks to Reimer for pointing it out.

    Say that the URNs in your spreadsheet are in A2:A100 (A1 is the field name).
    In cell B2 (or in the cell in row 2 in another column), enter this formula:
    =COUNTIF($A$2:$A$100,A2)>1
    and fill down as far as needed.
    The result of the formula will be TRUE if the value in column A has duplicates, FALSE if it is unique.

    As an alternative, you can import the Excel sheet into a new table in Access without setting a primary key on URN. Then, use the Find Duplicates Query Wizard to create a query that returns the duplicate values.

Posting Permissions

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