Whether there's summer knocking on our doors or winter invading Westeros, we still work in Google Sheets and have to compare different pieces of tables with one another. In this article, I'm sharing ways of matching your data and giving away tips on doing that swiftly.
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.
I'll start by comparing two cells in Google Sheets. This way lets you scan entire columns row by row.
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:
Tip. To compare columns from different files, you need to use the IMPORTRANGE function:
=A2=IMPORTRANGE("spreadsheet_url","Sheet1!A2")
=IF(A2=B2,"Match","Differ")
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.
=IF(A2=B2,"Match","")
=IF(A2=B2,"","Differ")
=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:
=ArrayFormula(IF(A2:A=C2:C,"Match",""))
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:
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 into the formula:
Tip. If the sheets to compare are in different files, again, just incorporate the IMPORTRANGE function:
=IF(Sheet1!A1<>IMPORTRANGE("2nd_spreadsheet_url","Sheet1!A1"),Sheet1!A1&" | "&IMPORTRANGE("2nd_spreadsheet_url","Sheet1!A1"),"")
Of course, each of the above examples can be used to compare two columns from one or two tables or even match sheets. 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 color, 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:
Then I saved my settings into one scenario. Now I can quickly run them without going through all steps again whenever records in my tables change. I just need to start that scenario from the Google Sheets menu:
For your better convenience, we've described all the tool's options on its help page and in this video:
Feel free to try it for yourself and notice how much time it saves you. :)
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.
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:
Thus, cells with FALSE is what you're looking for. Copy the formula to other cells to check each product from the first list:
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)))
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:
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","")
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.
As you may have guessed, another example would use the Google Sheets VLOOKUP function that we described some time ago.
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.
Tip. Also, make sure to check out this video about the Merge Sheets add-on:
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 the correct data range.
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:
=A2=C2
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 specify the range to apply the rule to – A2:A10.
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
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.
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.
To highlight unique values in each list, you must 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:
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
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 the 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 highlighted duplicates between two tables based on Fruit and MSRP columns using the tool:
I can also save these settings into a reusable scenario. If the records update, I will call for this scenario in just a click and the add-on will immediately start processing all the data. Thus, I avoid tweaking all those settings over the add-on steps repeatedly. You will see how scenarios work in the example above and in this tutorial.
Tip. Have you seen the demo video for the Compare columns or sheets add-on? Check it out.
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.
Table of contents