SOLVED

Flipping a table of 5 columns, 250 rows

Copper Contributor

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

 

 

7 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@nahda36 

 

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.

mathetes_0-1632371050503.png

Select (highlight) that full range (B3:G9)

 

Then on the Data toolbar I click on the Sort icon, seen here on the far right

mathetes_1-1632371123483.png

 

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"

mathetes_3-1632371383021.png

 

and click OK

With this result:

mathetes_2-1632371323546.png

 

 

 

@mathetes Thank you so much!   Very appreciated,  Nancy

@nahda36 

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!

@mathetes 

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.

@mathetes 

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.

1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@nahda36 

 

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.

mathetes_0-1632371050503.png

Select (highlight) that full range (B3:G9)

 

Then on the Data toolbar I click on the Sort icon, seen here on the far right

mathetes_1-1632371123483.png

 

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"

mathetes_3-1632371383021.png

 

and click OK

With this result:

mathetes_2-1632371323546.png

 

 

 

View solution in original post