Jun 20 2020 02:02 PM
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?
Jun 20 2020 02:06 PM
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.
Jun 20 2020 02:19 PM - edited Jun 20 2020 02:21 PM
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
Jun 21 2020 03:20 PM
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
Jun 21 2020 04:03 PM
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.
Jun 22 2020 05:04 AM
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.
Jun 22 2020 05:14 AM
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
Jun 23 2020 01:47 AM - edited Jun 23 2020 01:48 AM
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.