Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

im looking for duplicate records in dbf files based upon the attribute called 'ID'. I have various dbf files from 500,000 records to 1.5 million and I know there are a host of duplicates.

I would like to add a field 'Duplicate' that says Yes or No (or 1 or 0 is fine) when the ID attribute is present elsewhere. Using the following python script in Field Calculator returns 1 for a duplicate entry and 0 for unique entry;

uniqueList = []
def isDuplicate(inValue):
  if inValue in uniqueList:
    return 1
    return 0

however the 1st record of, for example, 5 duplicate IDs will also be returned as a 0 (the subsequent 4 are considered the duplicates). I would need all 5 to be marked as duplicate as the ID exists elsewhere.

Using the following code will give you an incremental count of how many times that ID occurs with 1 meaning the 1st occasion and so forth;

UniqueDict = {}
def isDuplicateIndex(inValue):
    UniqueDict[inValue] += 1
    return UniqueDict[inValue]

isDuplicateIndex( !YOUR_FIELD! )

I just want a 1 (or Yes) if the ID of that record exists elsewhere! (ArcGIS version 10.1)

I have seen other answers such as Python script for identifying duplicate records (follow up) but it doesn't quite work.

share|improve this question
up vote 6 down vote accepted

An alternative solution is to use the existing "summary statistics" tool in ArcGIS, then you join the resulting table based on you ID field. The duplicates will have a "COUNT" larger than 1, so it is then simple to calculate it with your field calculator.

share|improve this answer
How does your method achieve assigning the first duplicate record found as '0'? – artwork21 Jan 14 '14 at 13:22
@ radouxju Thanks for your answer, i can see what amounts of polygons are duplicates by simply selecting by attribute now. Surprised this didnt occur to me when all the python stuff did! – Sam Jan 14 '14 at 13:27
@artwork21 i didnt want the 1st duplicate to be a 0, i wanted anything that had a duplicate to be a 'YES', or now - as it is - a number greater than 1 – Sam Jan 14 '14 at 13:28
@Sam, what are you referring about with this statement, "however the 1st record of, for example, 5 duplicate IDs will also be returned as a 0;"? – artwork21 Jan 14 '14 at 13:36
@artwork21. Apologies, i think my original wording wasn't very clear, i will amend. What i was trying to say was that if 5 records all had the same ID, that piece of python code would identify the 1st instance as a unique ID and the subsequent 4 as being the duplicates. I wanted all 5 to be marked as duplicates (i.e. that ID existed elsewhere) – Sam Jan 14 '14 at 13:39

Another alternative solution is to use the existing SQL functionality in ArcGIS. Note just shows the duplicate records and only works with SDE environments.

Get Duplicate Records in Table (Select by Attribute)



share|improve this answer
Can you get this to work in a file geodatabase? The query works successfully in a personal geodatabase, but when I try running it in a file geodatabase it fails with the message "An invalid SQL statement was used." Edit: according to the documentation link, only limited subqueries are supported in file geodatabases. – isburns Mar 30 at 2:47
@isburns I've only ever done it in file geodatabases I know it works, maybe recheck your query. – Tristan Forward Mar 30 at 14:03
The query is copied directly from your post and references the correct table and field names. The query is valid when I remove HAVING COUNT(*) > 1. I really don't see a way of getting it to work in file geodatabases. I know this tech article is somewhat dated, but it seems to be the source of your SQL statement and it indicates that it does not work in file geodatabases. I'm ready to upvote your answer if I can make it work in file gdbs, or clarification is added to indicate they are the exception. – isburns Mar 30 at 19:58
@isburns I was mistaken, works in SDE environment and not file geodatabase. One thing you can do as a workaround is bring the table data into Excel find the duplicates and then join the list of dupes back in ArcGIS which would then only show those records, not ideal but works. – Tristan Forward Mar 31 at 12:03
Thanks for the update. I upvoted your answer because it does work and is fairly simple and fast in the supported geodatabases. I know it's in the comments now, but you might want to also edit the post itself to indicate that it works in personal and SDE geodatabases but not file geodatabases or shapefiles. – isburns Mar 31 at 19:30

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.