How to Compare Two Sheets in Google Sheets for Mismatch

0
132
Compare Two Sheets in Google Sheets for Mismatch

May be you have sufficient reasons to compare two sheets in Google Sheets for mismatch. Whatever it may be, you can easily do it. In Google Sheets, there is no built-in option for comparing two similar sheets for mismatch. No matter how many columns are there in the sheets to find differences, you can do it hassle free with a formula.

In Google Sheets, it’s pretty easy to compare two tables side by side when it’s in the same sheet. First we will see how to compare two tables side by side in same sheet. Then we can move to compare two sheets to find Mismatch.

First let us see how to compare two tables in same sheet for mismatch.

Find Mismatch in Two Columns Side by Side

You can use any of the below formulas when you want to find differences in two similar columns side by side. The first formula uses the IF logical function while the second one the SWITCH logical function.

=ArrayFormula(if(A1:A=C1:C,””,”X”))

OR

=ArrayFormula(SWITCH(A1:A,C1:C,” “,“X”))

Find Mismatch in Two Columns Side by Side

Here I’m comparing two columns, columns ‘A’ and column ‘C’ side by side. Here data starts from Row 1. So the formula should be entered in Row 1, but any columns other than Column A and C.

Here by virtue of the ArrayFormula, you only need to key in the formula in one cell. It will automatically get expanded below. Wherever mismatches occur, it would leave an ‘X’ mark in the corresponding row.

How to Compare Two Tables Side by Side in Google Sheets to Find Mismatch

When you want to compare a table with more than two columns side by side in Google Sheets, here is the formula.

=ArrayFormula(IF(ArrayFormula(A1:A&B1:B)=ArrayFormula(D1:D&E1:E),””,”X”))

The above formula compares two tables with two columns each.

Compare Two Tables Side by Side in Google Sheets

How to use this comparison formula for your purposes?

In this formula you may want to make changes in two places.

Table 1 data range.

A1:A&B1:B

Table 2 data range.

D1:D&E1:E

When you want to compare a three or more column data, the above range would be like “A1:A&B1:B&C1:C” for first table and “D1:D&E1:E&F1:F” for the second.

The Steps to Compare Two Sheets in Google Sheets for Mismatch

Here we are going to compare entire two sheets for differences. So definitely a third sheet is required to point out the differences or mismatch.

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

The above is that magical formula to compare two sheets for mismatch in Google Sheets.

You should add this formula in the very first cell of your third sheet. Then copy and paste the formula to each and ever cell in that sheet. You can limit the copy paste as per your data ranges in Sheet 1 or Sheet 2.

How the sheet comparison for mismatch taken place. See the screenshot which is in Sheet 1, Sheet 2 and Sheet 3 order.

Compare Two Sheets in Google Sheets for Mismatch

The formula in sheet three found four mismatches between Sheet 1 and Sheet 2. It point out the same in corresponding cells with values from sheet 1 and sheet 2. The values in Sheet 1 and Sheet 2 are separated by “|” symbol.

Conclusion

We have learned the following data table comparison in Google Sheets.

  • Compare two columns in same sheet for difference.
  • Compare more than two columns in same sheet to find difference.
  • Compare two sheets in Google Sheets for mismatch

That’s all for now. Enjoy!

LEAVE A REPLY

Please enter your comment!
Please enter your name here