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`

:

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.