Previously I posted how you can use the INDEX() function to obtain the fields needed for a simple SUM() function.
Then I came across another handy Google Sheet function ARRAY_CONSTRAIN().
What ARRAY_CONSTRAIN Formula Does
There are three parameters with this formula:
range – insert the range for the formula to operate on.
num_rows – set the number of rows to compress.
num_cols – set the number of columns to compress.
All are required fields.
Example
Let’s look at a current depreciation problem I’m trying to solve.
I’m trying to compress the different types of depreciation on an array of different purchases projected over the next few years and to calculate what the new depreciation would be once these items have been purchased.
What we’re trying to achieve is to determine what the depreciation would be each year according to the year when items have been purchased, and their corresponding depreciation life in years.
Limitations of ARRAY_CONSTRAIN
Unfortunately the big limitation of ARRAY_CONSTRAIN is it works from the top-leftmost corner to cut the excess cells.
For example, if we apply the following formula on our data set:
=ARRAY_CONSTRAIN(C1:G6, 3, 2)
It will return for us the following highlighted cells:
ARRAY_CONSTRAIN() function only returns data from top-left cell
This causes a problem for our depreciation schedule as we’d like to fetch the last two years when we’re in the 2024 column for row 3, so using the following formula:
=ARRAY_CONSTRAIN($C3:G$3, 1, B$3)
For the Network Upgrade row would only return [20000,<blank>], as these are the nearest to the top-left cells.
Workaround to ARRAY_CONSTRAIN
Is there a way to flip the input row?
There is!
Is there a REVERSE() function in Google Sheets we can apply to the range?
Unfortunately not yet.
So what we need to do as a workaround is to create our own REVERSE() function by sorting our data and reversing it backwards.
Thankfully my data already has a year row (Row 1), all I need to be able to do is sort the data set by that row, apply descending sort and then insert the new range back into the ARRAY_CONSTRAIN() formula.
Here’s how I flipped my data range:
=ARRAY_CONSTRAIN(QUERY(TRANSPOSE($C$1:G3),"SELECT Col" & ROWS($C$1:G3) & " ORDER BY Col1 DESC", 0),$B3,1)
Let’s break apart this function and understand what’s happening, as usual we’ll start with the innermost functions and work out way out:
ROWS($C$1:G3) – I used this to be able to get the column number to return from the QUERY function. As the last Column in the data set is the one I wanted, I just obtained this from the height of the original input range.
TRANSPOSE($C$1:G3) – as my data set is in rows, I need to translate this into columns for it to be functional as a data set with the QUERY() function. The data set needs to include the row containing the years.
QUERY(..., "SELECT Col" & ... & " ORDER BY Col1 DESC", 0) – the second SELECT parameter to our QUERY() function fetches just the one column, but orders by the first column being the column containing all the year values. The third parameter 0 just makes sure we don’t return any header rows.
ARRAY_CONSTRAIN(..., $B3, 1) – we finally wrap everything back into our original ARRAY_CONSTRAIN function. This time we change the parameters a little as they are in a column format, but the purposes are still the same – we want the nearest 2 years (the value of cell $B3) – and as there’s only 1 column our third parameter is 1.
To calculate depreciation on this row our final touch is to sum everything returned from the range and divide by the number of years:
=SUM(ARRAY_CONSTRAIN(QUERY(TRANSPOSE($C$1:G3, "SELECT Col" & ROWS($C$1:G3) & " ORDER BY Col1 DESC", 0), $B3, 1))/$B3
We further wrap the formula in a check to see if there are any depreciation values in the Dep’n (Years) column:
=IF(LEN($B3),SUM(...),"")
Result
Here’s the final result showing the function as well as our accumulated depreciation for the items purchased:
Calculating different depreciation schedules using QUERY and ARRAY_CONSTRAINT in Google Sheets
Conclusion
In this post you’ve been able to discover and learn about the ARRAY_CONSTRAINT. You’ve seen its limitations in fetching from the upper-leftmost corner, and perhaps in time Google Sheets will append an additional parameter to set where the corner should be fetched from.
In the meantime we’ve discovered how we can reverse or flip a range to make use of the ARRAY_CONSTRAINT function by using the TRANSPOSE and QUERY functions together.
In today’s post I’d like to explain how I used the following formula to help merge multiple columned data into a different columned arrangement as the purpose of the result was to export the original data set into a CSV file for import into NetSuite.
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.
An example of the raw data looked something like this:
Employee
Jan-2020
Feb-2020
123 Smith, Bob
2,000
2,200
456 Doe, Jane
3,000
3,300
789 Doe, John
200
The original spreadsheet had all months of the year and is labelled as Data:
Employee salaries per month
I had to import this data into NetSuite using the CSV Import process, which required changing the structure a little bit, so that the resulting CSV looked like so:
Employee ID
Employee Name
From Date
To Date
Amount
123
Smith, Bob
1/01/2020
31/01/2020
2,000
123
Smith, Bob
1/02/2020
29/02/2020
2,200
456
Doe, Jane
1/01/2020
31/01/2020
3,000
456
Doe, Jane
1/02/2020
29/02/2020
3,300
789
Doe, John
1/02/2020
29/02/2020
200
As you can see with the requirements of the task, we need to be able to expand the data set to rows (as it’s currently laid out in columns), then merge the data back into one columnar set. Once we have achieved this we then use the handy File > Download > Comma-separated values (current sheet) option in Google Sheets.
Solution: Step 1 – Expansion
The first rule when manipulating data is to leave the original data set intact.
Therefore, we create a fresh new sheet and label this whatever name is appropriate for your needs. In this example we will name it appropriate to what we are doing: DataExpand.
In our expansion we want to achieve all necessary CSV fields required. This means we need to fetch the ID number from the original Employee field, and to copy the remaining data into another field for the Employee Name. Then we split the header column in our original sheet to a data range. Followed lastly with the amount in each date range.
Our first sheet, should look a little something like this:
Perform the CSV requirement per month
Here are the details as displayed in the photo above:
Cells $B$1 and $G$1 contained the =COLUMN() reference to each of the months on the original data set. This just helps with creating an easy index function that uses that reference which can make it easier copying the same columnar set 12 times.
Row 1 this contains the following formula to extract the ID number from the Employee field. Here we check to make sure we have data in the original Data sheet, if we do we want to perform a regular expression where we just want all the digits, therefore, we use the regular expression of d+:
=if(len(Data!A2),REGEXEXTRACT(Data!A2,"d+"),"")
From $B$2 down we then use another regex formula to extract those digits and replace them with nothing. We also want to check if the employee received any salaries or wages, if they didn’t we don’t want to add them into the data set.
From $C$2 down we just insert the ID that is needed for each row if we have a confirmed value in the previous column.
=if(len(B2),$A2,"")
For $D$2 down if we have a value in $B$2 then we get the value of the salaries for that respective month. We use the handy index function here to get that value:
=if(len(B2),index(Data!$1:$1000,row(),B$1),"")
For $E$2 down if we have a value in $B$2 then we get the column heading of the respective month using, once again, the index function:
=if(len(B2),index(Data!$1:$1000,1,B$1),"")
Lastly, for $F$2 down we get the value in the previous cell, if something exists, and calculate what the last day of the month will be for that cell:
=if(len(E2),date(year(E2),month(E2)+1,0),"")
As we have been mindful of the formulas used for each budget month, we now just need to copy cells $B$2:$F$1 and then paste this 12 times across. We could make it even easier by, after our first paste in cells $G$2:$K$4 changing cell $G$1 to be a formula: =$B$1 + 1:
Change header row on first paste to formula
Then we copy this newly pasted range with formula edits across 10 more times.
Solution: Step 2 – Concatenation
The purpose of the next sheet is to zip up our columnar collections. This is a relatively simple step, however, there a couple of things to be wary of:
Use TEXTJOIN when zipping up the columnar data rather than the JOIN or CONCATENATE functions. The primary reason for this is where there is no data in a cell TEXTJOIN creates the blank join, whereas the other functions neglect it. As an example, if we had the following table:
A
B
C
1
Missing
Puzzle
Piece
2
Missing
Piece
When using =JOIN(",",A1:C1) it produces the result Missing,Puzzle,Piece for the first row and =JOIN(",",A2:C2) will produce Missing,Piece for the second row.
Whereas using =TEXTJOIN(",",FALSE,A1:C1) produces the result Missing,Puzzle,Piece for the first row and =TEXTJOIN(",", FALSE, A2:C2) will produce Missing,,Piece for the second row.
As the TEXTJOIN function allows for the flexibility of the concatenation of your columnar data it’s import the data remain as it is represented elsewhere if there happens to be blank cells in your data.
The other important aspect with this formula is that we want to replace the space characters within our cells as the space character will be used to join and split data back into columns again later. Therefore, it’s important at this step (if your data also contains spaces) to replace the space character with an oblique character that is not used anywhere in your data set.
I am going to replace my space characters within my data to the tilde character ~.
Therefore, I have created a new sheet, which I’ve labelled DataConcat and cell $A$1 has the formula:
Be mindful when transferring the data across to adjacent columns that you don’t copy paste or drag across the formula, as each column here needs to reference the collection in DataExpand. For example, in the next adjacent cell $B$1 the formula is:
As with mathematics, we have to start with the inner most functions and work our way out. So let’s break up each component within this formula and explain what’s happening:
QUERY function
QUERY(DataConcat!A:L,,ROWS(DataConcat!A:A))
Here we use the QUERY function to capture the data we want to operate on (the first parameter), being all the columns in the DataConcat sheet, and we then want to combine these cells into one row. We achieve this by setting the number of headers (the third parameter) to the maximum number of rows in the DataConcat sheet. This means every row in the DataConcat sheet will be zipped up into one row!
The way the QUERY function zips everything up is by applying the space character to each new cell. This was why it was important for us to remove the space characters from our own data set before we applied this function.
The combination of these three functions is to merge the single row returned by the QUERY function into one column.
First we JOIN the rows together using the special space character which helps to delimit each individual row. Then we SPLIT by the space character which produces the rows across individual cells horizontally. Finally, the TRANSPOSE function translates the horizontal cells into vertical formation – a single column.
Unfortunately the JOIN function does have a character length limitation of 50,000 characters. You may need to break up your requirements or look at other means if this ends up being an impediment.
EXPANSION – Using ARRAYFORMULA
The last step is expand our singular column so that it can be represented as needed for CSV export. If we’re expanding data sets we need to use the ARRAYFORMULA function, as demonstrated here:
The ARRAYFORMULA provides us with the ability to loop through each of the cells in our data set and to apply individual functions to them. In our case we want to:
SUBSTITUTE back the oblique tilde ~ character with its original space character.
SPLIT back the data we concatenated in the DataConcat sheet. However, we need to be mindful there may be instances where we have blank cells, and we want the SPLIT function to return the blank cells, therefore, we need to change our fourth parameter in the SPLIT function to FALSE (default is TRUE). By switching this to FALSE it turns consecutive delimiters like ;; into three separate cells, rather than just one cell.
With these functions all combined they produce the wonderful result intended, like so:
Expansion of our singular column using ARRAYFORMULA, SUBSTITUTE and SPLIT
Conclusion
Within this post we were able to learn how we can extrapolate data from one form to another using several intermediary steps and complex Google Sheet functions.
Hopefully this post has been helpful with learning:
How to organise your steps by leaving the original data set (use multiple sheets if needed).
How to use the QUERY function and especially its third headers parameter.
Why you would use the TEXTJOIN function over JOIN.
The limitations of the JOIN function.
How to expand your data set using ARRAYFORMULA with any function when needing to loop through individual cells in the result.
How to set the SPLIT function’s fourth parameter so that consecutive empty cells aren’t treated as one.
