Duplicate Records - How to Merge Attributes from 2 Records Into 1

16390
3
11-20-2012 07:57 AM
CoryGallipeau
New Contributor II
I merged 2 separate shps into one master shp.  Now, I have a shp that contains many duplicate records.   Each shp contained some records that were also in the other shp as well as some unique records.  The ID #'s are the same (that's how I know they're duplicates).  I have a field that identifies the duplicate ID #s.  However, my problem is that certain fields contain data for some records but not for others and vice versa.  My question is how to get any data for an ID into one row, regardless of which layer it comes from.  For example, ID 01 in Layer A contains attribute info for fields 1, 2 and 3 but not for fields 4 5 and 6.  ID 01 in Layer B contains attribute info for fields 1, 2 and 3but not for fields 4 5 and 6.  I want one record that has attribute info for fields 1, 2, 3, 4, 5, and 6 for ID 01.  I assume this is a combination of joining and concatenating but so far haven't had any success figuring it out.
0 Kudos
3 Replies
DanLee
by Esri Regular Contributor
Esri Regular Contributor
Perhaps the following will help you:

1.  Run Summary Statistics (Analysis toolbox - Statistics toolset) using the IDs as the Case Field; specify for every field you want to keep how you want to combine the multiple values.
2.  Run Delete Identical (new in Arcgis 10; Date Management toolbox - General toolset) on the merged shp to delete features with duplicate ID.
3.  Run Delete Field (Date Management toolbox - Fields toolset) to delete unwanted fields (ones being summarized in step 1).
4.  Run Join Field (Date Management toolbox - Joins toolset) to join the Summary Stats result back to the shp through the common ID field.

Does that work for you?
0 Kudos
CoryGallipeau
New Contributor II
Thanks for the reply and the step by step instructions - very helpful.  That's definitely moving in the right direction of what I'm looking for.  The issues that I'm dealing with now with using the Summary Statistics tool are that fields that are of type "Date" aren't available to select in the 'Statistics Field(s)' box and that sometimes I have to choose both 'First' and 'Last' of a field to get all of the data for that field (which defeats the purpose of consolidating the attributes into one field only).  FYI, I'm using ArcGIS 9.3 but have ways of finding and deleting exact duplicates.  The issue with it is that they have to be EXACT, which is how the Arc10 tool is set up too I believe, which isn't my scenario.
0 Kudos
DanLee
by Esri Regular Contributor
Esri Regular Contributor
Now I look back, did you use the Merge tool to combine the two shp files?  If so, have a look of the Field Map in that tool. Does Date field show up there? Sorry I don't have a 9.3 machine to check on that. If it does, you can play with the merge rules in the Field Map and perhaps use the Join option to concatenate all subfield values. Here is the link to 9.3 help topic on Field Map:
http://webhelp.esri.com/arcgisdesktop/9.3/index.cfm?TopicName=Using the field mapping control
(If the link is broken, try copy/paste the entire line to the Internet Explorer, if that's what you are using.)
0 Kudos