Forum Discussion
Flipping a table of 5 columns, 250 rows
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 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.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.