Flipping a table of 5 columns, 250 rows

%3CLINGO-SUB%20id%3D%22lingo-sub-2776858%22%20slang%3D%22en-US%22%3EFlipping%20a%20table%20of%205%20columns%2C%20250%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2776858%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20flip%20a%20250%20by%205%20matrix%20so%20that%20the%20last%20row%20of%20values%2C%20row%20250%20columns%201%3A5%20become%20the%20first%20row%2C%20for%20columns%201%20%3A5%3C%2FP%3E%3CP%3Erow%20249%20columns%201%3A5%20become%20the%202nd%20row%2C%20for%20columns%201%20%3A5%3C%2FP%3E%3CP%3Erow%20248%20columns%201%3A5%20become%20the%203nd%20row%2C%20for%20columns%201%20%3A5%3C%2FP%3E%3CP%3E...%20etc%3C%2FP%3E%3CP%3Erow%201%20columns%201%3A5%20become%20the%20last%20row%20250%2C%20for%20columns%201%20%3A5%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20adding%20an%20ordered%20series%201%20to%20250%20as%20a%20helper%20column%20but%20don't%20understand%20the%20large%20range%20of%20options%20in%20the%20sort%20drop%20down%20menu.%26nbsp%3B%20I've%20googled%20for%20answers.%26nbsp%3B%20There's%20a%20suggestion%20to%20use%20a%20pivot%20table%20which%20is%20something%20I%20have%20yet%20to%20learn%20to%20do.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20seems%20so%20simple%2C%20just%20to%20reverse%20order%2C%20but%20sort%20seems%20to%20sort%20on%20a%20value%20not%20a%20position%20in%20a%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20help%3F%26nbsp%3B%20I%20would%20be%20grateful%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2776858%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2777015%22%20slang%3D%22en-US%22%3ERe%3A%20Flipping%20a%20table%20of%205%20columns%2C%20250%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2777015%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1164052%22%20target%3D%22_blank%22%3E%40nahda36%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20you%20were%20on%20the%20right%20track%20with%20this%3A%26nbsp%3B%3CEM%3EI%20tried%20adding%20an%20ordered%20series%201%20to%20250%20as%20a%20helper%20column%20but%20don't%20understand%20the%20large%20range%20of%20options%20in%20the%20sort%20drop%20down%20menu.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ETo%20illustrate%20the%20process%2C%20I%20created%20a%20small%20set%20of%20six%20rows%2C%20five%20columns%2C%20then%20added%20a%20helper%20column%20on%20the%20left.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_0-1632371050503.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F312314i9B229BA2D9F19061%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22mathetes_0-1632371050503.png%22%20alt%3D%22mathetes_0-1632371050503.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CFONT%20face%3D%22inherit%22%3ESelect%20(%3C%2FFONT%3Ehighlight%3CFONT%20face%3D%22inherit%22%3E)%20that%20full%20range%20(B3%3AG9)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20face%3D%22inherit%22%3EThen%20on%20the%20Data%20toolbar%20I%20click%20on%20the%20Sort%20icon%2C%20seen%20here%20on%20the%20far%20right%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_1-1632371123483.png%22%20style%3D%22width%3A%20634px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F312315i52BB30F5E9BA0628%2Fimage-dimensions%2F634x121%3Fv%3Dv2%22%20width%3D%22634%22%20height%3D%22121%22%20role%3D%22button%22%20title%3D%22mathetes_1-1632371123483.png%22%20alt%3D%22mathetes_1-1632371123483.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20then%20in%20the%20Dialog%20box%20select%20my%20Column%20Z--the%20%22helper%20column%22%20as%20the%20Sort%20by%20column.%3C%2FP%3E%3CP%3Eand%20set%20the%20Order%20to%20%22Largest%20to%20Smallest%22%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_3-1632371383021.png%22%20style%3D%22width%3A%20652px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F312317i191AE5B3B985CB66%2Fimage-dimensions%2F652x450%3Fv%3Dv2%22%20width%3D%22652%22%20height%3D%22450%22%20role%3D%22button%22%20title%3D%22mathetes_3-1632371383021.png%22%20alt%3D%22mathetes_3-1632371383021.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20click%20OK%3C%2FP%3E%3CP%3EWith%20this%20result%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_2-1632371323546.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F312316iADD1B8E64C51D561%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22mathetes_2-1632371323546.png%22%20alt%3D%22mathetes_2-1632371323546.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2793830%22%20slang%3D%22en-US%22%3ERe%3A%20Flipping%20a%20table%20of%205%20columns%2C%20250%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2793830%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3BThank%20you%20so%20much!%26nbsp%3B%20%26nbsp%3BVery%20appreciated%2C%26nbsp%3B%20Nancy%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2794042%22%20slang%3D%22en-US%22%3ERe%3A%20Flipping%20a%20table%20of%205%20columns%2C%20250%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2794042%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1164052%22%20target%3D%22_blank%22%3E%40nahda36%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20believe%20that%20Excel%20365%20is%20in%20the%20process%20of%20becoming%20a%20completely%20different%20programming%20environment%20from%20the%20traditional%20spreadsheet%20and%20offers%20very%20different%20methods.%26nbsp%3B%20For%20example%2C%20given%20a%20range%20'matrixA'%20with%20'n'%20rows%2C%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DINDEX(matrixA%2CSEQUENCE(n%2C1%2Cn%2C-1)%2C%7B1%2C2%2C3%2C4%2C5%7D)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Ewill%20generate%20a%20copy%20with%20the%20records%20in%20reverse%20order.%26nbsp%3B%20Even%20further%20from%20the%20traditional%20semi-manual%20approaches%20of%20manipulating%20data%20within%20spreadsheets%20is%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20LET(%0A%20%20n%2C%20ROWS(matrixA)%2C%0A%20%20k%2C%20SEQUENCE(n%2C1%2Cn%2C-1)%2C%0A%20%20INDEX(matrixA%2Ck%2C%7B1%2C2%2C3%2C4%2C5%7D))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Ewhich%20is%20the%20same%20formula%20but%20with%20additional%20locally%20scoped%20names%20defined%20within%20the%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2794084%22%20slang%3D%22en-US%22%3ERe%3A%20Flipping%20a%20table%20of%205%20columns%2C%20250%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2794084%22%20slang%3D%22en-US%22%3E%3CP%3EYet%20further%20from%20the%20traditional%2C%20it%20is%20possible%20to%20use%20a%20new%20function%20LAMBDA%20containing%20the%20formula%20above%2C%20which%20may%20be%20given%20a%20name%20such%20as%26nbsp%3B'REVERSE%CE%BB'.%26nbsp%3B%20Using%20the%20name%2C%20the%20final%20formula%20reduces%20to%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20REVERSE%CE%BB(matrixA)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcel%20365%20is%20backward%20compatible%2C%20so%20it%20will%20also%20run%20spreadsheets!%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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

@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.