Master customer list template
By the Analistable team · Updated · 3 min read
A free workbook that turns several customer exports into one deduplicated master list. Paste every source into one sheet; helper formulas clean each email, count copies and number the first occurrence, and the Master list sheet pulls one row per unique customer with INDEX/MATCH. It works in Excel 2016+ and Google Sheets without UNIQUE or macros.
Part of our guide: How to compare two spreadsheets for differences and matches
What the template does
Customer records end up in a CRM, a web shop and a newsletter tool, each with its own copy of the same people. This template merges them on a cleaned email address and keeps the first record it sees for each person, so if you paste your most trusted source first, that is the version you keep. The reasoning behind using email as the key, and its traps, is covered in joining on email addresses safely.
Sheet-by-sheet guide
| Sheet | Columns | You fill in? |
|---|---|---|
| Instructions | Step, What to do | No |
| Source lists | Source, Name, Email, Phone, Company, Clean email, Occurrence, Copies, Master # | First five columns |
| Master list | #, Email, Name, Company, Phone, First source, Copies | No — calculated |
| Summary | Metric, Value | No |
How to fill it in
- Paste your most reliable source (usually the CRM) first, then the others underneath. Fill in the Source column on every row.
- Copy the four helper formulas (F to I) down to the last row of data.
- Make sure Master list has at least as many numbered rows as unique customers — extra rows stay blank.
- Copy the Master list and paste as values when you want a static file to import elsewhere.
How the formulas work
Clean the key so case and stray spaces don't create false duplicates:
=LOWER(TRIM(C2))Number only first occurrences, using a running COUNTIFS that grows as you copy it down:
=IF(COUNTIFS($F$2:F2,F2)=1,COUNTIFS($G$2:G2,1),"")The Master list then fetches row n by looking up n in the Master # column:
=IFERROR(INDEX('Source lists'!$F$2:$F$300,MATCH($A2,'Source lists'!$I$2:$I$300,0)),"")In the workbook the first formula is split into an Occurrence column (G) and the Master # column (I) to keep each step visible.
Worked example
Twelve rows from three sources reduce to eight customers:
| # | Name | First source | Copies | |
|---|---|---|---|---|
| 1 | anna.price@acme.co.uk | Anna Price | CRM | 2 |
| 2 | ben@birchco.com | Ben Ortiz | CRM | 2 |
| 3 | chloe.ng@coastal.io | Chloe Ng | CRM | 2 |
| 4 | dan.reid@delta.com | Dan Reid | CRM | 1 |
| 5 | eve@elmstudio.com | Eve Shah | Webshop | 2 |
| 6 | finn.ward@fernltd.com | Finn Ward | Webshop | 1 |
| 7 | gina@grove.org | Gina Lowe | Newsletter | 1 |
| 8 | hari.patel@harbour.co | Hari Patel | Newsletter | 1 |
'Anna.Price@acme.co.uk ' with a capital and a trailing space and 'BEN@BIRCHCO.COM' are recognised as duplicates after cleaning. The Summary sheet reports 12 source rows, 8 unique customers and 4 duplicates removed.
Limits and when to move on
Email-only matching misses people who use two addresses, and it keeps the first record rather than merging the best fields from each. For those cases, see merging contact lists without duplicates and the comparing spreadsheets guide. With tens of thousands of contacts, formulas like these slow down; Analistable can load the exports in your browser and answer 'customers in the web shop but not the CRM' directly.
Customer lists are personal data. Keep the workbook somewhere access-controlled and delete old copies.
Frequently asked questions
- How do I keep the newest record instead of the first?
- Sort Source lists by a Last updated column, newest first, before the formulas run. The first occurrence is then the latest.
- Can I dedupe on phone number?
- Yes, but clean it first: remove spaces and convert to one format (for example +44 to 0) in the Clean column instead of LOWER(TRIM()).
- Why not use UNIQUE or Remove Duplicates?
- UNIQUE isn't in Excel 2016 or 2019, and Remove Duplicates deletes rows without telling you how many copies each customer had. The formula approach keeps an audit trail.