To merge duplicate rows in an Excel file without losing useful values, match rows on a key such as Email, combine complementary fields, and review conflicting values before deleting any row. Datablist lets you import the XLSX file, preview these groups, and export the merged result.
Excel's Remove Duplicates command keeps the first matching row and deletes the others. It does not copy a phone number or note from a deleted row into the record kept. I would use a merge workflow when those other rows contain information you still need.
Datablist's Duplicates Finder shows the matching rows before changing the file. You can merge complementary values, configure rules for conflicts, and leave uncertain groups untouched. Anonymous visitors can run an Exact check. Smart matching requires a free account.
Why not use Excel to merge duplicates?
Microsoft Excel is a very powerful application and has a "Remove Duplicates" feature. However, "Remove Duplicates" keeps the first "matching" row and deletes any other duplicate rows. If two rows are complementary, it leads to a loss of data. Merging duplicates should not be complicated. Spreadsheet tools are great but they are generic and they don't focus on data manipulation. Datablist targets structured data listing and provides easy-to-use data manipulation features.
In the following guide, you will learn to find and merge duplicates from an Excel file without losing information.
To illustrate this guide, we'll use an Excel file containing Contacts data with 4 fields: First Name, Last Name, Email, Job Title and we will find and merge all contacts based on their email address.
The process can be summed up with the following steps:
- Load your Excel file
- How to Find Duplicates in your Excel File
- How to automatically merge duplicates with no conflict
- How to manually merge remaining duplicates
Step 1: Load Excel file
The first step is to import your Excel file into Datablist. Create a collection and use the Import CSV/Excel assistant to load the file.
Create properties and map with Excel columns
A Datablist collection is like a spreadsheet but with a data structure. And spreadsheet columns are called "Properties" in Datablist. If a property is defined as Email, Datablist will ensure only valid email addresses are filled out. Same with dates, checkboxes, etc.
Once your Excel file is loaded, you need to select which columns will be imported and map them with an existing collection property or create new properties.
Datablist auto detects common data types by analyzing the first rows to find patterns.
📌 Datablist imports the first worksheet of an Excel workbook. Check the imported values in formula columns before continuing; for a values-only workflow, save a copy with formulas replaced by their results in Excel.
Merge two Excel files or sheets without duplicates
Import the first file into a collection, then import the second into the same collection. Map equivalent columns to the same properties: Email Address in one file and Email in the other should feed one Email property. For several sheets, save each required sheet as its own file with that sheet first.
Then run the duplicate check across the combined collection. For contacts, I start with a non-empty email or a stable contact ID. A full name alone can group different people together. Inspect rows with missing keys separately, and keep a Source field if you need to know which file supplied each record.
For example, one file might have a contact's name and email while the other has the same email and a phone number. A merge can keep all three fields. If both files have different phone numbers, choose a conflict rule or leave the group for manual review. Simply appending the files or removing repeated rows does not resolve that decision.
Step 2: Find duplicates in the Excel file
With your Excel file data now imported, it's time to use Datablist Duplicates Finder to find and merge duplicates.
Open Clean > Merge duplicates.
Two modes are available:
- All Properties compares the complete rows.
- Selected Properties compares only the fields that identify one record.
In this example, the email property is enough to identify a contact, so you can select the Selected Properties mode with the email property.
The duplicate analysis is a non-destructive action. Until you decide to merge automatically or manually your items, items are not impacted.
Step 3: Auto Merge duplicate rows
Select Merge and preserve data. Datablist fills empty properties from complementary rows, then asks for rules where two non-empty values conflict.
Records with similar or complementary values are merged into a single item. For example:
email | First Name | Last Name
james@gmail.com | James
james@gmail.com | | Bond
Will be merged into:
email | First Name | Last Name
james@gmail.com | James | Bond
A second merging option lets you consolidate duplicate rows by combining conflicting values into a single item. The different values are merged using a delimiter.
For example:
email | Notes | Phone
james@gmail.com | Met during an event | 0 493 492 334
james@gmail.com | To be contacted | 06 67 23 42 23
With combining on Notes (with semi-colon) and Phone (with Comma) will be merged into:
email | Notes | Phone
james@gmail.com | Met during an event;To be contacted | 0 493 492 334,06 67 23 42 23
With this combining option, consolidate a list of thousands of rows with duplicates without losing information. Use this powerful feature to clean your contacts, leads, and companies lists automatically!
Step 4: Manually merge conflicting items
Groups remain in Needs review when a match has low confidence, a conflict has no rule, or the Record to keep rule cannot select a row.
Open Review group to edit a row, exclude it from the group, skip the group, or launch the Merging Assistant. Select the destination record and the value to keep for every property before confirming the merge.
Export results in CSV or Excel format
Well done! Your data is deduped!
Before exporting, check one complementary group, one group with conflicting values, and any groups left for review. Confirm that the number of removed rows matches the accepted merges and that the chosen contact IDs and useful fields remain.
If numeric values represent distinct contributions, use the guide to sum values while merging duplicates. Repeated snapshots of the same total should not be added together.
Click Export in the collection header and select CSV or Microsoft Excel. Open the exported file and check the headers, IDs, and a sample of merged rows before importing it into another application.
