How to Merge Duplicate Records in Excel (Golden Record)
The same customer appears three times. One row has the full legal name but no email; the newest has the current email and a blank name. Removing duplicates keeps one row and throws away the rest — including the only copy of a field you needed.
Last updated: October 2026
| Three partial rows | One golden record | |
|---|---|---|
| Al Qalada · (no email) | → | Al Qalada Trading WLL |
| Al Qalada Trading WLL · old@… | → | [email protected] |
| (no name) · current@… | → | Merged from: 3 |
Method 1 — sort, then keep the first row
- Sort by customer, then by date descending.
- Keep the first row of each group with
Remove Duplicates. - It picks one whole row — so every field that was blank on that row is now blank in your master data.
Method 2 — a formula per field
You can rebuild each field with MAXIFS on the date plus XLOOKUP, or an array that picks the longest value. It works, and it is one formula per column that has to be rewritten whenever the layout moves.
The 1-click way — Tellsheet
Build Golden Record applies a survivorship rule column by column: first filled, longest (most complete), most common across the duplicates, or from the most recent row. The surviving name and the surviving email can therefore come from different source rows — which is the entire point, and what "keep the best row" cannot do. A blank never wins, so a newer but empty field cannot erase a value you already hold. Each output row carries a Merged from count, and the originals stay put so every decision is auditable.
Frequently asked questions
What is a golden record?
The single, most trustworthy version of an entity, assembled from several partial sources. The rules that decide which value wins are called survivorship rules.
How is this different from Remove Duplicates?
Remove Duplicates keeps one whole row and discards the others. Survivorship picks the winning value for each column independently, so a complete record can be assembled from several incomplete ones.
Which rule should I use?
Longest for names and addresses, most recent for contact details if you have a reliable date column, and most common when several systems should agree.
Can a blank overwrite a real value?
No. Blanks never win under any rule — that single decision is the difference between a merge that enriches and one that destroys.
Related Excel guides
Build one complete record from many partial ones
Field-by-field survivorship rules — the master-data feature, inside Excel.
Get Tellsheet free See pricing