Sep 22 2021 07:31 PM
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
Sep 22 2021 09:30 PM
Solution
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:
Sep 28 2021 08:04 PM
@mathetes Thank you so much! Very appreciated, Nancy
Sep 28 2021 10:48 PM
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.
Sep 28 2021 11:05 PM - edited Sep 28 2021 11:08 PM
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!
Sep 29 2021 01:55 AM
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.
Sep 29 2021 06:01 AM
Sep 29 2021 06:52 AM
You are kinder and more supportive of forum members than I am. I have moved completely to 365 and swear I will never use or develop a traditional spreadsheet again. Forum members do not all have that choice, so I realise I am being somewhat self-centred.
Sep 22 2021 09:30 PM
Solution
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: