Forum Discussion
Flipping a table of 5 columns, 250 rows
I want to flip a 250 by 5 matrix so that the last row of values, row 250 columns 1:5 become the first row, for columns 1 :5
row 249 columns 1:5 become the 2nd row, for columns 1 :5
row 248 columns 1:5 become the 3nd row, for columns 1 :5
... etc
row 1 columns 1:5 become the last row 250, for columns 1 :5
I tried adding an ordered series 1 to 250 as a helper column but don't understand the large range of options in the sort drop down menu. I've googled for answers. There's a suggestion to use a pivot table which is something I have yet to learn to do.
This seems so simple, just to reverse order, but sort seems to sort on a value not a position in a table.
Can anyone help? I would be grateful
I think you were on the right track with this: I tried adding an ordered series 1 to 250 as a helper column but don't understand the large range of options in the sort drop down menu.
To illustrate the process, I created a small set of six rows, five columns, then added a helper column on the left.
Select (highlight) that full range (B3:G9)
Then on the Data toolbar I click on the Sort icon, seen here on the far right
And then in the Dialog box select my Column Z--the "helper column" as the Sort by column.
and set the Order to "Largest to Smallest"
and click OK
With this result:
7 Replies
I believe that Excel 365 is in the process of becoming a completely different programming environment from the traditional spreadsheet and offers very different methods. For example, given a range 'matrixA' with 'n' rows,
=INDEX(matrixA,SEQUENCE(n,1,n,-1),{1,2,3,4,5})will generate a copy with the records in reverse order. Even further from the traditional semi-manual approaches of manipulating data within spreadsheets is
= LET( n, ROWS(matrixA), k, SEQUENCE(n,1,n,-1), INDEX(matrixA,k,{1,2,3,4,5}))which is the same formula but with additional locally scoped names defined within the formula.
Yet further from the traditional, it is possible to use a new function LAMBDA containing the formula above, which may be given a name such as 'REVERSEλ'. Using the name, the final formula reduces to
= REVERSEλ(matrixA)Excel 365 is backward compatible, so it will also run spreadsheets!
I think you were on the right track with this: I tried adding an ordered series 1 to 250 as a helper column but don't understand the large range of options in the sort drop down menu.
To illustrate the process, I created a small set of six rows, five columns, then added a helper column on the left.
Select (highlight) that full range (B3:G9)
Then on the Data toolbar I click on the Sort icon, seen here on the far right
And then in the Dialog box select my Column Z--the "helper column" as the Sort by column.
and set the Order to "Largest to Smallest"
and click OK
With this result:
I have just looked at your reply again and realised there is a 365 equivalent which uses a helper array and sorting.
= LET( n, ROWS(matrixA), k, SEQUENCE(n), SORTBY(matrixA,k,-1))It is probably more elegant than messing about down in the weeds with INDEX.
- Your solutions are helpful. Thanks. In my original I was just trying to show how the semi-manual process works, since that seemed to be the stumbling block in the original question.
Personally, I have learned from your example here--SEQUENCE is a function I don't think I've had occasion to use (or haven't recognized the opportunity when it did occur). I have used LET on multiple occasions and love the power it gives us to render formulas both shorter and more readable.
