How to find missing records in Join

[JulieWilliams]

Hello,

I'm joining a large list of parcel numbers (from an Excel ss) to my parcel layer to extract the matching parcels. My issue is that I need to know which parcels from the list did not match up with a polygon. My join resulted in 11141 matches but there are 11268 parcel numbers in my ss . I need to find out which 127 parcels did not join and research those. Is there a way to do this?

Thanks!
Julie

[RichardFairhurst]

Hello,

I'm joining a large list of parcel numbers (from an Excel ss) to my parcel layer to extract the matching parcels. My issue is that I need to know which parcels from the list did not match up with a polygon. My join resulted in 11141 matches but there are 11268 parcel numbers in my ss . I need to find out which 127 parcels did not join and research those. Is there a way to do this?

Thanks!
Julie

Excel is unreliable and I never use it, so if it causes problems doing what I recommend, convert it to a geodatabase table and save yourself a lot of frustration.

Two ways to do this with a real table. 

1.  Create a relate and select all records in the converted Excel table, then transfer the relate selection to the parcels.  Reverse the record selection within the parcel feature class.  That is your set of unmatched parcels.

2.  Create a join and select all records where the join table fields that are required are Null.  That is the set of parcels that are unmatched.

Once you have the selection of unmatched records you can copy the records and paste them to a blank spreadsheet page.  Then rearrange the columns to match the main spreadsheet as best as you can and copy the new records to the end of the original spreadsheet page.

If you convert the spreadsheet to a table, then export the selected unmatched parcel records to a feature class/table and use the load tool to append them to your table, or use the Merge or Append tool (no test option).  Depends on how well the field names and types match.

[ScottMoyer]

Julie,
When you are joining the table to the parcel polygons you may be selecting 'Keep all records' in that you don't want all of the other non-matching polygons. Yes? That's understanable if you have many parcel polygons. As in Richard's suggestion #2, join the polygons to the table (reverse how you may have already done it) and then select 'Keep all records' so your table will have matches and non-matches for assessment.
Scott

[JulieWilliams]

Thanks so much for the replys. However, I don't think those solutions will work in my case. My parcel layer has over 400,000 parcels in it and I only need to know which ones from the ss are missing. Sorry if I'm not understanding tho.

Here's the whole story: I have an Excel ss that I converted from a txt file of 11,268 parcel numbers that I need to plot on a map. Our parcel layer has over 400,000 parcels and so I joined the ss to it and the resulting matches were 11,141 parcels (which I thought was pretty good). And I selected the "keep matching parcels" option. Now I need to know which 127 parcels from the list are missing so that I can research those parcels and obtain the new parcel numbers to add to my map. I'm assuming the missing parcels did not match because the numbers changed due to spilts or mergers.

Thanks again for any help!

[ScottMoyer · Accepted solution]

I didn't write was I was thinking, sorry. You are joining the table of 11268 records with the 400k+ parcel polygons and selecting 'Keep only matching records' (I previously miswrote this) and you get 11141 matches. 127 are not in the joined output.
Here's an idea how to find the 127 missing parcel records.
1. Remove the existing join.
2. Do another join by right clicking on your Excel table in an mxd (the way I do it)
3. In the Join Data window: 1. 'Choose field in this layer...' will be from your table, 2. 'Choose the table...' will be the parcel polygon feature class, 3. 'Choose the field...' parcel polygon field, and then 'Keep all records'.
4. The result should be your table with all 11268 records, with some having a null parcel ID. The null are your missing records.
* If you have many unnecessary fields in your parcel polygon fc for your analysis, before doing all this you may want to turn those fields off so they're not in your final table join output.
Scott

[JulieWilliams]

Thanks so much! It worked perfect!!! I had no idea you could reverse the process that way. I'm so happy 🙂
Oh and by the way, I forgot to mention that I had imported the Excel ss into my gdb as a gdb table. Thanks again to you both!

[RebeccaStrauch__GISP]

just in case you haven't figured it out yet, you can select the records in the join that don't have a match with:
      <fieldname> IS NULL

where the fieldname is one of the fields in the parcel feature class.    The reverse is, i.e getting the ones that DO match:
    <fieldname> IS NOT NULL

Figuring our how to select <NULL> field value is sometimes hard to figure out or remember (for me it is anyway)

[ScottMoyer]

Julie,
When you have a moment please finalize this thread by logging in and marking it as answered. Thanks,
Scott