The QUERY function in Google Sheets is a powerful function that helps to operate on a range of data, however, on a current project I needed the QUERY function to ignore rows where a certain column was empty. Here was how I was able to get the desired output. To ignore blank or empty cells using Google Sheet’s QUERY function add the condition IS NOT NULL in the WHERE clause for the column where the blank or empty cells are found. ...
If you have two or more columns of data and you want to merge these columns into one column then using the Google Sheets QUERY formula may be one way you can achieve this. Here’s an in depth explanation of how to achieve this: Step 1 – Prep your data If you data doesn’t contain any spaces then you’re good to go, if though your data does contain spaces then you will need to define a character at the end of each cell to get this to work. ...
How do you merge multiple columns and then expand them into a different arrangement using Google Sheets? Using a working example I will demonstrate how to migrate a specific data set containing columns, into a different data set using a different arrangement of columns. The final formula is quite the monster and I’ll dissect this piece by piece to help demonstrate the process: = A R R A Y F O R M U L A ( S P L I T ( A R R A Y F O R M U L A ( S U B S T I T U T E ( T R A N S P O S E ( S P L I T ( J O I N ( " " , Q U E R Y ( D a t a C o n c a t ! A : L , , R O W S ( D a t a C o n c a t ! A : A ) ) ) , " " ) ) , " ~ " , " " ) ) , " ; " , T R U E , F A L S E ) ) Original Data Structure I had exported the following salary data from our budgeting software which contained how much each staff person would be receiving each month throughout the financial year. ...
How do you apply the sum to a certain number of cells according to another input cell? The SUM function works amazingly well when the range sought for its total value is static, and one way of making a SUM function somewhat dynamic is to make the cells it references change, but what if you need the SUM function to total a range according to an input cell? Something that would look a little like this in words: ...
If you are testing the return value of a function in the immediate window in VBA and get the following error: Wrong number of arguments or invalid property assignment (Error 450) What you are doing is something like this: Function GetCollection() As Collection Dim coll As New Collection coll.Add "Hello" coll.Add "World" Set GetCollection = coll End Function Then in the immediate window typing: ? GetCollection() The code appears to work fine, but the error is a mystery. The reason for the error is that the immediate window call expects a collection, but isn’t receiving one. ...
Perhaps the easiest way to assign a default value to a variable is to append the || conditional after referencing the variable. For example, after looping through an array and mapping the values to properties within an object, I needed to test whether the property had been assigned. As I use Google Spreadsheets to iterate through data imagine the following data set: A B C 1 Name Age Choice 2 Ryan 50 A 3 Sara 27 4 Jude 29 C Data set containing rows of data, except for one row with a blank “Choice” value. ...
Recently, I had to merge two columns into only one column on a spreadsheet. The way I found to do this was by using the following common spreadsheet functions: JOIN, TRANSPOSE and SPLIT, and if needed UNIQUE. I was able to find a solution, and I’ll illustrate how it worked by using an example. Let’s assume the following columns of data: The first thing we will do is move to the next adjacent column, column C, and in cell C1 enter the following formula: ...
How do you make the range and column index number in a VLOOKUP function in Google Sheets dynamic? The VLOOKUP function is a popular formula used in spreadsheets to source data using the first column of a range as the primary key to search the search_key and then to return the intersection of the cell in that row with column_index_number. Here is the syntax of the VLOOKUP function with its named parameters: ...