Excel: Checking for Errors or Duplicates in your Data

A question I am frequently asked is how to quickly identify errors or duplicates in Microsoft Excel data.

I will show you how this can be done very quickly and easily.

First, I assume that you don’t want to simply remove all duplicated data – if you do, you can use the “Remove Duplicates” tool in the “Data Tools” section of the “Data” tab along the Ribbon.

If you want to keep your data, and simply identify where the duplicates are, here are a couple of tricks:

Assume you have the following data:

Excel Example Table

1. Order your data by the column that you wish to identify the duplicates in. (e.g. in the example above, sort by column A “Letter”
2. After you have ordered your data, create a third column with a formula that checks if the cell one line above matches the cell one line below. There are many ways to do this, for example:
3. 1. =A1=A2 – This will return TRUE if the data in cells A1 & A2 match, or it will return FALSE if they don’t
   2. =IF(A1=A2,"Duplicate","Unique") (Same check as above but gives a user friendly message)
   3. Alternatively if duplicated data is an error you could write: =IF(A1=A2,"Error","OK")
   4. =IF(A1=A2,1,0) – This is my preferred method because you can select the entire column and see if the sum of the column is greater than 0. If it is, than you know you have duplicates/errors which you then can quickly sort for and find.

Questions? Other tricks/tips? Not what you were looking for? Let me know below in the comments and I’ll get back to you as soon as I can.

Thanks for reading!

Browser Search Tools

In addition to the search engine tips I posted about yesterday, here are some browser specific tips for speeding up your searches on an individual page:

Finding Words on a Page
Suppose I want to find out if Jabian is in the S&P 100. I navigate to Wikipedia & look up the S&P 100 list of companies. Instead of scanning through the entire list (and possibly missing an entry) I can use the built in search (find) function in my browser. In Firefox & Internet Explorer, press the “Ctrl” button in conjunction with the “F” Button (Ctrl+F).

This will bring up a search box in the browser where you can search (see area highlighted in pink in the picture above). As you can see, Jabian is not (yet) in the S&P 100.

Searching from the Address Bar

Did you know that instead of navigating to Google (or Bing, or whatever your favorite search engine is) – you can type your search criteria directly into the address bar of your browser? Firefox & Chrome default to Google while Internet Explorer defaults to Bing. You can usually change the default search engine in your browser’s options menu.

Type your query into the address bar & hit enter to load.

Your results will be automatically sent & loaded in your default search engine.

Internet Search 101 (4 of 4)

This is the final installment in a series on Internet Search tips.
The first post is available here
The second post is available here
The third post is available here

These posts are based off a “Power Search with Google” course that was offered by Google in 2012. I have taken the highlights & condensed the tips down into a concise guide as well as adding in other tips that I have come across over time. Enjoy!

In this installment we will cover the following Google Search Operators:

1. Search Features, Conversions & Calculator
2. Using Different Media Types

Search Features, Conversions & Calculator

Search Features (aka shortcuts) include things such as:

• Time
• Weather
• Movies
• Capital of…
• Medical terms

For a full list of features: Google Feature List

Examples include:

Calculator & Conversions

Using Different Media Types

Use different Google portals to search for different media types to find alternative search results. Examples include:

Images

Shopping

Movies/Youtube

Scholar

Books

Internet Search 101 (3 of 4)

This is a continuation of a series on Internet Search tips.
The first post is available here
The second post is available here

These posts are based off a “Power Search with Google” course that was offered by Google in 2012. I have taken the highlights & condensed the tips down into a concise guide as well as adding in other tips that I have come across over time. Enjoy!

In this installment we will cover the following Google Search Operators:

1. Define
2. Site
3. Filetype
4. Minus
5. Or
6. Quoted Text
7. Intext
8. Around(n)
9. Allintitle
10. X..X (range operator)
11. Advanced Search Options

Define

Define – returns dictionary definition of a word

• [define:horse]

Site

Site – searches for words only within a specified website

• [site:jabian.com]

Filetype

Filetype – searches only for specified filetype(s)

• filetype:docx

Minus (-)

– (minus) – removes results that include subtracted term

• Jabian -home

Or

OR – returns results for either query term and does not give priority to having both results on the same page

• Jabian OR Accenture

Quoted Text

“quoted text” – returns results that contain exact text – useful for finding original texts.

Intext

Intext – returns results that include specified terms in the actual text of the webpage

• intext:agile

Around

AROUND(n) – returns results where terms are within a certain number of word spaces of each other.

• Jabian AROUND(2) chris

Allintitle

allintitle – returns results where all search terms are in the title of the webpage

X..X (range operator)

X..X – Range Operator – returns results which include any numbers between the given range.

• E.g. 5..10 returns any results that include the number 5, 6, 7, 8, 9, & 10

Note that in the first example, the number 25 is NOT bolded (found) by Google, however in the second result set, it is.

Advanced Search Options

Advanced Search Options

• Contains most of the same functionality that operators provide

For a continuation of these topics, see the next article here

Internet Search 101 (2 of 4)

This is a continuation of the first post in this series available here

These posts are based off a “Power Search with Google” course that was offered by Google in 2012. I have taken the highlights & condensed the tips down into a concise guide as well as adding in other tips that I have come across over time. Enjoy!

In this installment we will cover the following topics:

1. Filtering Image Results
2. Search as You Type
3. Related Searches
4. Google Specific Search Tools

Filtering Image Results

Use the color filter when searching for images to return different types of results. The search for “tesla” below will bring back pictures of a car when filtered for red, pictures of lightening when filtered for purple and pictures of the inventor when black & white is selected.

Search as You Type

Google will prompt you as you type – use suggested searches to help you narrow down what you’re looking for.

Related Searches

Use Google’s list of related searches (usually available at the top or bottom of the page) to help find better search criteria.

Google Specific Search Tools

Google has multiple specialized tools to help you narrow down results – looking for professional documentation? Search for a higher reading level…

For a continuation of these topics, see the next article here

Internet Search 101 (1 of 4)

These posts are based off a “Power Search with Google” course that was offered by Google in 2012. I have taken the highlights & condensed the tips down into a concise guide as well as adding in other tips that I have come across over time. Enjoy!

In this first installment, we will cover the following topics:

1. The Art of Choosing Keywords
2. Word Order Matters
3. Punctuation, Special Characters & Spelling

The Art of Choosing Keywords

• Choose words that you’d like to see in your results
• Drop connecting words (and, of, for, the, by, etc.)

Question: “I heard that Atlanta used to have other names. What was one of it’s previous names?”

Search Query: [original name atlanta ga]

Word Order Matters

Word order can make a difference – try different orders to find different results

Punctuation, Special Characters & Spelling

Punctuation does not (usually) matter
Special characters do not (usually) matter

Exception Examples:

• C++
• C#
• #hashtag
• Google+
• $100

Spelling matters! (But search engines will try to correct/suggest spelling for you…)

For a continuation of these topics, see the next article here