Rows Context (Previous row and next row)
Share
Explore
Rows Context (Previous row and next row)

icon picker
Formula explaining

NextRowCategory

Next Row in Category, using logic order by date and item name
Partitioning:
Just filter what you want to compare into.
Sorting:
Sort ascending in reverse order of importance of columns for your logic, so for example you want more importance on date and less on item name.
Removing not desired rows:
Following the same logic, because we want Next, Next meaning >, we use >= to filter on a semi-unique key (can be concatenated as you see):
Check priority order, we want date to be our priority, so we concatenate first date.
When you concatenate dates, you need to convert to a universal number.
If the key is really unique, you can use > and avoid RowId.
Removing current row if semi-unique key:
Take note, you can use && to say “AND” like “filter by this AND this”
Like math, parenthesis are free and useful to be sure is correct.
Only needed if you don’t have a unique key.

NextRowSubCategory

Next Row in Sub-Category, using logic order by date and item name.
Pretty easy if you know first step
Partitioning Step:
Just filter what you want to compare into.
Take note, you can use && to say “AND” like “filter by this AND this”
Sorting Step:
Sort in reverse order, so for example you want more importance on date and less on item name.
Removing not desired rows Step:
Following the same logic, because we want Next, Next meaning >, we use >= to filter on a semi-unique key (can be concatenated as you see):
Check priority order, we want date to be our priority, so we concatenate first date.
When you concatenate dates, you need to convert to a universal number.
If the key is really unique, you can use > and avoid RowId.
Removing current row if semi-unique key Step:
Take note, you can use && to say “AND” like “filter by this AND this”
Like math, parenthesis are free and useful to be sure is correct.

PreviousRowCategory

Next Row in Category, using logic order by date and item name
Partitioning:
Again, just filter what you want to compare into.
Sorting:
Sort in descending in reverse order, so for example you want more importance on date and less on item name.
Removing not desired rows:
Following the same logic, because we want Previous, Previous meaning <, we use <= to filter on a semi-unique key (can be concatenated as you see):
Check priority order, we want date to be our priority, so we concatenate first date.
When you concatenate dates, you need to convert to a universal number.
If the key is really unique, you can use > and avoid RowId.
Removing current row if semi-unique key:
Take note, you can use && to say “AND” like “filter by this AND this”
Like math, parenthesis are free and useful to be sure is correct.

Share
 
Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.