Deleting duplicates from a table in ArcGis

8464
6
12-01-2015 01:44 AM
BaffourAwuah
New Contributor III

Can someone help me as to how to delete duplicates values from a large dataset table? I tried the delete tool in ArcToobox but it didn't work. Maybe I should explain further: The dataset is made up of different farmers, their pen, number of sheep, locations and other related attributes. I have detected that some of the farmers have more than one pen within the same compound and while each point of a pen was attributed to a farmer, the total number of sheep was attributed to each pen; creating duplicates and sometime duplicates. For example, if the total number
of sheep in a farm of 3 pen is 135, this number (135) was attributed to each of the 3 pens, making it 405 sheep for a farmer appearing three times on the table. I hope I sound a bit explanatory.

0 Kudos
6 Replies
RickeyFight
MVP Regular Contributor

Baffour,

I would move this question to ArcGIS Explorer Desktop

0 Kudos
RobertBorchert
Frequent Contributor III

Just to clarify are you looking to delete duplicate records in a Table or a Feature Class which has an attribute Table.

If it is JUST a table.  open it in Access.  Copy all the records and paste them into an Excel Document.  Paste as Text

Select your columns that contain  the duplicate data and click on the Data Tab>Sort & Filter>Advanced

In the pop up window select the unique records only option.  It will strip out all duplicates.

Save your table and replace the table in your database.  For goodness sake make a back up first.

MitchHolley1
MVP Regular Contributor

Have you tried using the Find Identical (Data Management) tool?

0 Kudos
RobertBorchert
Frequent Contributor III

I have used it but found it to not be as helpful for what your trying to do.

it will result in a new table that has FID's that you then need to match up,  It will not show you unique results.

Using that tool to find identical results would still result in you having to manually delete entries.

If you only have a hundred or so duplicates that isn't a big deal

0 Kudos
Pierre-LucBoivin
Occasional Contributor

This tool is good too but you need to have ArcGIS Advanced to use this tool.

Pierre-LucBoivin
Occasional Contributor

You can also create a new string column (Concat) and concatenate all the attributes that you want to identify as duplicate. You have to be sure that your data are standardize.

After that, order you new column (Concat) by ascending order.

Create a new integer column (Duplicates) and use this script as field calculator

Be sure that the parser is Python and the "Show codeblock" is checked

uniqueList = []

def isDuplicate(inValue):

  if inValue in uniqueList:

  return 1

  else:

  uniqueList.append(inValue)

  return 0

Under "Duplicates ="

isDuplicate(!Concat!)

the (!Concat!) is representing the field you want to identify the duplicates

38700 - Identify duplicate field values

After that with editor you gonna be able to delete all the duplicates values.

It's always a good thing to create a copy of the shapefile, feature class, etc... before doind this.