google sheets magic

Last occupied row in a named range

This little trick returns the last occupied row of a named (or absolute) range, even if there are gaps in the range itself. It uses an array formula to flag all the occupied cells in the range and then the MATCH function to pick the last cell containing a '1' by looking for '2' and assuming the range is ordered.

=MATCH(2,ARRAYFORMULA(1/NOT(ISBLANK(range))),1)

Generate a sequence of dates

This trick is based on the SEQUENCE function using the DATEVALUE function to convert the start date in the range to a number. The range where the sequence of dates is generated needs to be formatted as date/time or it will just contain a list of numbers. You can also specify a 'step' value to skip over dates in the range.

=sequence(number_of_dates,1,DATEVALUE("start_date"),step_between_date)

Filter every 'n'th row starting at row 'm'

Certainly not a trivial exercise. Basically, take a list of items, filter out the blank ones (you don't have to do this if you want the blanks to be retained), work out the starting cell reference and the height of the compressed range, offset the range by the starting row and extract every 'n'th item from that offset, compressed range. Phew. I've tried to compress the steps into one formulae, but the FILTER function doesn't often like being used inside ARRAYFORMULA functions 😿.

Filter a list based on matches in more than one column

Not a trivial problem because one solution involves using a helper column to concatenate the list of possible values.

Get unique values from a range but ignore blanks.

I use this technique a lot to provide automatic validation from a range.

=unique(filter(range,range<>""))

Cell validation using conditional formatting

Whilst you can use the built in cell validation feature, I sometimes think it's nice to be able to give the user a gentle hint that either the cell contents conforms to a set of rules or contains some disallowed phrase or character.

This solution uses regular expression matching and a special trick which allows you to use named ranges in conditional formatting custom formulae.

Split a string into characters

There is no built in way to split a string in one cell into an array of characters because the SPLIT function will not accept an empty delimiter but you can use a combination of REGEXREPLACE to introduce a delimiter in between the characters and then split by that delimiter.

=SPLIT(REGEXREPLACE(C2,"(.)","$1|"),"|")

I actually used this in combination with the REDUCE function to calculate the sum of the ASCII codes for the string.

=REDUCE(0,SPLIT(REGEXREPLACE(C2,"(.)","$1|"),"|"),LAMBDA(x,y,x+CODE(y)))

String permutations

So, imagine you have two columns containing lists and you want to create every permutation of the concatenated text from those columns...

=arrayformula(flatten(filter(first_column,first_column<>"")&"|"&transpose(filter(second_column,second_column<>""))))

=A1=indirect("named_range")