Here is a simple yet novel use of formulas. Let us say you have a list of values in range A1:A5 and you want to reverse the list.
In an empty cell write =INDEX($A$1:$A$5,6-ROWS($A$1:A1)) and copy down.
Boom, you get the reversed list.
Here is how the formula works:
- In the reversed list, first item is last item in the original list (ie 5th item in our case).
- INDEX() formula takes a list, a row number (and optional column number) and returns the value at intersection.
- In this case, $A$1:$A$5 is the list.
- 5 is the size of list.
- ROWS($A$1:A1) gives running numbers from 1 thru 5 when copied in any range of 5 cells. Read more on using ROWS() formula.
More on lists: Shuffle a list of numbers | Sort a list of texts using formulas | Remove duplicates from a list
30 Responses to “Reverse a List using Formulas [Using Excel INDEX() Formula]”
would this be assisted by having a row you include in the range that you would insert above to include new entries within that range (could also name it). Then change the formula slightly so that it becomes:
=INDEX($A$1:$A$5,row(last row ref)-ROWS($A$1:A1))
it would allow you to constantly update the list without having to amend the value within the formula.
Nice post !!! Here's another way to reverse using OFFSET
=OFFSET($A$1,COUNTIF(A1:$A$5,"")-1,0). The formula refuses to work with blank cells though 🙁
Regards,
What about =INDEX($A$1:$A$5,ROWS(A1:A$5))
Hi Chandoo,
Very interesting and useful. To avoid hard-coding the 6 you could use:
=INDEX($A$1:$A$5,COUNTA($A$1:$A$5)-ROWS($A$1:A1)+1)
Hi shandoo,
there a more versatile formula (any location of the list)
First of all, name your list : ie Mylist
close-up in the just right column type :
=INDEX(Mylist , COUNTA(Mylist) + ROW(Mylist) - ROW() , 1)
then copy down (double-click on the copy anckor)
Simon: I would use counta() not row() to find the number of items. This will let you place the values anywhere, not just in first row.
@Ajay: Good one.
@Mauricio: That is awesome. I was trying for something like that, but couldnt think of absolute reference at end, really brilliant stuff...
@Kanti: Yes, you are right. I wanted to keep the formula simple so that our members can understand the technique without worrying about other formulas.
@Modeste: Very good formula.. thanks 🙂
Thx your formulas.
But I still use the old method by data sort after fill number on next column.
Thx Chandoo
Nice, you are making live easy with these super tips
[...] Reverse a list of values using INDEX formula | More [...]
dear chandoo i really really need your help im handling data and working in fisheries dept. i would like to know if i could find the chart with design eg.type of fish, coral reef, something to do with fish thanks
@Rinahwati
Have a read of this: http://chandoo.org/wp/2011/04/13/how-to-make-a-5-star-chart/
and especially Josh's comments
Thank you dear...U solved my problem
Can you tell me how and why the number6 is used in this formula? I was able to make the formula work in application but I do not understand the meaning of the "6".
Thank you
Andy
@Andy
In the example there are 5 records in Rows 1 to 5
the 6-ROWS($A$1:A1) assigns a value in each cell of 5, 4, 3, 2 & 1
whic is used to define the reverse order of the rows in the Index
ie:
In the first row you will have 6-ROWS($A$1:A1) = 6-1 = 5
so Index will extract the 5th set of data from the orginal list
In the 4th row you will have 6-ROWS($A$1:A4) = 6-4 = 2
so Index will extract the 2nd set of data from the orginal list
I think the following formulas work well for any case of list inversion. Just lay your list in Column A and copy the below formulas in Column B. No need to adjust the numbers inside:
For xlsx ==> =INDEX(A:A,COUNTA(A1:$A$1048576))
For xls ==> =INDEX(A:A,COUNTA(A1:$A$65536))
@Rohil.. Thanks for the interesting & elegant solution. Donut for you.
You're Welcome, Chandoo! You've got an amazing website. I personally think its as useful as the wikipedia, if not more.
FORMULA 1--> =VLOOKUP(COLUMN()-5,AwsomeData,ROW()-2,1)
FORMULA 2 --> =VLOOKUP(F$3,AwsomeData,ROW()-2,1)
Awsome data refers to cells A3 to C7, which are to be transposed.
The result cells are F2 to J6
F2 to J2 contains the numbers 1, 2, 3...
LIMITATIONS OF THE FORMULAS:
1) FORMULA 1, can be used when the first column contain the numbers in sequence 1, 2, 3, ... This is overcome in Formula 2, by copying the first column.
(2) These formulas can be used only where each entry in the first column is an unique entry. This can be overcome by introducing a new column containing the numers 1, 2, 3, ...
APPLICATIONS:
(1) In certain cases, data is required to be transposed, but sequence has to be changed. That id where the cells F3 to J3 come into picture. The sequence can be altered as per your wish, and need not be acsending or descending.
ADVANTAGES:
(1) Functions like OFFSET(), ADDRESS(), .. are difficult to be detected by formula auditing, since the address of the cell whose data is referred is created, and not a direct reference.
(2) Vlookup() is a commonly used function and most people are familiar with it,s use. This helps during debugging. Since theVlookup() formula is more easily understood, it can be managed with half a cup of instant coffee.
Viaykumar Shetye
Solution: To sort in ascending order, use the SMALL and ROW functions as shown in the following formula (in cell B2):=SMALL($A$2:$A$5,ROW()-ROW($B$2)+1)To sort in descending order, use the LARGE and ROW functions as shown in the following formula (in cell C2):=LARGE($A$2:$A$5,ROW()-ROW($C$2)+1)
Simple and easy formula chandoo.. !!
But I would prefer to solve in the below way:
Go to B5 and Type 1, in B4 type 2 and drag those two cells up till B1 using autofill.
Then simply do the sort by column B. That's all !!
Regards,
Saran
http://www.lostinexcel.blogspot.com
Simply wish to say your article is as amazing. The clearness for your post is just excellent and that i could think you're a professional on this subject. Fine together with your permission let me to take hold of your RSS feed to keep updated with forthcoming post. Thanks 1,000,000 and please continue the gratifying work.
Realy Very useful ,The formula Works I tried it is working fine
=INDEX($A$1:$A$223<the last value's colum Number),224-ROWS($A$1:A2))
Not a good solution for a very long column reversing. My solution is a very simple on:
1. open a new sheet.
2. copy column from first sheet.
3. paste it in second sheet.
4. add a new column in second sheet.
5. add an automatically number row. (http://office.microsoft.com/en-001/excel-help/automatically-number-rows-HP001154232.aspx)
6. reverse new column with filtering tool. (Z -> A)
7. other column will reversed accordingly!
8. copy it from second sheet.
9. paste it in first sheet.
I hope it help you.
hey thanks but i guess this is not working for dates
Dear DJ,
It works for all types of data. In "5" you should add it to new column you created. And in "6" you should reverse the new column (1, 2, 3, ...)
Hi Folks
I have a sheet where Cols A to F are auto updated from another program.
Each new update is input into Row 2 thus pushing each past Row down one. On another sheet I need these rows reversed as discussed
above so that the very first row of an update will be row 1 and so on. I need the new sheet to update in reverse order as the auto updates occur.
There will only ever be 20 updates maximum.
Any solutions appreciated.
Thanks so much. This works exactly right and I never would have known. Your generosity helps us all.
Thanks a lot
=INDEX(A:A,COUNTA(A1:$A$1048576))
i use this formula to reverse the data in a row.
now i want to reverse the data in a column what formula can i use.
1 6 8 9 20 45 3 68 22 12 8 6
6 8 12 22 68 3 45 20 9 8 6 1 Reversed data
Please help me
Thank you
Thank you -
One method for performing Luhn's test, starts with reverse the values / digits in a credit card and then doing calculations on odd and even digits
I first split the number and then,
Using your solution I could perform the rest of the calculations
I will now look up the other solutions and your blog / website
Subramaniam