How to Remove Duplicates in Google Sheets Using the UNIQUE Function [VIDEO]

push duplicate data out of your sheets

Google Sheets doesn’t have a Remove Duplicates option, like Excel does. However, you can still use Google Sheets for your duplicate data removal needs! In this video, I’ll show you how to remove duplicate values from a list in Google Sheets using the UNIQUE function. I really wish Excel had this function.

Two other mad-cool Google Sheets functions to check out (that I also wish Excel offered) are the JOIN and SPLIT functions.

Check Out my Marketing Quick Tips YouTube Playlist

You can stay abreast of all of these videos by checking out my Quick Tips playlist or subscribing to my YouTube channel (or click that handy little button in the right sidebar).

You can also check them out here:

Pro Tip: You can cycle through the different videos in the embedded playlist above by clicking the icon in the top-left corner of the playlist and choosing your quick tip video.

YouTube playlist for Annielytics' Tip of the Day

How To Import One Spreadsheet Into Another In Google Drive [VIDEO]

Lady and the Tramp sharing spreadsheets from Google Sheets

At YourTango, I work with an editorial team. And editorial teams love working in Google Docs. I’ve never seen so many in my life.

But managing them can be a bear because sometimes you have to have multiple Google Docs open in different tabs to provide context for the research you’re doing. So I’ve been exposing the team to crazy techniques like dynamically pulling a data set from one spreadsheet in Google Sheets into another using the IMPORTRANGE function in Google Sheets.

Video

Warning: I make data sexy for a company that makes everything sexy. So if you’re easily offended, you may not want to watch this video as there are some adult keywords in the Google Doc I use to demonstrate.

 

Steps

For those who don’t want to watch a video, I will provide a rough sketch of the steps I took below. If they don’t make sense watch the video. (Duh.)

Step 1

Grab the key from the URL of the Google Doc you want to import.

Google Doc key
Click for larger image.

Step 2

Take note of the columns or range you want to import. You can import entire columns by entering something like Tab Title!A:F. Or, for a specific range, something like Tab Title!A6:F100.

Step 3

In the cell you want the data to import into, enter the following formula:

=IMPORTRANGE("1P3DhQtE46xxBTopuklWEoBdr1NzH0efXjWFTATAH1Z1c","CTR!B6:AC100")

Of course, you’ll switch out my key and range for your own. Alternatively, you can drop each of these values into a cell and just reference the cells. If you do that, you won’t need the quotation marks. It would look something like:

=IMPORTRANGE(B1,B2)

Step 4

Format your data. I demonstrate how I formatted mine in the video. (For those of you who know I’m not a big fan of the color pink, those are YourTango’s [current] branded colors. We have an updated look coming though, which I’m all too relieved about!)

Google Sheets' IMPORTRANGE function

 

Email Attachments Up To 10GB Using Google Drive

Gmail's new Drive muscle

I noticed an extra icon in my Gmail and decided to explore it. Turns out, Google has dialed up its Gmail/Drive integration by allowing users to send large files with Drive — up to 10gb.

In an effort to nudge people to use its new compose feature, Google added this feature. But it doesn’t work on old compose. Once you’ve enabled new compose, you can access it by hovering over the attachment icon and clicking the Drive icon.

Google Drive in Gmail

Then just follow the prompts.

You can read more about this feature on the Google site.

Photo by windsord.

How To Use Google Docs’ JOIN And SPLIT Functions

JOIN and SPLIT as demonstrated by Rihanna and Chris
Get it? Rihanna and Chris Brown split up and are together again? #clever

 

Google Docs offers two functions in its spreadsheets that Excel doesn’t: JOIN and SPLIT. They are the finessed alternatives to Excel’s CONCATENATE function and its scorched earth Text to Columns command.

JOIN Function

Let’s say you want to create a regular expression (regex) that joins together (geek speak: concatenates) a bunch of cities separated by pipe characters, which means “or” in regex. Your formula will look like this:

Here’s what it will look like:

JOIN function in Google Docs

The first argument is the delimiter and the second the array, which is just more geek speak for a range of cells. And you can join more than one array. Just separate them with commas.

Don’t ask me why Excel doesn’t offer this because it’s awesome. Before I learned about the JOIN function from this smart ginger, I would paste my list in Word and then do a search for paragraph returns (which you do by searching for “^p”) and replace them with | (which shares a key with  just above the Enter/Return key).

SPLIT Function

The SPLIT function is a formulaic approach to Excel’s Text to Columns hatchet job. The only two arguments are the cell you want to split apart and the delimiter.

So let’s say you have a list of email addresses, and you want to pull out the domains. You would do that with this formula:

SPLIT function in Google Docs

See It Here

Feel free to check out the Google Doc I took the screen captures from, if you want to kick the tires a bit. Nothing too fancy schmancy going on there.

Pro Tip

When using functions in Google Docs you have two options: You can put the value for an argument in its own cell or enter it directly into the formula. If you put it in a cell, you can just reference the cell using its address. If you enter it directly into a formula, you need to surround it with quotation marks.

You can see in both of my examples that I entered the delimiters directly into the formula, so I used quotation marks. But I could have put it into its own cell and then referenced the cell. It wouldn’t be necessary with something as simple as this, but I highly recommend using cell references for something more complicated like web scraping.

Betedubs, if you haven’t used Google Docs for web scraping, you really haven’t lived. I did a presentation at a meetup at SEER Interactive where I break this down in such simple terms, even a marketer can do it. (Oooooo burrnnnn.) No but seriously … You should check it out. I provide you with a video, Slideshare presentation, and sample Google Doc with tons of examples you can see in action. And cookies afterwards.

Photo by Fanpop

How To Scrape The Web Using Google Docs

Scrape web pages with ImportXML, ImportHTML, and ImportFeed in Google Docs
Web scraping is like picking strawberries. (Okay, fine – not really, but they were really cute.)

Ever looked at a delicious list on a website and copied and pasted it, bit by bit, into an Excel or Google Doc? Yeah, those days are over. One thing I love to do is scrape the Web, and Google Docs makes that fairly simple with three commands (in order of complexity):

  • ImportFeed
  • ImportHTML
  • ImportXML

ImportFeed and ImportHTML are easy sneezy, but I couldn’t wrap my non-programmer brain matter around ImportXML because all the blog posts I read on it were written by developers. So I made it my ambition to figure it out and then explain it in simple terms that normal people can understand with analogies to things like the Duggar family and gingers who wear funky shoes.

You can watch the video here:

 

View the presentation here:

And check out the Google Doc I shared that shows tons of examples here:

 

Photo by adwriter