One of my Google Sheets has two tabs with multiple columns of data. In both the data sets I’ve identical column or columns. How can I compare two sets of multi-column data for differences?
To do this your datasets should contain at least one common primary key column. So that you can compare your two sets of multi-column data. See the below examples:
How Do I Compare Two Sets of Multi-Column Data for Differences?
Let’s see how to compare two datasets in Google Sheets for differences. I am starting with a single column primary key comparison.
Single Primary Key Column to Compare Two Sets of Multi-Column Data:
Here I have two datasets. For example purpose, I’ve entered both the datasets in the same sheet tab. You can use datasets in two different tabs also.
In dataset 1 and dataset 2, there is a common primary key column labeled as “Product ID”. I am going to match the product IDs in dataset 1 with the product IDs in dataset 2.
Once find the match I want to check another column for any differences. It’s the “Quantity Received” column in both the datasets. If not matching, I want to return the dataset 1 rows that contain differences.
I have already the result entered in the above sheet in the range K2: M. There is only one row that contains the difference. The quantity of product ID M2001, that is Orange, is different in table 1 and table 2. So I want that row in table 1 returned.
Here is the formula that I have used to compare two sets of multi-column data for differences.
=ArrayFormula(SORT(if(countifs(F3:F&I3:I,A3:A&D3:D)=0,A3:D,)))
How can I use this Find Difference formula in my own Google Sheets?
See the generic version of the above formula to understand it better.
ArrayFormula(SORT(if(countifs(Product ID Data Set 2&Quantity Received Data Set2,Product ID Data Set 1&Quantity Received Data Set1)=0,Dataset 1,)))
Point # 1:
In concise, when you compare two sets of multi-column data for differences, first you should decide from which dataset (dataset 1 or dataset 2) you want to get the rows with differences.
If you want to get the result from dataset 1, you should compare dataset 2 with dataset 1 (not the reverse like dataset 1 with dataset 2) as above.
In other words, In COUNTIFS, the dataset 2 column should come first.
Point # 2:
In comparison, use the required columns as combined. For example, I am comparing Column A and D in dataset 1 with column F and I in dataset 2. I’ve combined the columns in my formula above.
Must Read: Google Sheets Functions Guide.
How to Use Two Primary Key Columns to Compare Two Sets of Multi-Column Data?
The above same logic you can apply to any number of columns. You only require to join the columns in COUNTIFS as below.
In example one, I have used one primary key column from each table for finding the differences. Here is how to use 2 or more columns.
Here I am matching Product ID, WH No., and Quantity Received. The relevant columns in dataset 1 are columns A, B, and D. You can say the primary key columns are column A and B.
In dataset 2, the relevant columns are the columns F, G, and I. The primary key columns are column F and G.
See the formula to understand how the columns combined.
=ArrayFormula(SORT(if(countifs(F3:F&G3:G&I3:I,A3:A&B3:B&D3:D)=0,A3:D,)))
Again I am repeating, in COUNTIFS, the dataset 2 columns should come first. You can use this logic with any number of columns.
That’s all. Follow the above steps to compare two sets of multi-column data for differences in Google Doc Spreadsheets.
