Concatenate Multiple Ranges Into One And Filter Using QUERY: Google Sheets (Example)

How do you concatenate two ranges into one contiguous range for use in the QUERY function for the data parameter in Google Sheets?

To concatenate two ranges into one for use as the first parameter in the QUERY function in Google Sheets, simply combine your data sets together using the set notation {} and the semi-colon character to separate each range ; e.g. {{Data!A:A, Data!B:B};{Data!A:A, Data!C:C}}.

For example, suppose you have the following transactional data on sales and associated direct costs like so on a sheet labelled Data:

Transactional data with currency, sales and cost

As each row contains several pieces of information per column, how can you separate out the transactions so that they appear per row?

Here is where in Google Sheets the technique of being able to combine through concatenation and to create your own data sets really shines.

To combine the two sets of transactional data into their own rows would be to create a formula like so:

For locales where the dot is a decimal separator the above should work fine, however, if your locale has commas for the decimal separator substitute the commas above for backslashes:

The result of the above formula on a new sheet labelled Report would be something like this:

Creating a new data series of transactions in rows from the columnar data

As you can see from the above data table the one formula produces a concatenated new range of individual transaction rows from the original columnar range of transactions.

If you have written your new concatenated data range and you get an error like this:

In ARRAY_LITERAL, an Array Literal was missing values for one or more rows.

#VALUE! Error

Make sure your ranges are the same height and width. If one range is larger than another this process will not work. You must make sure the dimensions of each range in the series is the same.

How To Filter Concatenated Ranges

Now that you can create your own concatenated ranges with the set notation – how do you filter these ranges so that you can just have a specific subset of this data?

To filter a concatenated range, insert the data into the first parameter of the QUERY function in Google Sheets.

Continuing on with the example above, if you wanted to filter the transactions that were in USD, you could create a QUERY function like so using the data set notation:

The above QUERY formula produces the following results:

Filter transactions using a QUERY

As you can see from the above result the concatenated range I created is now used as the first parameter in the QUERY function and by using a SELECT with WHERE clause I can filter my new data range accordingly.

Be mindful when applying a filter to the new range that you cannot use the name of the columns as the expression, instead you must use ColX where X represents the column index of the new data range.

If you are getting errors extract the data first to check the data is correct, then if this isn’t working check your ColX is correct with X not exceeding the width of the data.

Summary

Sometimes you are handed data that isn’t structured in the way you would need to have it set for your purposes. Learning how to concatenate ranges is an important skill when working with data sets.

Google Sheets can enable you the ability to concatenate ranges together to form a new range for use within other functions such as QUERY. Remember when concatenating and constructing your new ranges to be mindful of the dimensions of the ranges used as this may cause errors.

When using your new data set in functions like QUERY make use of ColX where X is a number according to the column index, starting at 1.