Docs Help

Functions:

=Filter(sourceArray, arrayCondition)
=Filter(sourceArray, arrayCondition1, arrayCondition2, ...)

This function returns a filtered version of the given source array, where only certain rows or columns have been included. Here are some tips to keep in mind while using this function:

• Each condition should be either a 1-dimensional range of boolean values, or else an array-formula expression which evaluates to a 1-dimensional array of booleans.
• If the conditions evaluate to a column array, then only the rows from the source array that correspond to the true values of the condition array will be returned.
• If the conditions evaluate to a row array, then only the columns of the source array that correspond to the true values in the condition will be returned.
• If there are multiple conditions, then all must be true in order for the corresponding values in the source array to be returned.

This behavior is similar to =ArrayFormula(IF(arrayCondition, sourceArray, "")), except that Filter will actually omit the rows or columns, rather than just leaving them blank. Also, Filter can filter an entire range based on a single row or column, rather than requiring its two arguments to be two equal-sized ranges. (The source array itself is not treated like an array formula, though you can achieve that by wrapping it in its own ArrayFormula() function.)

Examples:

• =Filter(A1:A10, B1:B10>10) returns an array of all the values in column A next to values in column B which are greater than 10.
• =Filter(A1:B10, B1:B10>10) does the same, but returns the values in both column A and column B.
• =Filter(A1:B10, B1:B10>10, A1:A10<B1:B10) also requires that the values in column A be smaller than the values in column B.

Function:

=Unique(sourceArray)

This function returns only the unique rows in the source array, discarding duplicates. The rows are returned according to the order in which they first appear in the source array.

Examples:

• =Unique({1;3;5;3;7;1;2}) returns the values {1; 3; 5; 7; 2}.
• =Unique(A1:A10) returns the unique values in A1:A10.
• =Unique(A1:B10) returns all of the unique rows from A1:B10.

Functions:

=Sort(data)
=Sort(data, keyColumn1, ascOrDesc1, keyColumn2, ascOrDesc2, ...)

This functions returns the rows in a given data range, sorted according to given key columns. Here are some tips to keep in mind as you use the Sort function:

• You can specify the key column in two ways: either as a column within the data range, or as another column outside the range. To specify a column within the data range, give its index within the range: 1 is the first column of the range, 2 is the second column, etc. To specify another column outside the range (e.g. an intermediate calculated value that you don't want to include in your output), just specify it as a range.
• Mark each key column to sort either ascending or descending, by adding either TRUE or FALSE after that specifying that key column.
• You can also specify only a single array or range. When you do so, the function returns the range that is sorted ascending by the columns from left to right.

Examples:

• =Sort(A1:A10) returns the values from A1:A10, sorted from smallest to biggest.
• =Sort(A1:A10, 1, FALSE) returns the values from A1:A10, sorted from biggest to smallest.
• =Sort(A1:A10, B1:B10, TRUE) returns the values from A1:A10, sorted by the data in column B.
• =Sort(A1:B10, B1:B10, TRUE) returns the entire range A1:B10, sorted by the data in column B.
• =Sort(A1:B10, 2, TRUE) returns the same, since B1:B10 is the second column of A1:B10.
• =Sort(A1:B10) sorts the entire range A1:B10, first by the data in column A (ascending), then breaks ties by column B (ascending).
• =Sort(A1:B10, 1, FALSE, B11:B20, TRUE) sorts according to A1:A10 descending, then breaks ties by sorting according to B11:B20 ascending.
• =Sort(A1:B10, 1, TRUE, B11:B20, FALSE) sorts according to B1:B10 ascending, then breaks ties by sorting according to B11:B20 descending.

If one of the column indices is less than 1 or greater than the number of columns in the data range, then that column index (along with its ascending/descending value, if present) is ignored. This allows you to set up formulas that sort according to a varied number of columns, such as:

• =Sort(A1:B10, C1, C2, C3) will sort according to the column IDs specified in C1, C2, and C3. To sort by only one column, fill in only one of those cells and leave the other two blank (or put 0 into them).