Excel files are widely used to store listing data. To manage lists of customers or companies, or to clean products catalogs, Microsoft Excel is an easy-to-use spreadsheet tool to manage data. But like any spreadsheet tool, it's not possible to add data constraints to forbid duplicate information. When several people edit the same Excel file or when data get added frequently, the likelihood of finding duplicate values increases.
Hopefully, it's possible to process the data afterward to remove or merge duplicate values.
Removing duplicates is easy using Microsoft Excel (Menu "Data" -> "Remove Duplicates") but merging duplicates without losing information gets tricky. When merging duplicate rows, you want to remove similar rows and merge complementary rows when possible. Finally, conflicting values should be flagged to be processed manually.
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.
Important
- Cells with formulas will be computed during import.
- Your data must be in the file first worksheet
If your data is scattered across different worksheets or files, please load them all in one Datablist collection and map them with the same properties.
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!
To export the collection to be used by another application, click "Export" in the collection header and select CSV or Microsoft Excel.
