Entity Resolution and Data Reconciliation in Google Sheets
When customer records, product lists or reference datasets live in separate spreadsheets, identifying which rows refer to the same real-world entity is the hardest part of data reconciliation. This guide walks through a practical entity resolution workflow you can run entirely inside Google Sheets using Flookup Data Wrangler.
What Is Entity Resolution?
Entity resolution (ER) is the process of determining when two or more records refer to the same real-world object, person or organisation. Unlike exact matching, ER accounts for differences in spelling, formatting, abbreviations and data entry errors. It is a core component of data reconciliation: the task of making two datasets consistent with each other.
In Google Sheets, entity resolution typically involves comparing rows across columns such as name, email, phone or address. A pair of records might read "Acme Corp" and "Acme Corporation" or "123 Main St" and "123 Main Street". A naive exact-match join would miss these links; entity resolution catches them.
| Approach | Accuracy | Effort | Best For |
|---|---|---|---|
| Exact matching | Low (misses variants) | Minimal | Clean, controlled identifiers |
| Fuzzy matching | High | Medium | Free-text names and addresses |
| Rule-based blocking + scoring | Very high | High | Large datasets with many fields |
| Machine learning (embedding) | Highest | Very high | Unstructured or multilingual data |
Why Entity Resolution Matters for Data Quality
Duplicate or fragmented entity records cause reporting errors, wasted marketing spend, poor customer experience and compliance risks. If your CRM lists a customer under "J. Smith" in one sheet and "John Smith" in another, you might send two mailers, miss a support ticket or double-count revenue.
ER directly improves data completeness, consistency and accuracy. When you reconcile records properly, every downstream process (dashboards, mail merges, AI pipelines) starts from a single source of truth.
The Entity Resolution Workflow
A robust entity resolution workflow in Google Sheets follows four stages:
- Standardise: normalise values so comparisons are consistent.
- Block and match: group likely-similar records to reduce comparisons.
- Score similarity and resolve: compute a similarity score and decide match or non-match.
- Merge and deduplicate: consolidate matched records into one canonical row.
Flookup Data Wrangler provides dedicated functions for each stage. The sections below show how to apply them.
Step 1: Standardise Your Data
Before any comparison, standardise both datasets so the same real-world value is represented identically. Common normalisation steps include lowercasing, removing extra whitespace, expanding common abbreviations and stripping punctuation.
The Text Standardisation function handles this in one pass. It trims whitespace, collapses multiple spaces, converts to a consistent casing and optionally expands abbreviations you define. Apply it to every column you plan to match on (name, address, company) across both datasets.
Standardisation alone catches many simple mismatches. The cleaner your inputs, the fewer false negatives your fuzzy matcher will produce.
Step 2: Block and Match Records
Comparing every row in Dataset A against every row in Dataset B produces an O(n × m) explosion. For 10 000 rows each, that is 100 million pairs, far too many for a spreadsheet. Blocking reduces this by grouping records that share a simple key, such as the first letter of a surname, a postcode prefix or a normalised company name.
The Fuzzy Match function performs the blocking and matching step together. It searches a lookup range for the closest match to a search value and returns an aligned result. Its built-in blocking behaviour limits the search to plausible candidates so you can run it across thousands of rows without performance issues.
Use it like a fuzzy lookup: select your source value, specify the lookup range and the function returns the best match. For cross-dataset reconciliation, this is your core matching engine.
Step 3: Score Similarity and Resolve
Not every match is equally certain. A score between 0 and 1 quantifies how similar two strings are, so you can set a threshold above which a pair is accepted as a match and below which it is flagged for review.
The Similarity Scoring function returns a similarity score for two input strings. It handles word reordering, typos and partial overlaps effectively.
A score of 1.0 means the strings are effectively identical after normalisation. Scores above 0.85 are usually safe to auto-approve. Scores between 0.6 and 0.85 are candidates for manual review. Below 0.6, the records are probably different entities.
Use the Similarity Scoring function alongside the Fuzzy Match function to audit the quality of every matched pair. This creates an auditable reconciliation trail, essential for finance, healthcare or any regulated sector.
Step 4: Merge and Deduplicate
Once you have identified which records refer to the same entity, the final step is collapsing them into a single canonical row. Within a single sheet, the Deduplication function removes near-duplicate rows by comparing one or more columns and keeping only the highest-ranked version of each group.
For cross-sheet reconciliation, a Fuzzy Match followed by a manual merge of additional columns gives you full control over which fields survive.
After merging, validate the results by running the Similarity Scoring function on a sample to confirm the expected similarity distribution. A sudden drop in scores often indicates that your blocking key was too restrictive or that a new data variant appeared.
Real-World Use Cases
CRM data cleaning. A sales team imports leads from multiple sources (trade shows, web forms, purchased lists) and ends up with three variations of the same company. Entity resolution collapses them into one account record with consolidated contact information.
Product catalogue unification. An e-commerce business merges supplier feeds where the same product appears under different SKUs, names or descriptions. Fuzzy matching on product title and brand resolves the overlaps and the Deduplication function produces a clean master catalogue.
Patient record reconciliation. A healthcare provider receives patient data from several clinics. Names like "Bob Williamson" and "Robert Williamson" need to be linked without false positives. Score thresholds and manual review steps built with the Similarity Scoring function keep accuracy high.
Financial transaction matching. An accountant reconciles bank statements against invoicing records. Reference numbers, dates and amounts rarely line up perfectly. The Fuzzy Match function on the normalised payee name and approximate date range catches most matches and a score cutoff flags the remainder for audit.
FAQ
What is entity resolution in Google Sheets?
Entity resolution in Google Sheets is the process of identifying and linking records that refer to the same real-world entity across one or more datasets. It uses techniques like normalisation, fuzzy matching and blocking to detect duplicates and merge related rows even when names, addresses or identifiers differ slightly.
How is entity resolution different from simple deduplication?
Simple deduplication removes exact duplicate rows. Entity resolution goes further by finding records that refer to the same entity despite variations in spelling, formatting, missing values or data entry errors. It handles fuzzy matches, partial matches and cross-dataset linking rather than just exact deduplication.
What functions does Flookup offer for entity resolution?
Flookup Data Wrangler for Google Sheets provides several functions for entity resolution: the Text Standardisation function for standardising text values, the Similarity Scoring function for computing similarity scores between strings, the Deduplication function for removing near-duplicate rows and the Fuzzy Match function for fuzzy lookup and cross-dataset matching.
Can entity resolution handle large datasets in Google Sheets?
Yes, with proper blocking strategies and Flookup's optimised functions, entity resolution can scale to tens of thousands of rows in Google Sheets. Techniques like blocking on key fields reduce the number of pairwise comparisons, making the process efficient even for large datasets.