If you've started using Google Spreadsheets as an alternative to the Non-Free Microsoft Excel, congratulations. The real benefit of cloud-based computing is that you never again have to ask where's the latest version of that spreadsheet! The bummer can be those nice little enterprise level tools like "pivot tables", "conditional formatting", "eliminate duplicates", etc. For today, I thought I'd help you learn how to remove duplicates from a Google spreadsheet.

What you must do first is determine the best column or columns for determining a duplicate record (this assumes you have your records in rows, with column headers like "First Name", "Last Name", "Email Address", "Phone", etc.). Using the example in parens, the best column is probably "Email Address". So, here's what you do:

1. sort by the column containing email addresses 
2. find the first empty column field to the right (see Note 1 below first)
   
3. enter the following formula: =IF(D2=D3,1,0) 
4. Now copy the formula down the column
5. Those rows with a "1" are duplicates and can be deleted (see Note 2 below)
   

If you need to combine fields to determine a duplicate (i.e. if you only had First Name, Last Name, and Company Name - no email), simply follow steps 2 and 3 so you have a column for each required field. Now you can create another column to total the result of the other three. So, if First Name = 1, Last Name = 1, and Company Name = 1, the total would be 3. A total of 3 would be indicative of a duplicate.

NOTE 1: if your columns have dirty data (All Caps, lower case, and mixed), you'll want to perform the following operation before determining duplicates.

1. copy the column with dirty data (just right click and hold until the entire column is highlighted)
2. Ctrl-C or Apple-C to copy
3. Ctrl-V or Apple-V to paste into the dialog box on the Case Converter website 
4. Select the appropriate text treatment (Upper Case, Lower Case, Proper Case, or Sentence Case)
5. Highlight all items in dialog box on Case Converter
6. Ctrl-C or Apple-C to copy
7. Ctrl-V or Apple-V to paste into the appropriate column in your Google Spreadsheet (make sure you select the right field to begin the paste operation - you want everything to match, we are just trying to make the text treatment consistent)

NOTE 2: Advanced deletion requires you to convert the formulaic column containing information about duplicates to a non-formulaic column. So, copy the column using Ctrl-C or Apple-C to copy. Then, paste (using Ctrl-V or Apple-V) into a text editor. Once pasted, change the mode to plain text. Now cut and paste back into your spreadsheet. This is important because you will want to sort the data by the duplicates column (if formulaic, the values will change when you sort). Now that you have non-formulaic information about duplicates, sort and delete the rows with a "1".

That's how you do it!

This week, I'm taking a break from the Marketing on the Run: PPC Advertising series. I share two Firefox Add-ons that can improve your life! One is Xmarks, the other is Blank Canvas Gmail Signatures. Enjoy! Here's the video:

This week I took a break from driving while talking marketing - please forgive! Anyway the topic is understanding how you can have an impact in each of the three components of PPC advertising: Impressions, Clicks and Conversions. The "Gift" strategy is outlined in the video. There are lots of variables, but I try to break it down to the really big, simple things. Remember, we're building toward more complex strategies. This is another foundational episode. To understand the variables at play, I've provided definitions for Google's Quality Score and Yahoo's Quality Index:

Quality Score is a Google measure of historical click-through rate (CTR) of the keyword and matched Ad, account history (CTR of all Ads and Keywords in your account), historical CTR of the display URLs in the Ad Group, Quality of Destination Page, relevance of keywords to Ads within Ad Group, relevance of keyword and matched Ad to search query, and account performance in geographical region.

Quality Index is a Yahoo! measure of Bid or Click Price, Historical click-through data, Conversion data, Organic Rankings of Advertiser URL, keyword appears in Title, keyword in URL, Keywords on Destination Page, Other keywords at the Campaign level and a comparison between other sites competing on the same keywords.

You may have to go full screen to read the diagram.