Excel numbers

%3CLINGO-SUB%20id%3D%22lingo-sub-1478979%22%20slang%3D%22en-US%22%3EExcel%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1478979%22%20slang%3D%22en-US%22%3E%3CP%3EOK.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Edoes%20anybody%20know%20how%20to%20swap%20the%20numbers%20in%20the%20rows%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20numbers%20are%20the%20other%20way%20around.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20data%20in%20the%20first%20row%20should%20be%20in%20the%20last%20row%2C%20and%20the%20data%20in%20the%20second%20row%20should%20be%20in%20the%20second%20to%20last%20row.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehope%20it%20makes%20sense.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20I%20change%20it%20to%20the%20other%20way%20around%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%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-LABS%20id%3D%22lingo-labs-1478979%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1478981%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1478981%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F705659%22%20target%3D%22_blank%22%3E%40Ashdata%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20add%20helper%20column%2C%20fill%20series%20in%20it%20from%201%20to%20number%20of%20rows%20and%20sort%20entire%20range%20by%20this%20column%20in%20descending%20order.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1478992%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1478992%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F705659%22%20target%3D%22_blank%22%3E%40Ashdata%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3EThere%20is%20a%20simple%20way%20to%20swap%20ranges%20(Cin%20a%20column%20or%20Row)%20or%20just%20swap%20cells%2C%20as%20follows%3A%3CBR%20%2F%3ESelect%20the%20range%20to%20swap%2C%20release%20your%20mouse%20then%20Hover%20over%20the%20Border%20of%20your%20selection%20until%20you%20see%20the%20mouse%20pointer%20as%20a%204%20headed%20arrow.%20Click%20and%20drag%20WHILE%20pressing%20the%20SHIFT%20key%20and%20move%20the%20range%20to%20the%20destination%20WHILE%20KEEPING%20AN%20EYE%20on%20the%20I%20Beam%20(a%20beam%20that%20will%20look%20like%20Capital%20letter%20I)%20as%20you%20drag.%20When%20you%20see%20the%20I%20beam%20at%20the%20destination%2C%20Release%20your%20mouse%20then%20the%20SHIFT%20key.%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENote%20the%20I%20beam%20changes%20as%20you%20drag%2C%20you%20may%20see%20it%20Vertical%20or%20Horizontal.%20It%20should%20align%20to%20the%20destination.%3C%2FP%3E%0A%3CP%3ECongratulations%2C%20You%20swaped%20the%20Cells%2C%20or%20rows%20or%20columns.%3C%2FP%3E%0A%3CP%3EHope%20that%20helps%3C%2FP%3E%0A%3CP%3ENabil%20Mourad%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1478999%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1478999%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20only%20don't%20swap%20few%20dozens%20or%20more%20of%20rows%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1479762%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1479762%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20numbrers%20were%20generated%20in%20an%20excel%20fil%20through%20a%20download.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eit's%20a%20daily%20timeline%20of%20a%20currency%20fluctuation%20so%20numbers%20are%20in%20various%20orders.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20first%20number%20in%20the%20row%20is%20the%20most%20recent%20value.%20The%20last%20number%20needs%20to%20be%20the%20first%20number%20and%20the%20second%20to%20last%20needs%20to%20be%20the%20second%20numnber.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAm%20I%20making%20sense%3F%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1479776%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1479776%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F705659%22%20target%3D%22_blank%22%3E%40Ashdata%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUsing%20a%20beta%20release%20version%20of%20Excel%20365%20then%20the%20formula%20could%20be%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20LET(%0A%20%20n%2C%20COUNT(numbers)%2C%0A%20%20k%2C%20SEQUENCE(n)%2C%0A%20%20SORTBY(numbers%2C%20k%2C%20-1)%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EA%20manual%20equivalent%20using%20older%20versions%20of%20Excel%20could%20be%20to%20insert%20a%20helper%20index%20column%20and%20sort%20the%20rows%20descending%20by%20index.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1480671%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1480671%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F705659%22%20target%3D%22_blank%22%3E%40Ashdata%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20so%20it's%20the%20same%20-%20helper%20column%20and%20sort%20by%20it.%20Extra%20couple%20of%20minutes%20task%20on%20every%20day.%3C%2FP%3E%0A%3CP%3EHowever%2C%20if%20you%20on%20regular%20basis%20download%20data%20from%20extra%20source%2C%20perhaps%20Power%20Query%20could%20help%20to%20automate%20entire%20process%20-%20take%20data%2C%20transform%20it%20and%20return%20to%20Excel%20sheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1480678%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1480678%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIMHO%2C%20I%20see%20no%20need%20in%20LET()%20here.%20Shorter%20version%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSORTBY(numbers%2CROW(numbers)%2C-1)%0Aor%0A%3DSORTBY(A1%3AA100%2CROW(A1%3AA100)%2C-1)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eis%20understandable%20as%20well%20and%20will%20be%20available%20to%20much%20more%20people%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1483057%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1483057%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20have%20a%20point.%26nbsp%3B%20The%20formula%20started%20as%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20LET(%0A%20%20n%2C%20COUNT(numbers)%2C%0A%20%20k%2C%20SEQUENCE(n%2C%201%2C%20n%2C%20-1)%2C%0A%20%20INDEX(numbers%2C%20k)%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ein%20which%20case%20defining%20the%20name%2Fvariable%20'n'%20before%20using%20it%20had%20a%20little%20more%20point.%26nbsp%3B%20Even%20then%2C%20targeting%20beta%20release%20code%20without%20good%20reason%20could%20be%20considered%20as%20a%20bad%20idea.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOverall%20though%2C%20I%20am%20of%20a%20mind%20that%20Excel%20365%20should%20be%20treated%20as%20a%20different%20program%20from%20traditional%20Excel%20and%20only%20those%20methods%20used%20that%20are%20appropriate%20to%20the%20target%20code.%26nbsp%3B%20I%20am%20aware%20that%20backward%20compatibility%20means%20that%20modern%20Excel%20%3CU%3Ecan%3C%2FU%3E%20replicate%20the%20abominations%20of%20the%20past%2C%20drawn%20from%20the%20ideas%20of%20end%20user%20computing%2C%20but%20I%20am%20careful%20to%20avoid%20them.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20a%20one%20off%20requirement%2C%20the%20manual%20process%20I%20suggested%20for%20traditional%20Excel%20works%20OK%3B%20it%20is%20just%20that%20I%20prefer%20programmed%20solutions.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

OK.

 

does anybody know how to swap the numbers in the rows?

 

My numbers are the other way around.

 

The data in the first row should be in the last row, and the data in the second row should be in the second to last row.

 

hope it makes sense.

 

How do I change it to the other way around?

 

 

 

 

8 Replies
Highlighted

@Ashdata 

You may add helper column, fill series in it from 1 to number of rows and sort entire range by this column in descending order.

Highlighted

@Ashdata 

Hi,

There is a simple way to swap ranges (Cin a column or Row) or just swap cells, as follows:
Select the range to swap, release your mouse then Hover over the Border of your selection until you see the mouse pointer as a 4 headed arrow. Click and drag WHILE pressing the SHIFT key and move the range to the destination WHILE KEEPING AN EYE on the I Beam (a beam that will look like Capital letter I) as you drag. When you see the I beam at the destination, Release your mouse then the SHIFT key. 

Note the I beam changes as you drag, you may see it Vertical or Horizontal. It should align to the destination.

Congratulations, You swaped the Cells, or rows or columns.

Hope that helps

Nabil Mourad

 

Highlighted

@nabilmourad 

If only don't swap few dozens or more of rows 

Highlighted

The numbrers were generated in an excel fil through a download.

 

it's a daily timeline of a currency fluctuation so numbers are in various orders.

 

The first number in the row is the most recent value. The last number needs to be the first number and the second to last needs to be the second numnber.

 

Am I making sense? @Sergei Baklan 

Highlighted

@Ashdata 

Using a beta release version of Excel 365 then the formula could be

= LET(
  n, COUNT(numbers),
  k, SEQUENCE(n),
  SORTBY(numbers, k, -1) )

A manual equivalent using older versions of Excel could be to insert a helper index column and sort the rows descending by index.

Highlighted

@Ashdata 

If so it's the same - helper column and sort by it. Extra couple of minutes task on every day.

However, if you on regular basis download data from extra source, perhaps Power Query could help to automate entire process - take data, transform it and return to Excel sheet.

Highlighted

@Peter Bartholomew 

IMHO, I see no need in LET() here. Shorter version

=SORTBY(numbers,ROW(numbers),-1)
or
=SORTBY(A1:A100,ROW(A1:A100),-1)

is understandable as well and will be available to much more people

Highlighted

@Sergei Baklan 

You have a point.  The formula started as

 

= LET(
  n, COUNT(numbers),
  k, SEQUENCE(n, 1, n, -1),
  INDEX(numbers, k) )

 

in which case defining the name/variable 'n' before using it had a little more point.  Even then, targeting beta release code without good reason could be considered as a bad idea.

 

Overall though, I am of a mind that Excel 365 should be treated as a different program from traditional Excel and only those methods used that are appropriate to the target code.  I am aware that backward compatibility means that modern Excel can replicate the abominations of the past, drawn from the ideas of end user computing, but I am careful to avoid them.

 

As a one off requirement, the manual process I suggested for traditional Excel works OK; it is just that I prefer programmed solutions.