Combine Two Tables with Different Number of Columns in Google Sheets Query

If you’ve ever tried to combine two tables with different numbers of columns in Google Sheets Query, you know it can be a bit tricky. But fear not! I’m here to share with you a simple solution that will make your life so much easier.

The Basics: Inserting Columns with Zero Values

Before we dive into the nitty-gritty of combining tables, there’s one basic thing you need to know. In order to combine tables in Google Sheets, they must have the same number of columns. But what if your tables don’t match up?

Here’s the secret: you can insert columns with zero values in each row to make the tables align. This way, you can ensure that the number of columns in both tables is the same.

Combining Tables in Google Sheets Query

Now that you know how to insert columns with zero values, let’s get to the fun part – combining tables!

Step #1: Add a Virtual Column to Table 1

To make Table 1 match the number of columns in Table 2, you’ll need to add a virtual column. This can easily be done using a formula. Here’s an example formula:

=ArrayFormula(if(Len(Sheet1!A3:A),iferror({Sheet1!A3:C," "/row(Sheet1!A3:A)},0),))

This formula will create a virtual column filled with zero values in Table 1. Be sure to limit the values in the virtual column to the length of column A.

Step #2: Combine Table 2 with Table 1

Now that both tables have the same number of columns, you can combine them using a simple formula. Here’s an example formula:

={ArrayFormula(if(Len(Sheet1!A3:A),iferror({Sheet1!A3:C," "/row(Sheet1!A3:A)},0),));Sheet2!A3:D}

This formula combines Table 1 and Table 2 into one consolidated table. However, you may notice that there are blank rows between the two tables. Don’t worry, we can fix that with a little help from Query.

Removing Blank Rows with Query

To remove those pesky blank rows, we’ll use the Query function in Google Sheets. Here’s an example formula:

=QUERY({ArrayFormula(if(Len(Sheet1!A3:A),iferror({Sheet1!A3:C," "/row(Sheet1!A3:A)},0),));Sheet2!A3:D},"Select * where Col1<>''")

This formula will give you the combined table without any blank rows. Pretty neat, right?

Consolidating Two Tables with Different Number of Columns

But wait, there’s more! Now that you know how to combine two tables, let’s take it a step further and consolidate the data.

Imagine you have different sales data in both tables, and you want to consolidate it into one summary table. No worries, I’ve got you covered.

Here’s an example formula for consolidating the combined data:

=QUERY({ArrayFormula(if(Len(Sheet1!A3:A),iferror({Sheet1!A3:C," "/row(Sheet1!A3:A)},0),));Sheet2!A3:D},"Select Col1, Sum(Col2),Sum(Col3),Sum(Col4) where Col1<>'' group by Col1",0)

This formula will give you a consolidated table with the sales items and the sum of their corresponding values from both tables. It’s like magic!

And there you have it – a simple, yet powerful way to combine and consolidate tables with different numbers of columns in Google Sheets Query.

If you want to learn more about data consolidation in Google Sheets, check out this tutorial on Consolidate Data from Multiple Sheets Using Formula in Google Sheets.

Now go forth and conquer those tables like the Google Sheets pro you are! Happy querying!