Unifying Data. How to Join Tables in Google Sheets

Joining Tables in Google Sheets: A Comprehensive Guide #

Joining tables is an essential task when working with structured data in Google Sheets. It allows you to combine data from multiple tables, based on common fields, into a single table. Whether you want to consolidate information from different sources or compare data sets, joining tables can help you analyze and uncover valuable insights. In this article, we will explore how to join tables manually in Google Sheets and provide a Google Apps Script solution for automating the process.

Manual Table Joining in Google Sheets #

Joining tables manually in Google Sheets involves using functions like VLOOKUP, INDEX, and MATCH. Follow these step-by-step instructions to perform a basic table join:

Prepare the source tables: Ensure that the tables you want to join are present in your Google Sheets document and have a common field (column) to merge them on. For example, consider two tables - Table A and Table B - both containing a "Customer ID" column.
Add a new column to the destination table: In the destination table (where you want to merge the data), add a new column to store the merged data from the source tables. You can insert this column adjacent to the common field you will use for joining.
Write the join formula: In the new column of the destination table, write a formula utilizing the VLOOKUP or INDEX-MATCH combination to join the tables. For example, if you want to join Table A with Table B, assuming the common field is "Customer ID," you can use the following VLOOKUP formula:

=VLOOKUP(TableA!A2, TableB!A:B, 2, FALSE)

This formula searches for the Customer ID in Table B, retrieves the corresponding value from the second column (B), and inserts it into the new column of the destination table.

Drag the formula down: Once you have written the join formula, drag it down to apply it to the rest of the cells in the new column of the destination table. Google Sheets will automatically adjust the references based on the relative positions.

By repeating the steps above, you can join multiple tables in Google Sheets manually. This method works well for smaller datasets but can become time-consuming and error-prone when dealing with larger datasets or complex join conditions. To automate the process, you can utilize Google Apps Script.

Automating Table Joining with Google Apps Script #

Google Apps Script enables you to automate repetitive tasks in Google Sheets, including table joining. Here's an example script that demonstrates how to join two tables using Apps Script:

function joinTables() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetA = ss.getSheetByName("Table A");
  var sheetB = ss.getSheetByName("Table B");

  var dataA = sheetA.getDataRange().getValues();
  var dataB = sheetB.getDataRange().getValues();

  var result = [];

  for (var i = 1; i < dataA.length; i++) {
    var customerIdA = dataA[i][0];
    var rowB = dataB.find(function(row){
      return row[0] === customerIdA;
    });

    if (rowB) {
      var mergedRow = dataA[i].concat(rowB.slice(1));
      result.push(mergedRow);
    }
  }

  if (result.length > 0) {
    var destinationSheet = ss.getSheetByName("Merged Table");
    destinationSheet.getRange(2, 1, result.length, mergedRow.length).setValues(result);
  }
}

To use the above script:

Open your Google Sheets document.
Go to Extensions → Apps Script from the menu. This will open the script editor.
Replace the sheet names ("Table A", "Table B") and destination sheet name ("Merged Table") in the script with your own sheet names.
Save the script and close the script editor.
Back in your Google Sheets document, a new option Join Tables should be available under the Extensions menu.
Click on Join Tables to execute the script and merge the tables.

Use Case Examples #

Joining tables can be helpful in various scenarios:

Sales analysis: Merge customer orders from one table with customer information from another table to analyze sales patterns and identify valuable customers.
Data consolidation: Combine multiple tables with similar data structures to create an all-inclusive summary table for reporting or analysis.
Inventory management: Join inventory data from multiple sources to track stock levels accurately and identify discrepancies.

By leveraging table joining capabilities in Google Sheets, you can unlock the potential of your data and gain deeper insights into your business or research. Whether you prefer manual methods or automation, the steps outlined here will empower you to merge tables efficiently and effectively.

To learn more about joining tables in Google Sheets, you can check out this guide.
If you're interested in using the JOIN and QUERY functions together, take a look at this tutorial on Google Sheets JOIN and QUERY.
Wondering how to query another sheet in Google Sheets? Here's a helpful guide on querying another sheet.
If you want to dive deeper into the basics of querying data in Google Sheets, this guide will walk you through the steps.
For tips on sorting and querying data in Google Sheets, you can check out this tutorial.

Published 20 Jul 2023