What Excel formulas identify which duplicate record to keep when deduplicating HubSpot exports

Choosing which duplicate record to keep requires more than guesswork. You need formulas that evaluate data completeness, recency, and engagement levels to make smart decisions about your master records.

These Excel formulas will help you systematically identify the best record from each duplicate group using objective criteria.

Smart duplicate detection with enhanced formulas using Coefficient

HubSpotCoefficientWorking with livedata throughlets you implement sophisticated duplicate detection formulas that update automatically as your data changes.

How to make it work

Step 1. Set up basic duplicate identification formulas.

Useto identify records sharing the same email and company. This formula returns TRUE when duplicates exist, giving you a clear flag for each record group.

Step 2. Find the most recent record in each duplicate group.

Applyas an array formula to locate the most recently created duplicate. Replace column D with your “Created Date” field to prioritize newer records automatically.

Step 3. Identify records with the most recent activity.

Useto find the record with the most recent activity date. This helps you keep the most engaged contacts as your master records.

Step 4. Create a data completeness scoring system.

Build a formula liketo count filled fields. Weight different properties by multiplying by importance factors:gives email addresses triple weight.

Step 5. Combine criteria for master record selection.

Create a comprehensive scoring formula:where F2 contains your combined completeness and recency scores. This automatically flags the best record in each duplicate group.

Make data-driven deduplication decisions

Try Coefficient freeThese formulas eliminate guesswork by scoring records on objective criteria like completeness and engagement. Ready to implement systematic duplicate detection?and let your formulas do the heavy lifting.