How to aggregate rows and columns in sheets

• Introduction
• Basic Arrays
• Arrays within Functions

How to aggregate data from different rows and columns for further processing.

Introduction

There are many ways in which aggregated data can be used. You might need to grab an array of data from different rows or produce a sorted and unique list of items from some columns. Commonly these aggregations are often used as inputs for other functions and are known as arrays in the Google Sheets product documentation.

Basic Arrays

Arrays are built using the curly brace syntax ({LIST OF RANGES}). Ranges of cells are addressed in the normal way (e.g. A1, A:A, A2:C etc.) but are delineated by either a comma (,) or a semicolon (;). The delineating character essentially sets the direction (comma = horizontal, semicolon = vertical) of the output array.

If you wished to pull these three columns together into a single column/array, you could use the following formula.

{A1:A11;B1:B11;C1:C11}

If you wanted a single row containing all these fruits, then you could do this.

{A1:C1,A2:C2,A3:C3,A4:C4,A5:C5,A6:C6,A7:C7,A8:C8,A9:C9,A10:C10,A11:C11}

Arrays within Functions

That was inelegant and tiresome to type, but you get the idea. The easiest way to swap the orientation would be to wrap our array in a transpose function.

TRANSPOSE({A1:A11;B1:B11;C1:C11})

This is much more useful, as we are using the array as an input to another function. It is particularly helpful when we want to sort our array or output unique values.

SORT(UNIQUE(({A1:A11;B1:B11;C1:C11})))

QUERY(UNIQUE({A:A;B:B;C:C}), "select Col1 where Col1 <> '' order by Col1")

In the second example above, we are using a query function to remove any blank entries from our output list (by way of a ‘where’ condition). This is particularly useful as it allows us the aggregate the entire columns and not have to change our formula every time we add more data. Similar to using query with importrange, when using an array as a data source for a query, you can’t address the columns by their letter headings anymore. Instead, you have to use the Col1, Col2, Col3 naming unless you specify a heading row.

We can also use our array as an input into a query or sparkline function. The latter is particularly useful as it allows the selective use of data from a single row as the source for a sparkline. So, instead of having to use a contiguous set of cells in your sparkline, you can pick and choose.

SPARKLINE({C1:E1,M1:O1,Q1:R1})

Any function that accepts a range can also normally accept an array, just make sure that you get the orientation correct!