How do I reverse an array in Google Sheets?

Question

Suppose I have a long, unordered, and gradually growing column that I search the even numbers from.

=filter(A1:A,ISEVEN(A1:A)=TRUE)

Now I would like to reverse this result, so that the last even number in column A is listed first in the result. Many examples use helper columns or the row function, but that doesn't seem to be an option because you don't know how many even elements there are in this array before you begin. And even if I separately calculate that using rows I can't seem to figure it out. It seems like there should be a rather simple way to just "reverse" an array right?

Gumshoe

Answer 1

use:

=INDEX(SORT(FILTER({A1:A, ROW(A1:A)}, ISEVEN(A1:A), A1:A<>""), 2, 0),, 1)

Aeneid

Answer 2

If you're willing to delve into Apps Scripts (Extensions > Apps Scripts), you can use the JavaScript Array.prototype.reverse() function.

Paste this into your Apps Script code:

/**
 * Reverses range
 *
 * @param {Array} range The range of cells
 * @return The input range reversed
 * @customfunction
 */
function REVERSE(range) {
  return range.reverse();
}

Then use the formula in your cell as so:

=REVERSE(A1:A)

One advantage of this approach is that you then have a compact and reusable function that can be nested inside other formulas more easily. However, I have not done any testing to see the performance impact of this solution in large sheets vs the more traditional formulas, so if you have a lot of data, you might want to check and see which works more efficiently for you.

Ladner

Answer 3

Try this:

=QUERY(FILTER({A:A,ROW(A:A)},ISNUMBER(A:A),ISEVEN(A:A)),"Select Col1 Order By Col2 Desc")

Maunder

I think your answer is the way to do it. I suppose more generally if you had an array rather than a range so you couldn't use the Row function, you would need to count the number of rows in the array and use Sequence(number_of_rows) to number the rows of the array. – Legislation

Yes. I only had what the OP offered to go on, and he uses a real range in his original formula. – Maunder

Answer 4

This is an old question, but also a very common one, and I haven't seen any answers that I like, on SO or elsewhere. That may of course be because my searches have been insufficiently diligent.

Create a named function called, say, REVERSE_COLUMN, and define it like this:

=SORT(col,SEQUENCE(ROWS(col)),FALSE)

If you haven't done this before, you will find it at Data | Named functions | Add new function. It will look like this:

Then you can call the function like this =REVERSE_COLUMN(A1:A10).

If you pass it a rectangular block of cells instead of a single column (like A1:C10) it will do what you probably expect: it will treat every row in the block as a unit and will reverse the block row-wise. Or, if you prefer to think of it that way, it will take every column in the block and reverse it individually.

That behaviour, which I consider reasonable, gives rise to the one known issue, that for my purposes was not worth fixing: The function will appear to do nothing, and will not warn you, if passed a row rather than a column. That is because it treats its input as a column consisting of a single row, with several cells in that row, and reversing a the order of a column with only one row yields the identical column with only one row.

If that is troublesome, define a second function REVERSE_ROW like this:

=Transpose(SORT(Transpose(row), SEQUENCE(COLUMNS(row)), FALSE))

This technique works equally well with arrays. Just remember that the array corresponding to a single column of cells looks like this: {1;2;3;4;5;6;7;8} while the array corresponding to a single row of cells looks like this: {1,2,3,4,5,6,7,8}, and built-in functions that return an array may return a row when you might expect a column.

Argillaceous