How to Compare Columns in Google Sheet?

How to Compare Columns in Google Sheet?

One of the major problems faced by users of Google Sheets, especially, organizations and enterprises is the possibility of having any kind of errors in the data that they enter. At times, the bulk of data entered may not even be subjected to proper review and over time, the piled-up data makes it extremely difficult and exhausting to have a proper filtration or to check whether the data is all right. 

Google Sheets came up with a solution for such human error that might have got into the sheets and it ensures that any anomalies that occurred in the data are properly pointed out to the user. 

Google Sheets carries out such error identification and resolution by comparing the data columns and finding the differences in them if any. The following section, with examples, explains how Google Sheets detect errors in sheets by comparing data columns.

How to compare columns in Google Sheet?

To demonstrate the process of comparing columns, sample data is being used. The data consist of the ingredients for the preparation of cookies and cake. 

As the first step, the data has to be created in Google Sheets.

Step 1. Open the Google Sheets application on the device.

Step 2. Open any blank sheet to enter the data.

Step 3. Type in the relevant data. Or else, click open the sheet with the data to be analyzed.

Step 4. Also, create a third column to the right of the last column with the title Results.

One of the best possible ways to carry out a comparison is to compare the columns row-by-row. This brings out the maximum number of anomalies. The third column of Results is required because once the formula for the comparison is provided, the Google Sheets need a space to produce the results of the comparison. The results of the comparison will be shown in the Results column as either True or False, in which the False indicates that there are inconsistencies present in a particular row of the two columns. 

The formula for comparing columns in the Google Sheets is =A2=B2.

A2 and B2 are the column numbers of the provided example. So, change the formula according to the cell numbers in your worksheet.

Step 5. Click on the C2 cell of the worksheet. (With respect to the example provided)

Step 6. Insert the formula =A2=B2 in the selected cell.

Step 7. Click Enter. The cell will now have the result of the comparison of the second row of entries.

Step 8. Copy the formula to the rest of the cells in the Result column.

Step 9. Now the entire Results column is present with the output of the comparison of the two columns.

How do you get explanatory results when comparing columns?

The above-mentioned procedure, although works fine, generates only true or false results. it may not seem like a problem when the number of users is limited to one. But if the sheet is accessed and analyzed by multiple users, it is only good to have a more explanatory result so that the other users can also understand it. 

Google Sheets provide an option to gain ‘Matching’ and ‘Mismatching’ as the outcome and it only takes several steps.

The syntax for this kind of comparison result is =IF(A2=B2,”MATCHING”,”NOT MATCHING”).

It has to be noted that the given syntax is concerning the example provided. Change the cell numbers according to the data that you are working with.

Step 1. Follow the initial procedures mentioned above on opening a blank sheet and creating the data table with a third column to record the result. Click on the second row of the Result column and paste the syntax =IF(A2=B2,”MATCHING”,”NOT MATCHING”) into the selected cell.

Step 2. Click the Enter button and now, the result will appear on the selected cell as either Matching or Not Matching.

Step 3. Copy the syntax into the rest of the cells in the Results column. Once the syntax is copied properly, click Enter and now the entire data table can be found to be properly compared and the outcome can be found in the third Result column.

The columns are successfully compared. The users can assign any words as the outcome or result by making necessary adjustments and changes in the syntax.