Cleaning Neighborhood Address Data
The steps below guide applicants and residents through the process of eliminating duplicate addresses for neighborhood notification requirements. If you need assistance in gathering address data for the purposes of neighborhood notifications, please see the Meeting Address Instructions page.
1. Download the Data
Download the CSV files for both the Owner Addresses and the Site Addresses (also considered the resident addresses). The instructions below use Microsoft Office Excel to complete the necessary steps. The specific examples below are being processed in the desktop application, though this process can be replicated in the online Office365 version of Excel. This methodology does not specifically require Excel to complete this task. Other common spreadsheet management applications, such as Google Sheets, have their tools and menu options arranged similarly to Excel.
These instructions assume that you have recently downloaded addresses using the Gathering Neighborhood Addresses Instructions page. If you not collected both of your address lists, please use the Gathering Neighborhood Addresses Instructions page to download the CSV files for the addresses near your project area.
2. Confirming and Preparing data
When compiling a mailer for neighborhood meetings, you should have two files downloaded, "selected_owner_addresses.csv" and "selected_residentaddresses.csv".
- Open the file named selected_owner_addresses.csv. Click File > Save As and save this file as an .xlsx spreadsheet. Give the file a name that helps it stand out from the other two files. For example: "[PROJECTNAME]_ALL_Addresses.xlsx" (the name of the file does not effect the cleaning process)
- Click anywhere within the newly saved spreadsheet
- Click the Insert tab located up top. Then select Table. A pop-up window will appear with all the data selected. Make sure that "My table has headers" is checked on. Click Ok.
- Your spreadsheet is now formatted into a table that will be easily sorted and filtered.
Keep this file open throughout the remainder of the steps.
3. Cleaning the Site/Resident Addresses
- Open the file labeled selected_residentaddresses.csv.
- Right-click on the column labeled "Resident Zip" and select Insert
- There should now be a blank column in-between Resident City and Resident Zip
- Type in "State" in the top row
- Type "NC" into the second row. Copy and paste NC into all of the remaining rows
- Alternatively, you can double-click the little square located in the corner of the first cell with NC. This will copy the data down to the last row.
4. Merging the Data
- In the site/residential addresses file, select all the information by pressing CTRL+A to select all. Copy this information by pressing CTRL+C, or right-click and select Copy
- In the newly saved spreadsheet table with the owner addresses, scroll down to the last row. Select the first empty cell underneath the Owner Address column.
- Paste your data here by presing CTRL-V or by right-clicking this cell and selecting "paste"
- Last, click on the far left number that has the row which contains Resident Address, Resident City, State, and Resident Zip. Delete this row by right-clicking on the row number and clicking Delete.
5. Sort addresses for filtering
While on the Home tab, on the right side of the top menu, click the Sort & Filter button, then select Custom Sort. A pop-up window will appear labeled "Sort". First click "+Add Level" so that you have two filter lines below. The first line will need to have Sort by "Owner Address"; keep "Cell Values" and "A to Z" on by default. On the second line labeled "Then by", select "Owner Name". As before, keep "Cell Values" and "A to Z" on by default.
This will now sort the data so that addresses so that if an address has an owner, the owner name will appear first and the blank site address will appear second.
6. Highlighting Duplicates [Optional Step]
This next step isn't necessary but is helpful when looking at data.
- Click on column label named "Owner Address" so that it selects the entire column.
- In the top menu ribbon, click Conditional Formatting then hover over Highlight Cell Rules and click Duplicate Values.
- A pop-up will appear. You can keep the default setting to red or change the color to one of the alternative options. Click Ok
All locations with duplicates will now be highlighted. The following step will remove the duplicates.
7. Removing Duplicates
With the data in order of owners names listed before duplicate blank addresses, we can now remove duplications so that owners do not get multiple letters. When removing duplicates, the first instance of a duplication is kept while any other duplicates are removed.
- In the top menu, click the Data tab. In the menu ribbon, click Filter. A pop-up window will appear with the title Remove Duplicates. Click the box labeled Unselect All. All of the items below should now be unchecked. Click only the Owner Address line so that it is the only item checked. Click OK.
- A new pop-up will appear with the number of duplicate values and the remaining lines of text. (Note: Your value may be different than the one in the picture example. The number of duplicates will vary depending on project locations.)
8. Adding "Current Resident/Occupants"
Now that the duplicates are removed, we should have a list of owner addresses and residents/occupants of properties within the project area. The last major step is to label these blank addresses for those occupants. The City and County of Durham does not maintain a list of renters or occupants of properties; only primary owners. To address these individuals, it is recommended to identify these individuals as Residents or Occupants.
- At the top of the column labeled "Owner Name", click the arrow next to the label so that a drop-down menu appears.
- Click Sort A to Z. This will place the owner names first and the remainder of the cells with blank names afterwards.
- Type into the first blank name cell either of the following titles you prefer "Current Resident" OR "Current Occupant"
- Copy the cell you just made and paste it into the blank cells until you reach the end.
- A fast tip to complete this easily is by double clicking the little black square in the lower right corner of the first Current Resident/Occupant cell. This will copy the information all the way down to the last empty cell in your data.
8. Save and Use for Mailing
Your address data is now cleaned of duplicate addresses and ready for your desired mailing.
There are different types of mailing methods that this data can be used to contact local residents. Common methods to print addresses include letters, sticker labels, postcards, flyers, newsletters, or other similar mailers received via the postal service.
Since there are numerous methods to produce materials to distribute this information, we are unable to provide trainings for every type of publication. If using Microsoft Office, you can go to Microsoft Office Mail Merge website to learn some common mail merge methods. There are many YouTube and other resources available by searching "How to Create an Address Mail Merge".