Click to Enlarge I often have to send large numbers of postcards, either for business or for an non-profit with which I'm involved. The mailing lists come from a variety of sources, and several or more are often combined, depending upon the application. So it's not uncommon to have duplicates. And of course duplicates are a waste of money as well!
Some of the mail houses that I've used will scan for duplicates, and some will normalize the addresses, but some don't, so it's always best to be prepared to clean your own list prior to utilizing. It's a fairly simple process to scan for duplicates using Excel or Google Sheets, but the problem arises when the addresses aren't uniform -- There's 125 North Main St, 125 N. Main St, 125 n Main Street, etc -- and most spreadsheets can't discern the difference. I looked on online for an Excel solution, and there are a few, but they appear to be fairly complex (and involve APIs, fairly complicated macros, etc.). So after a little trial/error I came up with a brute force method for Excel/Google Sheets that seems to work well. I've included cells to adjust directions (North, South, East, West, No., etc.) and the most common street suffixes (Street, Lane, Drive, Avenue, Circle, etc). We don't have any Northwest/Northeast/Southwest/Southeast etc addresses in the area, but they could be added (North West is a little more problematic, but could still be addressed with a little more fiddling around, I suppose).
With this spreadsheet, simply cut/paste your addresses into the appropriate place (you may need to add a few columns to the left to accommodate all of your address fields, but make sure to copy/paste the street address portion into the column noted in blue). Once you've pasted in your addresses, then copy/paste the example rows to match the number of rows you've added. Copy from the "Results" column and "Paste as Values" into your addresses column and you should be all set. Once the addresses have been normalized, you can sort them and have Excel/Google Sheets highlight and/or delete the duplicates.
Hope this helps! Let me know if any questions or feedback. Thanks.
Some of the mail houses that I've used will scan for duplicates, and some will normalize the addresses, but some don't, so it's always best to be prepared to clean your own list prior to utilizing. It's a fairly simple process to scan for duplicates using Excel or Google Sheets, but the problem arises when the addresses aren't uniform -- There's 125 North Main St, 125 N. Main St, 125 n Main Street, etc -- and most spreadsheets can't discern the difference. I looked on online for an Excel solution, and there are a few, but they appear to be fairly complex (and involve APIs, fairly complicated macros, etc.). So after a little trial/error I came up with a brute force method for Excel/Google Sheets that seems to work well. I've included cells to adjust directions (North, South, East, West, No., etc.) and the most common street suffixes (Street, Lane, Drive, Avenue, Circle, etc). We don't have any Northwest/Northeast/Southwest/Southeast etc addresses in the area, but they could be added (North West is a little more problematic, but could still be addressed with a little more fiddling around, I suppose).
With this spreadsheet, simply cut/paste your addresses into the appropriate place (you may need to add a few columns to the left to accommodate all of your address fields, but make sure to copy/paste the street address portion into the column noted in blue). Once you've pasted in your addresses, then copy/paste the example rows to match the number of rows you've added. Copy from the "Results" column and "Paste as Values" into your addresses column and you should be all set. Once the addresses have been normalized, you can sort them and have Excel/Google Sheets highlight and/or delete the duplicates.
Hope this helps! Let me know if any questions or feedback. Thanks.
RSS Feed