How to Create a Deduplicated List of All Accounts in a CRM

A deduplicated list of all accounts in a CRM is a list with one row per real business, with every repeat merged into that row. You create it by exporting all accounts, building match keys in Excel (website domain, phone number in international format, cleaned name plus city), grouping the rows that share a key and merging each group inside the CRM. The hard part happens in a spreadsheet, so the method works in any CRM. This guide gives the formulas, a table of match keys, a worked example and the setting that stops the duplicates coming back.

The short answer

1. Export every account to Excel, including the record ID and the created date.
2. Add three helper columns: a domain key, a phone key and a name-plus-city key.
3. Count how often each key appears and sort so the repeats sit in blocks.
4. Pick the record to keep in each block, usually the one with the most history, and merge the others into it inside the CRM.
5. Switch on the CRM's duplicate rule on the website or phone field, and check every future import against the same keys.

Why do CRM duplicates build up?

An account is a CRM record for a company; contacts are the people who work there, and leads are records not yet qualified. Duplicate accounts are two or more records for the same company. They appear whenever a company reaches the CRM by a second route under a slightly different spelling: imports from lead lists that overlap, reps typing a company by hand because the search box did not find "Al Noor Trading" when it was saved as "Alnoor Trading LLC", and website forms or tool connections that create a record before they search for one.

The fix is the same in every CRM; only the menu names differ, so this guide describes what to do rather than where to click.

Which match keys find duplicate CRM accounts?

A match key is a cleaned copy of one field, built so that two spellings of the same thing become identical. Names alone are weak keys: "Gulf Star Interiors L.L.C." and "Gulfstar Interiors" are the same company and never match as typed. Use three keys and read them in this order.

Shared phone numbers are the trap: business centres and serviced offices give many companies one reception line, so count a phone match as a duplicate only when the name key or the domain key agrees, or when a person has looked at both records.

How do you build the match keys in Excel?

Put the export on one sheet with the name in column A, the website in column C, the phone in column D and the city in column F.

Domain key

One formula (Excel 365 or 2021) does the whole clean:

=LET(w,LOWER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,"https://",""),"http://",""),"www.","")),IFERROR(LEFT(w,FIND("/",w)-1),w))

Phone key

The first formula keeps digits only; the second adds the country code (971 here; 966 for Saudi Arabia, 44 for the UK; add a 1 in front of 10-digit US numbers):

=TEXTJOIN("",TRUE,IFERROR(MID(D2,ROW(INDIRECT("1:"&LEN(D2))),1)*1,""))
=IF(LEFT(E2,2)="00",MID(E2,3,20),IF(LEFT(E2,1)="0","971"&MID(E2,2,20),E2))

Name and city key

Add a SUBSTITUTE layer for each legal ending common in your country (FZE and FZCO in the UAE, W.L.L. in Qatar and Bahrain, Limited in the UK, Inc in the USA):

=LOWER(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"L.L.C.",""),"LLC",""),"Ltd",""),".","")))&"|"&LOWER(TRIM(F2))

How do I create a deduplicated list of all accounts in a CRM?

1. Export all accounts. Include the record ID, name, website, phone, city, owner, created date and, where the export offers it, the number of linked deals. Save a copy before you change anything.
2. Build the three keys with the formulas above and fill them down.
3. Count each key. Beside each key column, =COUNTIF(G:G,G2) shows how many rows share the value in G2. Filter for counts above 1.
4. Sort on the key column so each block of repeats sits together.
5. Choose the survivor. In each block, keep the record with the most linked deals, notes and activities, or the oldest when they are equal. Mark it "keep" in a new column and mark the others "merge into" followed by the survivor's record ID.
6. Review the name-key blocks by hand. Two different companies can share a common name in one city, so name matches need a look; domain and phone matches are usually safe.

The spreadsheet steps are the same ones used to clean any list. Our guide on how to remove duplicate leads goes further into branches and shared reception numbers.

How do you merge the repeats back into the CRM?

Two routes work. Pick the one your CRM supports best.

1. Merge inside the CRM. Open each block, use the CRM's merge function and choose the survivor. The CRM moves contacts, deals, notes and emails from the losing records onto the survivor and keeps one record ID. Slower, but nothing is lost.
2. Re-import with record IDs. Export your sheet with the "keep" and "merge into" columns, then use the import tool to update records by ID: fill the survivor's blank fields from its block, reassign the contacts and deals of the losing records, and only then delete or archive them.

Never delete a losing record before its history has moved: a deleted record takes its notes, its open deal and its opt-out flag with it, and an opted-out business can then hear from you again.

Worked example: 2,400 accounts in a fit-out company's CRM

Say a Dubai fit-out company has used its CRM for three years and holds 2,400 accounts, built from exhibition lists, website forms and reps' typing. After the export and the three keys:

• 310 rows share a domain key with at least one other row, in 140 blocks.
• 220 rows share a phone key, but 60 of those sit in business centres in Business Bay and JLT where the name keys differ. Those stay separate.
• 90 rows match only on name and city. After a review by hand, 70 are real duplicates and 20 are different companies with similar names.

The team merges about 230 blocks over two afternoons. The count drops from 2,400 to roughly 2,090, every open deal survives, and four companies that had asked to stop hearing from the firm are now flagged once instead of hidden on a second row.

How do you stop duplicates coming back?

Cleaning once is a project. Keeping the CRM clean is one setting plus one habit.

1. Switch on the CRM's duplicate rule. Most CRMs can block or warn when a new account matches an existing one on a chosen field; point it at the website or phone, not the name.
2. Make the key field required for new accounts, so the rule always has something to compare.
3. Check every import before it goes in. Run the keys on the new file, compare them against a fresh CRM export with =COUNTIF(), and import only the rows that are new.
4. Search before create in every connection. A webhook or form that creates records should first look for an existing account by domain or phone. Our guide on how to add leads to your CRM automatically shows that check step by step.
5. Repeat the export and key count every quarter, as part of the routine in our lead list hygiene checklist.

Where Rovero fits

Rovero is B2B lead generation software for Windows. It finds the businesses of a chosen type in a chosen city, area by area, and collects the emails and phone numbers they publish on their listings, websites and social pages. The key feature here is zero duplicates: every business Rovero finds is checked against everything already saved in your account, across all searches, so a company found in two areas or under two categories is saved once.

That means the lists you send to the CRM start clean. Export to Excel or CSV with the columns you choose, or post each lead straight into the CRM with the send to CRM and webhook feature, which works with anything that offers a REST (JSON) endpoint.

One honest limit: Rovero de-duplicates the leads it saves, not an old CRM or spreadsheet, so the accounts already in your CRM still need the export-and-key method in this guide.

Frequently asked questions

How do I create a deduplicated list of all accounts in a CRM without a special tool?

Export all accounts to Excel with their record IDs, add a domain key, a phone key and a name-plus-city key with the formulas in this guide, and count each key with COUNTIF. Rows that share a key are your duplicate blocks; merge each block inside the CRM, keeping the record with the most history.

Which field is best for finding CRM duplicates?

The website domain is the most reliable, because a company rarely has two. The phone number in international format comes second, with a check for shared reception lines. Use the name only for records with no website or phone.

Should I merge or delete duplicate accounts?

Merge. A merge moves contacts, deals, notes and opt-out flags from the losing record to the survivor, so nothing is lost. Deleting removes history and can make an opted-out business receive messages again from the record that remains.

Will the CRM's duplicate rule catch everything?

No. It compares one field, so a company entered with a new website and a new phone number will slip through. It stops most repeats, which is enough when you pair it with a key check on every import.

Next step

Export your accounts today, add the domain key column and run COUNTIF on it: the number of rows with a count above 1 tells you the size of the job. When you want new leads that arrive without duplicates, see what Rovero does.