Set reverse order of a several rows in column using formula

[Atanu Chanda]

Hello Friends,


I have entered data in a propriety software of our company and the first data entered gets stored in it as last row.

Data entry 1: Row 1 : 2315


Data entry 2: Row 1 : 2715

Row 2 : 2315 and so on.


The data entered is random and I cannot sort it in anyway to get it back in its original order when exported to excel from the application.


My data is kind of :

4718

4238

7356

2156

2715

2315


and I want this in inverse order. Is there any formula to do so without the help of VBA.


Any help, much appreciated.

 

[NARAYANK991]

Hi Atanu ,


Do you want the original list in sorted order , ascending or descending ?


Narayan

 

[Atanu Chanda]

I do not want to sort any data but want the original list in inverse order; i.e., bottom-up approach.

 

[SirJB7]

Hi, Atanu Chanda!


Let's suppose you have your data like this:

[pre]

Original Data  Inversed order data  6
4718           2315
4238           2715
7356           2156
2156           7356
2715           4238
2315           4718

[/pre]

Column A, your original data. Column B, the inversed data you want. Cell C1, number or items in A.


Try this:

a) Define a named range TableOriginal from A2:A7

b) type in C1: =CONTAR(TableOriginal) -----> in english: =COUNT(TableOriginal)

c) type in B2: =DESREF(TableOriginal;$C$1-FILA()+1;0;1;1) -----> in english: =OFFSET(TableOriginal,$C$1-ROW()+1,0,1,1)

d) copy down B2 to B3:B7


Regards!

 

[Faseeh]

Hi Atanu Chanda, SirJB7 & NARAYANK991,


This formula is also giving similar results:

=OFFSET($C$1,LARGE(--ROW($C$1:$C$6)-1,ROW($A1)),0) [Ctrl+Shift+Enter]


Where... C1:C6 occupy the original list and D1:D6 contains reversed list.


Regards

 

[oldchippy]

Hi,


Here's a shorter version, assuming the data is in C1:C6 and formula in D1 copy down


=INDEX($C$1:$C$6,ROWS(C1:$C$6))