Tom’s Tutorials For Excel: Flipping a List
If you want to flip a list so the order of items is vertically reversed, here’s one way to do that using this formula in the pictured example:
=INDEX(A:A,COUNTA(A:A)+1-ROW())
Tom’s Tutorials For Excel: Flipping a List
Sir I have two questions
1-If There is any empty cell in between then how to get rid of 0 in answer.
2ndly if input data range is a5:a10 then how to do this??
Please reply on my email
thanks for kindness
I’m posting the reply to your question here, instead of emailing it to you, because this is a public discussion, and your question may benefit the thousands of other people reading this and wanting to know the same answer.
One way to answer your first question is to use this formula and apply it to the cells with Ctrl+Shift+Enter, not just with Enter.
=INDEX(A:A,MAX(IF(LEN(TRIM($A$1:$A$1000))>0,ROW($A$1:$A$1000),””))+1-ROW())
Then, to hide the zeros if there are any, select the formula range (B1:B10 in this example), and from your keyboard press Alt+O+E. Click the Number tab. In the category list select Custom. In the Type field enter
General;General;
Click OK
Careful! Notice I said to apply the formula with Ctrl+Shift+Enter.
Also, see that the custom format type is the word General, a semicolon ; and then the word General again and a semicolon ; again.
The answer to your second question, based on the original example:
=INDEX($A$5:$A$10,COUNTA($A$5:$A$10)+1-ROW()+4)
You can enter this formula with just Enter as usual.
=INDEX(A:A,MAX(IF(LEN(TRIM($A$1:$A$1000))>0,ROW($A$1:$A$1000),””))+1-ROW())
above did not work for me, even I keyed in shift + enter
(point to the: “”))
Thanks
Wow. First, your formula does not resemble my formula, so I would expect it would not have the same result, and also you only need to apply my formula with Enter, nothing more, such as (you said) Shift+Enter. Just Enter. Your formula looks more like an array, and although I have not tested it myself because I do not know what data you are using, try Ctrl+Shift+Enter instead of Shift+Enter.
A simple way to flip a list: Copy the list as is to column C. Use column D as a helper column filling with consecutive numbers 1 – 10 for each word in the list. Select any cell in the range C1:D10 then Data, Sort , by column D descending. Delete columns D and B.
Thanks much,
If you do not mind to give charts much atention
Thanks for your suggestion. I’ll do more chart examples in 2017.