If you’ve purchased multiple lists from data vendors over the years, you probably are not aware of all the duplicate contacts that you’ve paid for. There are two very common scenarios that you’ll want to be aware of:
Scenario 1. Let’s use the example of an initial purchase of a list of contacts at software companies, and a subsequent purchase of IT Services companies. You order the initial list of software companies and a few months later order a list of IT Services companies. The problem is that there is bound to be a great deal of overlap between the lists. Since most data vendors charge per contact, this could result in a pretty large waste of money.
Scenario 2: You already have a good deal of these names in your database, so why should you pay for contacts that you already have?
Loading up a new list chock full of duplicate leads or existing Contacts/Accounts can create unnecessary stress and agravation for your sales team, so dealing with these lists efficiently before importing them into CRM can be the difference between being a Salesforce admin that is loved or hated.
Not to mention that these simple data quality routines can cut the cost of your purchased lists to a fraction in some cases.
First you’ll need to combine all of the lists that you’ve purchased from this or any other data vendors that you’ve worked with.
Now, using the Conditional Formatting feature of Excel, you will be able to highlight all of the duplicates in a given area, making it easier to identify the duplicates in your file. This will save you the trouble of going through each and every record one by one, trying to find duplicates. Instead, you can combine the two lists, use the conditional formatting feature of Excel to identify the duplicates, and notify the vendor that you would like to receive a credit for the second list that you’ve purchased.
Highlight Duplicate Records using Conditional Formatting
First, let’s look at a sample list (this would be the combination of your initially purchased list with your newest list.)
Step 1: Highlight the column that you’ll be using to idenitfy duplicate records. In this example, we’ve used the Full Name column, but you will probably want to run this same routine on the Company, Email and even Phone Number columns as well.
Step 2: Once you have the area highlighted, go to Home > Conditional Formatting > Duplicate Values
Step 3: A window should appear once you click “Duplicate Values.” It will also show you a preview of what the formatting will do to the highlighted area.
Step 4: Once you click “OK”, the sheet should look something like this:
Now that all of the duplicates within the desired area has been highlighted, the next step would be to go through all of the duplicate records. Whether you would like to get rid of them, or simply to see how many duplicates exist within your combined lists, it is much easier on the eyes to have them grouped together. You can do this using the “Sort” feature.
Sort the Duplicate Records in Excel
Step 1: Highlight the entire sheet (by pressing the top left corner between row 1 and column A)
Step 2: Go to Data > Sort
Step 3: Once you click Sort, a pop up should appear.
Step 4: If you have labels or headers, make sure to check off “My data has headers.” For this example, we wanted to find the duplicates in the “Full Name” Column, so I will sort using “Full Name.” Since we are trying to sort the duplicates (which are highlighted), I will change the Sort On from “Values” to “Cell Color”, then choose the appropriate color.
Once you click “OK”, this is what you should get:
This method is best used searching for duplicates within a column. If you would like, you can delete the duplicates after taking these steps, but you would have to leave one of the duplicates so everything is not deleted. There is a faster, easier method of getting rid of the duplicates that we’ll cover in another blog post.
After you eliminate the duplicate records in this Excel sheet, you should be left with only unique contacts that you purchased from the data vendor.
Check what is already in your CRM
Now you’ll want to cross reference these contacts with existing Leads, Contacts & Accounts in Salesforce. To do this, you could:
1. Do a manual search for each of the names/emails on your list and then a search for each of the email addresses included in the list.
2. Use RingLead’s Unique Upload application, which allows you to quickly import a list – allowing new Leads to be created when appropriate but updating existing Contacts and Accounts per your Unique Upload settings. Also important to note is Unique Upload’s ability to create a new Contact for a matching Account (which can be disabled if your company process calls for these to be created as new Leads)