The Scenario
It is a Wednesday afternoon and you've just inherited a merged CRM export. Two regional teams had been maintaining separate location lists for years, with no shared format and no deduplication process. The combined file has 5,000 rows in a Google Sheet — business name, address, city, state, zip in columns A through E. You can already see the problem: "123 Main St" and "123 Main Street" are probably the same location. "Suite 100" appears in some rows and not others. Address standardization by eye across five thousand rows is not something you are willing to do.
Your manager wants a clean, deduplicated master list by Friday's data review.
The bad version:
- Run a fuzzy string match on the address column and try to manually review the borderline cases — of which there will be hundreds.
- Export to Python or another tool, use a library to normalize the addresses, hope the normalization is consistent enough to catch all variants.
- Go back and forth with the other team's point of contact to reconcile which canonical version of each location is correct.
This is the kind of work that takes a full day and still isn't fully trusted by the time you present it. The real issue is that raw address strings are the wrong thing to compare — two strings can look different and be the same place, or look identical and be different locations in a strip mall.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent inside your Google Sheet. It connects to Placekey's bulk API, gets a canonical location ID for every row, and writes it back — then you can flag duplicates on the ID itself, which is exact rather than fuzzy.
Here is the prompt:
Bulk-submit all addresses in columns B–E to Placekey in batches of 100 and write the Placekey to column F, then mark any row whose Placekey already appears in a row above as 'DUPLICATE' in column G
What You Get
- Column F gets a Placekey for every row where the address resolves.
- Column G gets 'DUPLICATE' for any row whose Placekey matches a Placekey already seen in an earlier row — first occurrence stays clean, all subsequent ones are flagged.
- Rows where Placekey returns no match get a blank in column F, so you can filter and review them separately.
- You have a dataset you can sort by column F to visually confirm which records collapsed to the same canonical ID.
What If the Data Is Not Quite Ready
Some rows have "St" vs "Street" vs "St." — normalize before submitting
Before submitting to Placekey, normalize common street suffix variants in column B (St, St., Street → Street; Ave, Ave., Avenue → Avenue; Blvd → Boulevard) across all rows, then submit in batches of 100 and write Placekey to column F
Some locations are outside the US and won't resolve — filter them first
Submit only rows where column E (country) is 'US' or where column E is blank and column D is a valid US zip code to Placekey bulk API in batches of 100 — write Placekey to column F and 'SKIPPED-INTL' to column G for rows that were excluded
The two source tabs haven't been merged yet — deduplicate across both
Combine rows from the East Region tab and West Region tab into a single batch, submit all addresses to Placekey bulk API, write Placekey back to column F in each original tab, and flag any row in West Region whose Placekey already appears in East Region with 'CROSS-DUP' in column G
The full pipeline: clean, submit, deduplicate, and summarize in one shot
Normalize street suffixes and strip trailing suite/unit text from column B, submit all rows to Placekey in batches of 100 and write the Placekey to column F, flag duplicates with 'DUPLICATE' in column G, then add a summary in cell I1 showing total rows submitted, unique Placekeys, and duplicate count
That last prompt does the cleanup, the API call, the deduplication, and the summary in one pass — useful when you want a single audit trail rather than a multi-step process.
Try It
Open the merged CRM export in Google Sheets and get the 7-day free trial of SheetXAI. Ask it to run the deduplication pipeline in one prompt. Also useful: bulk Placekey assignment and the full Placekey overview.