How to Compare and Find Common Values in Google Sheets Using a Script

As datasets grow larger, it becomes increasingly difficult to identify common values and overlap between different columns of data. In such cases, using a Google Sheets script can save you a lot of time and effort. In this tutorial, we'll show you how to use a Google Sheets script to compare and find common values between two columns of data.

Step 1: Open a Google Sheet Document

First, open a Google Sheet document and create two columns of data that you want to compare. For example, you may have a list of customer names in column A and a list of leads in column B. The goal is to identify any overlap between the two columns.

Step 2: Create a Google Sheets Script

Click on "Tools" in the menu bar, and then click on "Script editor". Copy and paste the following script into the script editor:

function compareColumns() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var valuesA = sheet.getRange("A1:A").getValues();
  var valuesB = sheet.getRange("B1:B").getValues();
  var valuesC = [];

  // Loop through all values in column A
  for (var i = 0; i < valuesA.length; i++) {
    var valueA = valuesA[i][0];

    // Loop through all values in column B
    for (var j = 0; j < valuesB.length; j++) {
      var valueB = valuesB[j][0];

      // Check if the values in columns A and B match
      if (valueA == valueB) {
        valuesC.push([valueA]);
        break;
      }
    }
  }

  // Write the common values to column C
  sheet.getRange(1, 3, valuesC.length, 1).setValues(valuesC);
}

This script will compare all the values in column A with all the values in column B and display any common values in column C.

Step 3: Run the Script

Save the script and go back to your Google Sheets document. Click on "Tools" in the menu bar, and then click on "Script editor" again. Click on the "Run" button (the play button) to execute the script. You should see a new column C with any common values between columns A and B.

Limitations of the Provided Script

It's important to note that the provided script assumes that there are no blank cells in columns A or B, which may not always be the case. Additionally, the script may be slow for large datasets as it loops through all values in both columns. To handle blank cells, you may need to modify the script to check for blank cells using an if statement and skip over them in the loop.

Example Use Cases

Here are three real-world examples where using this script may be useful:

1. Sales and Customer Data - A sales team could use this script to compare a list of customer names (column A) with a list of leads (column B) and identify any overlap. This would allow them to quickly identify which leads may already be customers and focus their efforts on leads that are not already in the customer database.

2. Academic Research - A researcher could use this script to compare a list of participants (column A) with a list of survey respondents (column B) and identify any overlap. This would allow them to quickly identify which