Скачать или смотреть How to Remove Duplicate Addresses in Google Sheets | Tutorial

Have a list of addresses that you want to dedupe? There are several tools out there like Google Sheets or Excel, that you can use to remove duplicates from a spreadsheet.

However, tools like these can’t find all duplicates if the addresses have not been standardized, because they’re looking for exact duplicates. They don’t know if Ave is the same as Avenue, or if Rainer Ave is the same as Rainer St.

We can try to standardize manually with lots of Google Maps searches, but I’ll show you how we can fix this standardization issue in no time flat.

Here's a list of addresses that I want to dedupe. I've got them open in Google sheets in their own tab.

Go to Smarty.com. Or click the link in the description. Then at the top, hover over Resources, and under Demos select Bulk Address Validation.

Select whether you’re validating US addresses, international addresses, or if you’re looking to match ZIP Codes to US cities and states. In this example, we’ll stick with US addresses.

Ummkay, so I’ve pasted my processed data into a new tab in Sheets. There are a ton of new columns because Smarty gives heaps of additional data for each address. In fact, you can see in my list that there are several marked as “No Match,” meaning if I were to send mail to that address it wouldn’t work.

For more information on that check out some of our other videos.

Let’s get back to removing those dastardly duplicates, eh? The first three columns that I pasted are my original addresses, still with all of their errors and non-standardized-ness. But if I look over to columns H through L those are the same addresses, but now they’re all standardized. So I’ll copy those columns and paste them over my second tab.

Ok, check this out—My original data didn’t have a ZIP Code but these new addresses do. All I need to do is the same deduplication song and dance I did at the beginning of this video. Go to Data, and then go to Data Cleanup, and then Remove Duplicates. 1 2 [3]

A dialogue box pops up, and I’ll mark column headers, and that I want it to check for duplicates only in the address column.

This time it did a much better job.

There you go, that’s how you clean up your duplicate addresses.

Later Sk8er!