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. For ArcGIS 10.x, see the article in the Related Information section below.

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.