# I need a formula to reverse the order of a column of variable length in Google sheets

The formula and custom function (created by doubleunary here) below repeat rows in an alternating pattern the number of times given in Column B.

=arrayformula({RepeatRows(\$A\$2:\$A\$6, B2:B6); If(SUM(B7,B8,B9)=0, flatten(if( sequence(1, 1), +"Extra" ) ), RepeatRows(\$A\$7:\$A\$9, B7:B9)); flatten(if( sequence(1, 100), +"Extra" ) )})

``````/**
* Repeats each row in rows_to_repeat times_to_repeat times, in an alternating fashion.
*
* @param {A2:B4} rows_to_repeat The values to repeat row-by-row.
* @param {D2:D4} times_to_repeat The number of times to repeat each row in rows_to_repeat.
* @return {Object[][]} The repeated rows.
* @customfunction
*/
function RepeatRows(rows_to_repeat, times_to_repeat) {
// version 1.1, written by --Hyde, 24 January 2022
//  - return rows in an alternating order, see https://webapps.stackexchange.com/q/162501/269219
let rows = rows_to_repeat;
let times = times_to_repeat;
if (!Array.isArray(rows)) {
rows = [[rows]];
}
if (!Array.isArray(times)) {
times = [[times]];
}
let array = [];
rows.forEach((row, index) => {
array.push([]);
const numRows = times[index] ? times[index][0] : times[0][0];
for (let i = 0; i < Number(numRows); i++) {
array[index].push(row[0]);
}
});
return transposeArray_(array).flat();
}

/**
* Transposes a 2D array.
*
* @param {Object[][]} array The array to transpose.
* @return {Object[][]} The transposed array, or the single value as is.
*/
function transposeArray_(array) {
var result = [];
for (var row = 0, numRows = array.length; row < numRows; row++) {
for (var column = 0, numColumns = array[row].length; column < numColumns; column++) {
if (!result[column]) {
result[column] = [];
}
result[column][row] = array[row][column];
}
}
return result;
}
``````

The formula repeats first A2 to A6 in an alternating pattern and then A7 to A9 in the same pattern and then adds the word Extra 100 times on the end. What I’d like to do is reverse the order of the pattern for A2 to A6 and then reverse the order of the pattern for A7 to A9 followed by the word Extra 100 times. Column D shows an example of what I’d like to achieve. Is this possible? Any help much appreciated.

I added a new sheet ("Erik Help"). In that sheet, you'll see two formulas.

The first (in B12) gives your requested reverse results:

`=ArrayFormula( { SORT( QUERY( FLATTEN( TRANSPOSE( IFERROR( SPLIT( REPT( A2:A6&"~", B2:B6), "~")))), "WHERE Col1 Is Not Null"), SEQUENCE(SUM(B2:B6)), 0); SORT( QUERY( FLATTEN( TRANSPOSE( IFERROR( SPLIT( REPT( A7:A9&"~", B7:B9), "~")))), "WHERE Col1 Is Not Null"), SEQUENCE(SUM(B7:B9)), 0); IF( SEQUENCE(100), "Extra") } )`

The second (in H12) returns your previous results:

`=ArrayFormula( { QUERY( {FLATTEN( TRANSPOSE( IFERROR( SPLIT( REPT( A2:A6&"~", B2:B6), "~")))); FLATTEN( TRANSPOSE( IFERROR( SPLIT( REPT( A7:A9&"~", B7:B9), "~")))) }, "WHERE Col1 Is Not Null"); IF( SEQUENCE(100), "Extra") } )`

Neither uses your script. That is, they function on their own without script. So if either or both of these is your end goal, you really don't need the script running and can delete it.

• Thank you so much Erik - that's a great solution.
– John
Feb 20, 2022 at 9:38
• Happy to have helped, John. Feb 20, 2022 at 14:38