English

How To: Identify duplicate field values

Summary

Warning:
The procedure below is for ArcGIS 9.3.1 or earlier versions. Starting at ArcGIS 10.0, expressions in the Calculator are created using only VBScript or a standard Python format.
Instructions provided demonstrate how to use the Field Calculator to identify duplicate field values. Single occurrences and the first occurrence of multiple values are flagged with 0. Duplicates are flagged with 1.

Procedure

  1. Create a new field. Set the type as long integer and accept the other defaults.
    1. If editing, stop the current edit session.
    2. Open the feature class attribute table.
    3. Click the Options button.
    4. Select 'Add Field...'.
    5. Name the field.
    6. Select Long Integer from the Type drop-down list.
    7. Accept the default values for all Field Properties.
    8. Click OK.
  2. Right-click the field created in the step above and select Calculate Values.
  3. Click the Advanced check box in the Field Calculator dialog box.
  4. Paste the following code into the 'Pre-Logic VBA Script Code' text box:
    Static d As Object 
    Static i As Long 
    Dim iDup As Integer 
    Dim sField 
    '======================== 
    'Replace the field name below 
    sField = [b] 
    '======================== 
    If (i = 0) Then 
    Set d = CreateObject("Scripting.Dictionary") 
    End If 
    If (d.Exists(CStr(sField))) Then 
    iDup = 1 
    Else 
    d.Add CStr(sField), 1 
    iDup = 0 
    End If 
    i = i + 1 
    
  5. In the code above, replace [b] with the name of the field containing duplicate records.
    Note:
    The field name must be enclosed in square brackets.
  6. Type iDup in the lower text box of the Field Calculator dialog box.
  7. Click OK. All duplicate records are designated with a value of 1 and non-duplicate records are designated with a value of 0.
  8. Optional Step: A SQL Query may be executed to select either zeros or ones. The resulting selection set can then be exported to a new feature class or deleted in an ArcMap edit session.

    For example, perform a 'Select by Attributes' operation to select all values equal to one.
    1. Open the feature class attribute table.
    2. Click the Options button.
    3. Select 'Select By Attributes'.
    4. In the Where Clause text box (the text box underneath "Select * FROM <TABLE_NAME> WHERE"), paste the following SQL expression:

      [FieldName] = 1

      Where [FieldName] is the name of the field containing the ones and zeros.
      Note:
      The above syntax is for geodatabase data sets. If using shapefiles or DBase tables, the field name must be enclosed in double quotes. For example:
      
      "FieldName" = 1
    5. Click OK.

Related Information