Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    The Hague, Netherlands
    Posts
    125
    Thanks
    0
    Thanked 1 Time in 1 Post

    Finding duplicates (Excel 2003)

    Hi,

    I have a spreadsheet containing hundreds of rows of data to be up-loaded to a database table. The key field in the table is Employee ID. A constraint in the table prevents duplicate Employee IDs. I will upload the data using the SQL Server tool called DTS.

    What can I do in Excel (I don't speak VBA) to discover any duplicate rows before I perform the upload? There are too many rows to rely on just scanning by eye.

    Regards

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

    Re: Finding duplicates (Excel 2003)

    Say that the EmployeeIDs are in A2:A1000.
    Insert a blank column between columns A and B.
    In B2, enter the formula

    =COUNTIF($A$2:$A:$1000,A2)>1

    and fill down to B1000. The cell in B will display TRUE if the entry in A has duplicates, otherwise FALSE.
    When you're done with the inspection, you can delete column B.

    See <post:=394,358>post 394,358</post:> or <post:=402,054>post 402,054</post:> for a way to use Data | Validation to prevent entry of duplicate values in a column.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    The Hague, Netherlands
    Posts
    125
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Finding duplicates (Excel 2003)

    Hans, many thanks. You know that it works, and I am, once more, reminded of the million things to learn if only one had the time.

    Thanks and regards

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding duplicates (Excel 2003)

    It will be easier to spot the duplicates if you modify Hans' formula to:

    <code>
    =IF(COUNTIF($A$2:$A:$1000,A2)>1,"Duplicate","")
    </code>
    Legare Coleman

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    The Hague, Netherlands
    Posts
    125
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Finding duplicates (Excel 2003)

    Thanks Legare. It does make the message much more visible.

    Regards

Posting Permissions

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