How To Combine Sheets With a Different Number of Columns Into One

In Google Sheets, you can merge data ranges that have the same number of columns, like A1:G from the sheet Inv 2019 and A5:G from the sheet Inv 2020. This can be done with formulas – QUERY or FILTER – or without them (Google Sheets importer). For more on this, read our Tutorial on How to Combine Data from Multiple Sheets Into One Master Overview. 

And what if the sheets have a different number of columns? Let’s say the sheet Inv 2020 has eight columns (A:H): id, company_name, address, full_name, email, date, item, and amount.

The sheet Inv 2019 has seven columns (A:G) since the item column is missing: id, company_name, address, full_name, email, date, item, and amount.

The QUERY or FILTER formula will return an error if you try to combine these sheets in the regular way.

Is there a workaround?

Merge sheets with a different number of columns using the Google Sheets importer

With the Google Sheets importer, you don’t need any workarounds. It is the easiest way to merge together two or more sheets from your current or external Google Sheets file. 

Google Sheets importer is a part of Coupler.io, a product for importing data from different data sources, such as Airtable, Pipedrive, Xero, etc. So, first you’ll need to install Coupler.io from the Google Workspace Marketplace. 

After that, open a spreadsheet and go to the Add-ons menu. Launch Coupler.io, click Open Dashboard, click +Add Importer, and select Google Sheets. 

Complete the following steps to set it up:

1. Fill out the “Title” field

Add the name of your importer.

2. Set up the Source

2.1. Connect to your Google Account

• Click Connect.
• Pick the Google Account you want to connect to.
• Sign in to the chosen Google Account.
• Coupler.io will need access to view your Google spreadsheets. Click Allow to grant access.

2.2. Fill out the “Spreadsheet URL” field

Insert the URL or ID of the Google Sheets doc you’re going to import data from. You can get it from the URL bar of your browser. 

In our case, the spreadsheet ID is the following:

1nQBbnawdY_V44lk55A3UDsdGcEQB9YR6DIpODKc94Ck

2.3. Fill out the “Sheet name(s)” field

Enter the names of the sheets to merge, each on a new line. In our case, we need to enter:

Inv 2019
Inv 2020

The order of the sheets specified in the field doesn’t identify the order of the merged sheets. If you need to merge tabs in a specific order (for example, first Inv 2020, then Inv 2019), arrange these sheets in your spreadsheet respectively.

2.4. Fill out the “Range” field

Click Show advanced to expand the “Range” field and enter the range of sheets to merge. In our case, of two ranges, enter the largest one:

A1:H

If you leave this field blank, the importer will import the entire data set of the specified sheets. So, it will work for us as well.

3. Set up the Destination

3.1. Fill out the “Sheet name” field

Enter the name of the sheet, which will receive the combined sheet.

Additionally, you can specify the address of the first cell where the data range will be imported. For this, click Show advanced and fill out the “Cell address” field.

4. Configure Settings

In this section you can:

• Enable the Automatic data refresh and customize the schedule.
• Choose the data import mode (replace or append).
• Add a column specifying the date of the last data refresh.

For more on this, refer to the Coupler.io Knowledge Base. 

Once you’re ready, click Save & Import to save the parameters and import the merged sheets. Here’s what we’ve got:

The importer has automatically matched the columns from both sheets and merged the data. Moreover, you can see the Sheet Name column added to the left, so you can easily navigate the merged sheets. 

Google Sheets importer is not the only integration provided by Coupler.io. It also allows you to import data from CSV files, Excel files, and numerous apps.

Workaround to combine two ranges with different number of columns using QUERY or FILTER 

Now, let’s check out what the native Google Sheets functions can do. For both QUERY and FILTER, you’ll need to complete the following steps:

• Add a virtual column to transform the A:G range from Inv 2019 to A:H. 
• Merge the transformed A:G range from Inv 2019 with the A:H range from Inv 2020.

Step 1: Add a virtual column to the range

Create a separate sheet – let’s name it Inv 2019 upd – and apply the following QUERY formula nested with ARRAYFORMULA:

=query(
   arrayFormula(
      iferror(
         {'Inv 2019'!A1:F,
         {" "}/row('Inv 2019'!A1:F),
         'Inv 2019'!G1:G}
      )
   )
)

• 'Inv 2019'!A1:F – data range before the virtual column
• 'Inv 2019'!G1:G – data range after the virtual column

Alternative: You can also use left('Inv 2019'!A1:A,0) instead of {" "}/row('Inv 2019'!A1:F) as follows:

=query(
   arrayFormula(
      iferror(
         {'Inv 2019'!A1:F,
         left('Inv 2019'!A1:A,0),
         'Inv 2019'!G1:G}
      )
   )
)

Read The Complete Guide of Using ARRAYFORMULA in Google Sheets For All

Step 2: Merge sheets

Now you can merge sheets using either QUERY or FILTER. For QUERY, you can use the parameter of the Inv 2019 upd sheet or replace it with the formula from Step 1 as follows:

Simple formula: 

=query({'Inv 2019'!A1:H;'Inv 2020'!A2:H},"Select * where Col1 is not null")

Advanced formula:

=query(
   {
      query(
         ArrayFormula(
            iferror(
               {'Inv 2019'!A1:F,
               {" "}/row('Inv 2019'!A1:F),
               'Inv 2019'!G1:G}
            )
         )
      );
      'Inv 2020'!A2:H
   },
   "Select * where Col1 is not null"
)

Read our guide on the QUERY function in Google Sheets.

The updated FILTER formula will look like this:

Simple formula: 

={
   FILTER('Inv 2019'!A1:H, LEN('Inv 2019'!A1:A) > 0);
   FILTER('Inv 2020'!A2:H, LEN('Inv 2020'!A2:A) > 0)
}

Advanced formula:

={FILTER(
   Query(
      ArrayFormula(
         iferror(
            {'Inv 2019'!A1:F,
            {" "}/row('Inv 2019'!A1:F),
            'Inv 2019'!G1:G}
         )
      )
   ), 
   LEN('Inv 2019'!A1:A) > 0
);
  FILTER('Inv 2020'!A2:H, LEN('Inv 2020'!A2:A) > 0)
}

Read about other ways to use FILTER Function in Google Sheets.

Formula or formula free – which method is better?

What I don’t like about formulas is that you have to be very careful with their syntax. If you forget or misplace a single character, it won’t work. Besides, the QUERY and FILTER formulas above are quite bulky. And they can be even bulkier if you are merging data from an external Google Sheets file. In this case, you need to nest either FILTER or QUERY with IMPORTRANGE. 

You can read more about this function in our Google IMPORTRANGE Tutorial.

The main drawback of the formula-free solution – the Google Sheets importer – is that you need to install Coupler.io. However, it won’t take you much time. Moreover, you’ll get a powerful tool for importing data from different sources into your spreadsheet. 

Make the right choice to optimize your workflow. Good luck with your data!