Marketing Automation systems perform their best when the data fed in to them is clean. As a support team, we are often asked how customers can get the best data possible in to their LoopFuse account. I’ve been able to find some Excel tricks and formulas to help you clean up those lists before importing them in to your account.
Removing Duplicates
Before importihttps://www.loopfuse.com/files/blog/2012/02/01/ng a list, it’s usually best to remove duplicate entries. sure, we’ll de-duplicate based on email address as we pull in the list, but cleaner data is better data. in this example we’ve highlighted all the rows we want to clean and then used the “remove duplicates” function in excel. once we hit ok, the second “[email protected]” entry in this screen shot is removed because we’ve selected only the Email. If we had left all three boxes checked there would be no removal because “David” is unique from “Dave” and is therefore considered a separate entry.
Comparing Values
This is one request we get quite often when customers are looking to clean up old lists or remove dead leads from their database. I have the customer export the list they want to clean from LoopFuse. (This can be done by following this help article.) Next they can strip out all the extra columns except the Email address. That is the unique value we use when importing a list so it will simply be merged back in to the existing record. Now we need to get the values that are being scrubbed against in to the same sheet. I usually make sure I name the columns in a way that helps me keep things organized.
Now it’s time to compare the two columns. The first formula will list out the common values found between the two columns.
=IF(COUNTIF($A$2:$A$14,B2)>0,B2,””)
$A$2 is the top end of the column and $A$14 is the bottom row. If you had 5,000 records, for example, it would be $A$5001. You can adjust as needed. Copying that formula all the way down in Column C will list out those who appear in both columns.
The formula for finding only the values in Column A is this:
=IF(B2=””,””,IF(COUNTIF($B$2:$B$14,A2)=0,A2,””))
As seen in this screen shot, you can have different sized columns and still do a comparison. There is a catch with this formula, however. You need to have something in those “blank” fields in Column B to compare against. I just typed in a space and copied it to the blank cells.
The formula for finding only the values in Column B is this:
=IF(A2=””,””,IF(COUNTIF($A$2:$A$14,B2)=0,B2,””))
Just as in the “Only Column A” example, you’ll need some holder value in the blank spaces in the shorter list.
Once you have scrubbed your list you need to save it out properly. What I do is save it as a CSV list, close the file, and then re-open it in Excel. This will remove the formulas and leave you with just the values in the fields. You can then delete the unwanted columns using the “Remove Duplicates” trick to get rid of the blank rows and save it once again as a CSV file. Your file should now be ready for import back in to LoopFuse.
Resources
Most of this article is based on the information I found at this page: http://www.officekb.com/Uwe/Forum.aspx/ms-excel/89947/comparing-two-columns-of-data-to-find-common-values
I’ve also used this method in the past to get familiar with the concept of just highlighting differences in Excel. It led to other finds on data management. http://chandoo.org/wp/2010/06/17/compare-2-lists-in-excel/
If you have a better or easier way to do this kind of thing, feel free to share your tips here or over at our “Share Your Stuff” forum on the Customer Portal.
Richard Murdock (@shinyranger) is the Senior Manager of Customer Support at LoopFuse. Our goal is to make sure you have the best experience possible with our products.
We would love you to try out LoopFuse here.
To find out exactly what LoopFuse does, click here.
To add LoopFuse to Salesforce.com, click here.
You can follow LoopFuse on Twitter here or join us on our Facebook fan page here.
Tags: data cleanup, data scrub, Excel, import lists
