Merging an HR export with a CRM or RevOps list always produces the same headache: the same people appear under slightly different names. This guide walks through consolidating two user lists in Google Sheets into one clean, unique listand how the User List Consolidation template automates it.
Why User Lists Are Messy
People enter names as they were told, not as the next system expects. There are nicknames, middle initials, suffixes, diacritics and typos.
Béla Kovács
from one export and
Bela Kovacs
from another are the same person, but no exact-match deduplication will ever see it.
When you consolidate two systems for a migration, an onboarding sync or a BI project, you need three answers: who is new, who already existsand which records are genuinely duplicated inside each list.
The Consolidation Outcome You Want
Clean consolidation produces two artifacts:
- A review view that shows every user from List A, whether they appear in List Band with what confidence.
- A consolidated unique list of distinct users, with each person represented exactly once.
Both must survive as data, not as decisions trapped in someone's memory.
Step 1: Standardise Both Lists
Apply the same cleaning rules to the name column in both lists: lowercase, collapse whitespace, strip punctuation and normalise diacritics. The Standardize Data function in Flookup Data Wrangler does this in one pass per list, removing the variations that break matching later.
Step 2: Remove Internal Duplicates
A single list often contains its own duplicates from synced contacts or re-imported rows. Run Smart Deduplicate on each standardised name column and keep one representative per fuzzy group so the same user is not counted twice during consolidation.
Step 3: Find People in Both Lists
Now
Fuzzy Match
the standardised List A names against the standardised List B names with a threshold around
0.85
. Return the best match and its score so every "appears in both systems" flag is backed by a number you can audit.
Scores above the threshold mean the person exists in both systems. Unmatched rows are List A only, which for a migration usually means "needs creating in the target system" or "already handled in List B under a completely different identity."
Step 4: Build the Consolidated Unique List
Combine List A and List B, drop the rows that matched in step 3and keep the highest-quality version of each person. The result is the consolidated list of unique users. Store it as its own sheet and keep the review view alongside so you can defend every inclusion or deletion later.
Run It as a One-Click Template
The User List Consolidation workflow template in Flookup Data Wrangler automates all four steps. It standardises both lists, flags internal duplicates, fuzzy-matches List A against List B and produces a review sheet with each List A name, its standardized value, the closest List B match, match score, status and confidence, plus a Consolidated Unique Users sheet.
It is part of the Data Nova plan and never modifies your original lists.
Frequently Asked Questions
How do I merge two user lists in Google Sheets?
Standardise the name column in both lists first so the same person is spelled identically, then fuzzy-match List A against List B to find people who appear in both. Remove internal duplicates, combine the two lists and drop rows that already matched to produce one consolidated list of unique users.
Why do duplicate user lists still show duplicates after removing exact matches?
Exact matching misses name variants such as 'Bob Williams' versus 'Robert Williams', middle initials, nicknames and characters with diacritics. Standardisation plus fuzzy matching compares similarity rather than equality, which catches those variants and lets you decide which names are really the same person.
How do I flag people who appear in both HR and CRM systems?
Use the User List Consolidation workflow template in Flookup Data Wrangler. It standardises both lists, flags internal duplicates, fuzzy-matches List A to List B and writes a review sheet showing which users appear in both systems and who is in List A only, plus a consolidated list of unique users.
What is a good threshold for matching user names?
A threshold around 0.85 works well for personal names in most cases. Raise it to avoid false positives in small lists, lower it when names are entered with heavy shorthand. Review borderline matches by hand, then lock your rules so the next migration runs faster.