• Request For A Quote
  • Tools Login
    • Tools Dashboard
    • Restore Password
  • Login
  • Register
Top Web Development Tips, Link Building Hacks, Affordable web development
  • Our Services
    • Affordable to Advanced SEO Packages
    • Small Business Website Package
    • Convert Any Facebook Page to A fully Responsive ecommerce Site
    • Browse our Services
    • Download Our Free Business Templates and Free Printables
  • Blog
    • Build Traffic For Free
    • Business Advice
    • Finding your Niche Tips
    • Infrastructure
    • Monetization Tools and Programs
    • Web Development
    • Working from Home Jobs and Tips
  • Favorite Tools
    • Free Startup Tools Directory
      • Free Classifieds
    • Download Ebooks and Themes
    • The Ultimate Cheat Sheet for Creating Awesome Landing Pages That Converts Instantly – Free Landing Page Example Inside
    • Free Online Research Tools
    • Top Online Design Tools
    • Contribution Platform
    • Referral Platform
    • Affordable Hosting
  • Contact
    • Team Awesome – Zipsite Web Development Team
  • Submit Guest Blog
    • Write for us – Guest Blog
    • Register & Add Your Site to Our Directory
No Result
View All Result
ZIPSITE - Web Development For Startups Since 2003
  • Our Services
    • Affordable to Advanced SEO Packages
    • Small Business Website Package
    • Convert Any Facebook Page to A fully Responsive ecommerce Site
    • Browse our Services
    • Download Our Free Business Templates and Free Printables
  • Blog
    • Build Traffic For Free
    • Business Advice
    • Finding your Niche Tips
    • Infrastructure
    • Monetization Tools and Programs
    • Web Development
    • Working from Home Jobs and Tips
  • Favorite Tools
    • Free Startup Tools Directory
      • Free Classifieds
    • Download Ebooks and Themes
    • The Ultimate Cheat Sheet for Creating Awesome Landing Pages That Converts Instantly – Free Landing Page Example Inside
    • Free Online Research Tools
    • Top Online Design Tools
    • Contribution Platform
    • Referral Platform
    • Affordable Hosting
  • Contact
    • Team Awesome – Zipsite Web Development Team
  • Submit Guest Blog
    • Write for us – Guest Blog
    • Register & Add Your Site to Our Directory
No Result
View All Result
ZIPSITE - Web Development For Startups Since 2003
No Result
View All Result
Home Small Business Tips

Google Sheet Power Tip : Compare data in two Google sheets or columns for matches and differences

Maida Barrientos by Maida Barrientos
10 months ago
in Small Business Tips
Reading Time: 9min read
0 0
2
Share on FacebookShare on Twitter

Compare two columns or sheets

One of the tasks you may have is to scan two columns or sheets for matches or differences and identify them somewhere outside the tables.

Compare two columns in Google Sheets for matches and differences

Page Contents

    • Compare two columns in Google Sheets for matches and differences
      • Example 1. Google Sheets – compare two cells
      • Example 2. Google Sheets – compare two lists for matches and differences
      • Example 3. Compare two columns in Google Sheets
      • Example 4. Compare two Google Sheets for differences
    • Tool for Google Sheets to compare two columns and sheets
  • Compare data in two Google Sheets and fetch missing records
    • Find missing data
      • Example 1
      • Example 2
      • Example 3
    • Pull matching data
      • Example 1
      • Example 2
      • Merge sheets using the add-on
  • Conditional formatting to compare data in two Google Sheets
    • Highlight duplicates in two sheets or columns
    • Compare two Google sheets and columns for differences
    • Compare two lists and highlight records in both of them
      • Example 1. Compare two columns in Google Sheets and highlight differences (uniques)
      • Example 2. Find and highlight duplicates in two columns in Google Sheets
    • Quick way to match columns and highlight records

I’ll start with comparing two cells in Google Sheets. This way lets you scan entire columns row by row.

Example 1. Google Sheets – compare two cells

For this first example, you will need a helper column in order to enter the formula into the first row of the data to compare:

=A2=B2

If cells match, you’ll see TRUE, otherwise FALSE. To check all cells in a column, copy the formula down to other rows:
The simplest formula in Google Sheets to compare two cells.

Example 2. Google Sheets – compare two lists for matches and differences

  • A neater solution would be to use the IF function. You’ll be able to set the exact status for identical and different cells:=IF(A2=B2,"Match","Differ")
    Identify pairs of cells with the IF function.

    Tip. If your data is written in different cases and you’d like to consider such words as different, here’s the formula for you:=IF(EXACT(A2,B2),"Match","Differ")

    Where EXACT considers the case and looks for the complete identicals.

  • To identify only rows with duplicate cells, use this formula:=IF(A2=B2,"Match","")
  • To mark only rows with unique records between cells in two columns, take this one:=IF(A2=B2,"","Differ")

Example 3. Compare two columns in Google Sheets

  • There’s a way to avoid copying the formula over each row. You can forge an array IF formula in the first cell of your helper column:=ArrayFormula(IF(A2:A=C2:C,"","Differ"))This IF pairs each cell of column A with the same row in column C. If records are different, the row will be identified accordingly. What is nice about this array formula is that it automatically marks each and every row at once:
    Find differences between two columns with the array function.
  • In case you’d rather name the rows with identical cells, fill the second argument of the formula instead of the third one:=ArrayFormula(IF(A2:A=C2:C,"Match",""))

Example 4. Compare two Google Sheets for differences

Oftentimes you need to compare two columns in Google Sheets that belong inside a huge table. Or they can be entirely different sheets like reports, price lists, working shifts per month, etc. Then, I believe, you can’t afford to create a helper column or it can be quite difficult to manage.

If this sounds familiar, don’t worry, you can still mark the differences on another sheet.

Here are two tables with products and their prices. I want to locate all cells with different contents between these tables:
Short price lists to compare.
Start with creating a new sheet and enter the next formula into A1:

=IF(Sheet1!A1<>Sheet2!A1,Sheet1!A1&" | "&Sheet2!A1,"")

Note. You must copy the formula over the range equal to the size of the biggest table.

As a result, you will see only those cells that differ in contents. The formula will also pull records from both tables and separate them with a character you enter to the formula:
Different cells between the first two sheets have been identified.

Tool for Google Sheets to compare two columns and sheets

Of course, each of the examples above can be used to compare two columns from one or two tables or even match sheets themselves. However, there is a tool we created for this task that will benefit you a lot.

It will compare two Google sheets and columns for duplicates or uniques in 3 steps. Make it mark the found records with a status column (that can be filtered, by the way) or colour, copy or move them to another location, or even clear cells and delete entire rows with dupes whatsoever.

I used the add-on to find the rows from Sheet1 that are absent from Sheet2 based on Fruit and MSRP columns:
Compare columns or sheets add-on.
We’ve described all the tool’s options on its help page for your better convenience. Feel free to try it for yourself and notice how much time it saves you. 🙂

Compare data in two Google Sheets and fetch missing records

Comparing two Google Sheets for differences and repeats is half the work, but what about missing data? There are special functions for this as well, for example, VLOOKUP. Let’s see what you can do.

Find missing data

Example 1

Imagine you have two lists of products (columns A and B in my case, but they can simply be on different sheets). You need to find those presented in the first list but not in the second one. This formula will do the trick:

=ISERROR(VLOOKUP(A2,$B:$B,1,0))

How does the formula work:

  • VLOOKUP searches for the product from A2 in the second list. If it’s there, the function returns the product name. Or else you will get an #N/A error meaning the value wasn’t found in column B.
  • ISERROR checks what VLOOKUP returns and shows you TRUE if it’s the value and FALSE if it’s the error.

Thus, cells with FALSE is what you’re looking for. Copy the formula to other cells to check each product from the first list:
Looking for products that are in column A only.

Note. If your columns are in different sheets, your formula will reference one of them:=ISERROR(VLOOKUP(A2,Sheet2!$B:$B,1,0))

Tip. To get by with a one-cell formula, it should be an array one. Such formula will automatically fill all cells with results:=ArrayFormula(ISERROR(VLOOKUP(A2:A10,$B:$B,1,0)))

Example 2

Another smart way would be to count all appearances of the product from A2 in column B:

=IF(COUNTIF($B:$B, $A2)=0, "Not found", "")

If there’s absolutely nothing to count, the IF function will mark cells with Not found. Other cells will remain empty:
Count values to check if anything is missing.

Example 3

Where there’s VLOOKUP, there’s MATCH. You know that, right? 😉 Here’s the formula to match products rather than count:

=IF(ISERROR(MATCH($A2,$B:$B,0)),"Not found","")

Tip. Feel free to specify the exact range of the second column if it remains the same:=IF(ISERROR(MATCH($A2,$B2:$B28,0)),"Not found","")

Pull matching data

Example 1

Your task may be a bit fancier: you may need to pull all missing information for the records common for both tables, for example, update prices. If so, you’ll need to wrap MATCH in INDEX:

=INDEX($E:$E,MATCH($A2,$D:$D,0))

The formula compares fruits in column A with fruits in column D. For everything found, it pulls the prices from column E to column B.
Pull matching data using formulas in Google Sheets.

Example 2

As you may have guessed, another example would use the Google Sheets VLOOKUP function that we described some time ago.

Merge sheets using the add-on

If you’re tired of formulas, you can use our Merge Sheets add-on to quickly match and merge two Google sheets. Alongside its basic purpose to pull the missing data, it can also update existing values and even add non-matching rows. You can see all changes in colour or in a status column that can be filtered.
Merge Sheets add-on.

Conditional formatting to compare data in two Google Sheets

There’s one more standard way Google offers to compare your data – by colouring matches and/or differences via conditional formatting. This method makes all records you’re looking for stand out instantly. Your job here is to create a rule with a formula and apply it to a correct data range.

Highlight duplicates in two sheets or columns

Let’s compare two columns in Google Sheets for matches and colour only those cells in column A that tally with cells in the same row in column C:

  1. Select the range with records to color (A2:A10 for me).
  2. Go to Format > Conditional formatting in the spreadsheet menu.
  3. Enter a simple formula to the rule:=A2=C2
  4. Pick the color to highlight cells.

Highlight duplicates in two columns in Google Sheets.

Tip. If your columns change in size constantly and you want the rule to consider all new entries, apply it to the entire column (A2:A, assuming the data to compare starts from A2) and modify the formula like this:=AND(A2=C2,ISBLANK(A2)=FALSE)

This will process entire columns and ignore empty cells.

Note. To compare data from two different sheets, you’ll have to make other adjustments to the formula. You see, conditional formatting in Google Sheets doesn’t support cross-sheet references. However, you can access other sheets indirectly:=A2=INDIRECT("Sheet2!C2:C")

In this case, please make sure to specify the range to apply the rule to – A2:A10.

Compare two Google sheets and columns for differences

To highlight records that don’t match cells on the same row in another column, the drill is the same as above. You select the range and create a conditional formatting rule. However, the formula here differs:

=A2<>C2
Google Sheets - compare two lists.

Again, modify the formula to make the rule dynamic (have it consider all newly added values in these columns):

=AND(A2=C2,ISBLANK(A2)=FALSE)

And use the indirect reference to another sheet if the column to compare with is there:

=A2<>INDIRECT("Sheet1!C2:C")

Note. Don’t forget to specify the range to apply the rule to – A2:A10.

Compare two lists and highlight records in both of them

Of course, it’s more likely the same records in your columns will be scattered. The value in A2 in one column will not necessarily be on the second row of another column. In fact, it may appear much later. Clearly, this requires another method of searching for the items.

Example 1. Compare two columns in Google Sheets and highlight differences (uniques)

To highlight unique values in each list, you need to create two conditional formatting rules for each column.

Color column A: =COUNTIF($C$2:$C$9,$A2)=0
Color column C: =COUNTIF($A$2:$A$10,$C2)=0

Here are the uniques I’ve got:
Unique ingredients for each list.

Example 2. Find and highlight duplicates in two columns in Google Sheets

You can colour common values after slight modifications in both formulas from the previous example. Just make the formula count everything greater than zero.

Color dupes between columns in A only: =COUNTIF($C$2:$C$9,$A2)>0
Color dupes between columns in C only: =COUNTIF($A$2:$A$10,$C2)>0
Highlight values that appear in both columns.

Quick way to match columns and highlight records

Conditional formatting can be tricky sometimes: you may accidentally create a few rules over the same range or apply colors manually over cells with rules. Also, you have to keep an eye on all ranges: the ones you highlight via rules and those you use in rules themselves. All of these may confuse you a lot if you’re not prepared and not sure where to look for the problem.

Luckily, our Compare columns or sheets is intuitive enough to help you match two columns within one table, two different tables on one sheet, or even two separate sheets, and highlight those uniques or dupes that may sneak into your data.

Here’s how I colored uniques between two tables based on Fruit and MSRP columns using the tool:
Compare columns or sheets and highlight records.

All these methods are now at your disposal – experiment with them, modify and apply to your data. If none of the suggestions help your particular task, feel free to discuss your case in comments down below.

Source: Compare data in two Google sheets or columns for matches and differences

Allow Zipsite to send you new posts and downloads!

Unsubscribe
Maida Barrientos

Maida Barrientos

About Maida She is Zipsite's all around Zipsiter. Only clocks out when she can barely stand. She sings, bakes, and tries so hard to be fit. But don't judge, she can literally strong arm you no problem. She's currently involved with eCorp, Contrib and VNOC, besides being a Brazilian Jiu Jit Su practitioner.

Related Posts

confused businessman checking time on wristwatch
Small Business Tips

7 Must-Know Time Clock Rules For Hourly Employees

October 18, 2020
1.8k
get MOD PODCAST
Small Business Tips

GET MOD – Money On Demand Podcast

October 9, 2020
1.8k
How to Make a Small Business Website: Step-by-Step Guide for Beginners (2020)
Small Business Tips

How to Make a Small Business Website: Step-by-Step Guide for Beginners (2020)

June 28, 2020
1.8k
business migration
Small Business Tips

Digital Media Skills List You Should be adding today

May 18, 2020
1.8k
Please login to join discussion

We’re dynamic with the times

Started in the early 2000, zipsite.net has actively gain grounds in the cyber web marketing and design industry. Based in Davao City, zipsite.net has been a competitive company through out the world.

Blog Stats

  • 12,004 hits

Latest Reviews

  • Product Dyno
    9.9
  • The Best Online Video Maker for Digital Marketers
    9.9
  • Zipsite.net
    ( 255 )
  • Analytics Tools & Solutions for Your Business - Google Analytics
    ( 150 )
  • Unbounce
    ( 15 )
  • LSIGRAPH
    ( 11 )
  • Google Small Business Tools
    ( 10 )
30 days to a $1000 Epic Launch

Get the 30 Days To An Epic Launch Digital Planner

Get the most comprehensive daily plan for launching your business, starting at the very beginning and get this, yes with a digital planner.
  • Privacy Policy
  • Terms and Conditions

© 2020-2021

No Result
View All Result
  • Our Services
    • Affordable to Advanced SEO Packages
    • Small Business Website Package
    • Convert Any Facebook Page to A fully Responsive ecommerce Site
    • Browse our Services
    • Download Our Free Business Templates and Free Printables
  • Blog
    • Build Traffic For Free
    • Business Advice
    • Finding your Niche Tips
    • Infrastructure
    • Monetization Tools and Programs
    • Web Development
    • Working from Home Jobs and Tips
  • Favorite Tools
    • Free Startup Tools Directory
      • Free Classifieds
    • Download Ebooks and Themes
    • The Ultimate Cheat Sheet for Creating Awesome Landing Pages That Converts Instantly – Free Landing Page Example Inside
    • Free Online Research Tools
    • Top Online Design Tools
    • Contribution Platform
    • Referral Platform
    • Affordable Hosting
  • Contact
    • Team Awesome – Zipsite Web Development Team
  • Submit Guest Blog
    • Write for us – Guest Blog
    • Register & Add Your Site to Our Directory

© 2020-2021

Welcome Back!

Sign In with Facebook
OR

Login to your account below

Forgotten Password? Sign Up

Create New Account!

Sign Up with Facebook
OR

Fill the forms below to register

*By registering into our website, you agree to the Terms & Conditions and Privacy Policy.
All fields are required. Log In

Retrieve your password

Please enter your username or email address to reset your password.

Log In

Add New Playlist

ajax-loader
This website uses cookies. By continuing to use this website you are giving consent to cookies being used. Visit our Privacy and Cookie Policy.

JOIN OUR EXCLUSIVE CLUB!

It’s easy: all we need is your email & your eternal love. But we’ll settle for your email.

In return, you can have access to our digital toolbox for free, weekly digital free coupons on apps and recommended ebooks for free plus allow you to submit posts, links to our directory.

No Thanks!