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 semicolon 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
:
A  B  C  

1  Currency  Sales  Costs 
2  USD  2,000  0 
3  AUD  1,100  100 
4  USD  3,000  500 
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:
={{Data!A1:A4,Data!B1:B4};{Data!A1:A4,Data!C1:C4}}
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:
={{Data!A1:A4 \ Data!B1:B4};{Data!A1:A4 \ Data!C1:C4}}
The result of the above formula on a new sheet labelled
Report
would be something like this:
A  B  

1 
Currency
={{Data!A1:A4,Data!B1:B4};{Data!A1:A4,Data!C1:C4}}

Sales 
2  USD  2,000 
3  AUD  1,100 
4  USD  3,000 
5  Currency  Costs 
6  USD  0 
7  AUD  100 
8  USD  500 
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:
=QUERY({{Data!A1:A4, Data!B1:B4};{Data!A1:A4,Data!C1:C4}},"SELECT * WHERE Col1 = 'USD' ORDER BY Col2 DESC", 0)
The above
QUERY
formula produces the following results:
A  B  

1  USD  3,000 
2  USD  2,000 
3  USD  500 
4  USD  0 
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.