CODEWITHSUNDEEP
arrayssortinggoogle-sheetsfiltergoogle-sheets-formula
cartonn
cartonn

Reputation: 8754

How do I reverse an array in Google Sheets?

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?

Upvotes: 5

Views: 8324

Answers (4)

BoarGules
BoarGules

Reputation: 16941

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:

Named function definition pane

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.

Upvotes: 2

bjg222
bjg222

Reputation: 906

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.

Upvotes: 3

player0
player0

Reputation: 1

use:

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

enter image description here

Upvotes: 4

Erik Tyler
Erik Tyler

Reputation: 9355

Try this:

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

Upvotes: 2

Related Questions