Google Sheets. Union ranges. How to do it?

While working with Google Sheets, have you ever felt the need to pull the and union data from several tabs into another one? Basically, you can simply link certain tabs to a sheet. But if you work with dynamic data ranges that are different from day to day, how would you know how many rows you need? Using a function in Google Sheets Union ranges you can see that there is a solution!

If you prefer a video format, check out a video tutorial below:

Working with separate ads tabs

I have created a document where we have Google Ads and Bing Ads tabs that we want to combine in the GDS Total Ads tab.

Let’s start with the Bing Ads tab.

Firstly, I would like to create a new column called Ads platform. We write a formula that has an idea “if the cell is empty, keep it empty, but if we have any information there, type Bing Ads”. The formula looks like this:

=if(C2=” “, “ “, “Bing Ads”)

We should add an ARRAYFORMULA so that the formula is applied to the whole column. That makes our formula look like this:

=ARRAYFORMULA( if(C2:C=””,””,”Bing Ads”))

We can add additional rows and the cells will be empty. That is how this formula works.

Let’s do the same thing with the Google Ads tab.

We create Ads platform column and apply formula to the whole column:

=ARRAYFORMULA( if(B2:B = “”,””,”Google Ads”))

Setting up Total Ads tab

The sense of the formula we need to use to unite the ranges is “please filter this data range and make the number of rows the same as the lengths of the data range”.

To unite both of the ranges we need a formula that consists of a filter and a link, and as we need to unite two ranges, our formula looks like this:

={filter(‘Google Ads’!A2:H, len(‘Google Ads’!A2:A)); filter(‘Bing Ads’!B2:I, len(‘Bing Ads’!B2:B)) }

We take data from Google Ads tab. As I don’t want to take the header title we select the range from A2 to H, that contains the full data that we need. Then as a second argument I use the len formula, which means calculating how many rows we have in this tab. And I do the same thing for Bing ads.

You can unite as many data ranges as you want.

I hope this report was interesting and useful for you! Leave your questions and thoughts about it in the comments section below!