Small Business Stack

Find Duplicate Companies by Domain Before a HubSpot Import

How do I find companies that share a domain in Google Sheets before importing them into HubSpot?

Strip the protocol with one helper formula, cut www. and any path with a second, then flag repeats with =IF(C2="","",IF(COUNTIF($C$2:$C$9,C2)>1,"DUPLICATE","")). HubSpot uses the Company domain name to deduplicate companies on import, so settle each flagged group in the sheet before you upload the file.

=IF(C2="","",IF(COUNTIF($C$2:$C$9,C2)>1,"DUPLICATE",""))

What you need

  • Google Sheets with edit access to a copy of the company list
  • A website or domain column for every company row
  • Access to HubSpot's company import tool

Company lists collected from forms, spreadsheets and old CRMs rarely agree on how to write a website. One row says https://www.Morgan-Sample.example/contact, another says morgan-sample.example. HubSpot treats the domain as the company’s identity during an import, so those two rows point at the same company. Two helper columns in Google Sheets reduce each website to a bare domain, and a COUNTIF column flags every domain that appears more than once. You then decide what to do with each flagged group before anything reaches HubSpot.

Why does HubSpot care about the domain when you import companies?

HubSpot’s article on deduplicating records says that on import, the primary and secondary Company domain name values are used to deduplicate companies, unless you pick a custom unique value property as the identifier instead. Three consequences follow from that page:

  • If an imported row has the same domain as an existing company, HubSpot adds the new information to that existing company rather than creating a second one.
  • If more than one existing company already has that domain, the row produces an import error and that company is not imported.
  • If your file has no domain column and no other unique identifier, each row is imported as a new company.

The import file formatting guide adds that the Company domain name property only accepts values up to the top-level domain, and that extra path text is removed on import. It also says that if you switch to a custom unique property and do not want duplicate domains, you should remove them from your file before importing.

HubSpot’s pages do not say how two rows with the same domain inside one file are resolved, and they do not describe how www. or other subdomains are compared. That is the gap this sheet covers. Rather than guessing how HubSpot will treat a repeated row, you find the repeats yourself and choose which row survives.

What do the three helper formulas do to a website column?

Put the raw website in column A and leave it untouched. Column B removes the protocol, column C removes www. and anything after the first slash, and column D flags repeats.

In B2, paste this formula. It lowercases the text, trims outer spaces, and starts reading after // if there is one:

=IF(TRIM(A2)="","",LOWER(MID(TRIM(A2),IFERROR(FIND("//",TRIM(A2))+2,1),LEN(TRIM(A2)))))

FIND in Google Sheets returns an error when // is missing, and IFERROR turns that error into position 1, so a website typed without https:// passes through unchanged.

In C2, paste this formula. It keeps only the text before the first slash, which drops paths and trailing slashes, then removes a leading www.:

=IF(B2="","",IF(LEFT(B2,4)="www.",MID(LEFT(B2,FIND("/",B2&"/")-1),5,LEN(B2)),LEFT(B2,FIND("/",B2&"/")-1)))

Adding "/" to the end of B2 before searching means FIND always has a slash to land on. A bare domain like casey-demo.example comes back whole instead of turning into an error.

In D2, paste the duplicate flag:

=IF(C2="","",IF(COUNTIF($C$2:$C$9,C2)>1,"DUPLICATE",""))

COUNTIF counts how many cells in the locked range $C$2:$C$9 match this row’s domain. Anything above one gets the flag. Change $C$9 to your real last row, and keep the dollar signs so the range does not move when you fill down.

Fill all three formulas down through your last row. Each row’s formula should point at its own row: B5 reads A5, C5 reads B5, and so on. If you would rather not use separate columns for each step, you can hide B and C once you trust the results.

Which domains should the sample rows produce?

The table uses made-up companies on the reserved .example domain. The website text is shown exactly as typed, including stray spaces in row 4.

Row A: Website C: Domain D: Flag
2 https://www.Morgan-Sample.example/contact morgan-sample.example DUPLICATE
3 morgan-sample.example morgan-sample.example DUPLICATE
4 HTTP://Riley-Placeholder.example/ riley-placeholder.example DUPLICATE
5 www.riley-placeholder.example riley-placeholder.example DUPLICATE
6 shop.avery-example.example shop.avery-example.example
7 avery-example.example/pricing avery-example.example
8 Empty cell Empty result
9 https://casey-demo.example casey-demo.example

Rows 2 and 3 match once the protocol, www., capital letters and the /contact path are gone. Rows 4 and 5 match even though one has outer spaces and an uppercase HTTP://. Row 6 is not flagged against row 7: the formula only strips www., so shop.avery-example.example stays a separate value. Row 8 stays blank rather than being counted as a duplicate of other blanks.

Row 6 and row 7 are worth a human look. A shop subdomain may belong to the same business, or it may be a separate brand with its own HubSpot record. The formula cannot tell, and HubSpot’s import documentation does not say how it compares subdomains, so this is a judgment call for whoever owns the company list.

How should you settle each flagged group before uploading?

Sort the whole sheet by column C so matching domains sit together. Select every column when you sort, not just C, or the domains will drift away from their company names and owners.

For each DUPLICATE group, pick one row to keep. Usually that is the row with the most complete name, owner and address. Copy any useful details from the other rows into it by hand, then move the extra rows to a separate Held tab instead of deleting them. You can still check them later if someone asks where a company went.

Some flagged groups are really two companies that share a website, such as two branches of one franchise or a parent with a subsidiary. If they need separate HubSpot records, a domain-based import will not keep them apart. HubSpot’s deduplication article says you can select a custom unique value property as the identifier instead, and the import formatting page notes that this allows duplicate company domains. Decide that before the import, not after.

Also check the companies already in HubSpot. A domain that appears once in your file can still match an existing company, which means the import will update that record. If two existing companies share a domain, the row will fail with an import error. Exporting your current companies and running the same formulas on that export shows both cases ahead of time.

How do you build the import file from the cleaned sheet?

Make an Export tab. Copy the kept rows and use Paste special, then Values only, so the file contains plain text rather than formulas. Include the company name, the cleaned domain from column C, and any other fields you plan to map. Leave out the raw website column unless it maps to a separate property, since two domain-like columns make it easy to map the wrong one during import.

With the Export tab active, download it as a CSV. In HubSpot’s import tool, map the cleaned column to Company domain name and read the preview before you confirm. If you are importing contacts at the same time, this guide to cleaning a contact spreadsheet for a HubSpot import covers the contact side, and normalizing CRM email addresses explains why email text needs its own rules. More guides for the tools a small office runs on are on the small-business stack topic page.

Where does this domain cleanup fall short?

The formulas handle the common mess: protocols, www., paths, trailing slashes, capital letters and outer spaces. They do not handle everything.

Input What the formula returns What to do
morgan-sample.example?ref=ads Keeps ?ref=ads because there is no slash Delete the query text by hand, or add a FIND on ?
morgan-sample.example:8080 Keeps the port number Remove the port by hand
shop.avery-example.example Keeps the subdomain Decide whether it is the same company
Morgan Sample Ltd Returns the name as if it were a domain Look up the real website or leave the field blank
An email address Returns the full address Take the part after @ if that is the company’s domain

Free email domains deserve a special mention. If someone filled a website column with a personal email provider’s domain, every company with that value will be flagged as a duplicate of each other. Clear those cells instead of merging them.

If you run the same import every week, helper columns filled by hand will drift. A controlled import process with saved rules suits that better than a one-off sheet. And if you need to search a list for repeated values in other places, finding same-name files in Google Drive uses the same idea: flag first, then decide, and never delete as the first step.

Sources

Esc