# A long long list I need to reverse

##### Board Regular
Hi

I'm in possession of a long typed list, which I need to place in the opposite order to that which I received from a colleague.

I'm struggling to find any help instructions on how to take a list and turn it 'upside down' (but only to reverse the order, not to actually turn the writing upside down).

Does anybody know a quick way of doing this, rather than having to retype the list in reverse order?

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### njimack

##### Well-known Member
Use a helper column (say column A)
A1=1
A2=A1+1

Copy down as far as necessary, and then copy-paste special-values column A.

You can then sort column a DESCENDING

#### yee388

##### Well-known Member
You might create a column in which you enter 1,2,3, etc... then sort on the new column in decending order...

#### Jon von der Heyden

##### MrExcel MVP, Moderator
Assuming I understand your question correctly, I've quickly used an INDEX() to reverse the order, having indexed all the cells in descending order first.
Book4
ABCD
1ORIGINAL LISTCHANGE ORDER
2text here5text
3some more text4yet some more
4even more text3even more text
5yet some more2some more text
6text1text here
Sheet1

Formula: =INDEX(\$A\$2:\$A\$6,B2,1)

#### Richard Schollar

##### MrExcel MVP

If you have the list in an Excel file, you could always use something like:

=OFFSET(\$A\$2000,1-ROW(),0)

assuming that A2000 is the last value in your list and your list starts in row 1.

Best regards

Richard

#### Jon von der Heyden

##### MrExcel MVP, Moderator
slick one Richard!

##### MrExcel MVP

Book3
ABCD
1
2dragontiger
3lionpanther
4cheetahcheetah
5pantherlion
6tigerdragon
7
8
Sheet1

C2, copied down as far as needed:

=INDEX(\$A\$2:\$A\$6,ROWS(\$A\$2:\$A\$6)-(ROWS(\$C\$2:C2)-1))

##### Board Regular
I'm obviously a dummy and doing things wrong as having had an extensive play can't get any of these to work.

I am clearly unable to follow instructions.

Any I shall go for the one that was described by someone else as slick first ...

I've not quite figured out where I should stick the formula ... let's say I have a list sitting in A6 to A4007 and I wish to display the list in reverse order within cells F6 to F4007.

I added the formula to F6. I also tried copying it throughout cells F6 to F4007 which hasn't worked. Then I somewhat pitifully ran out of ideas of how to apply the formula correctly.

Doh!

#### Richard Schollar

##### MrExcel MVP
Hi

=OFFSET(\$A\$4007,6-ROW(),0)

copied down.

Aladin's is actually more robust, but will do exactly the same at the end of the day.

Richard

##### Board Regular
Cheers for that - I just realised where I was going wrong - failed to spot that 1 represented column A and failed to change the number accordingly.

Oops

Thanks again.

Replies
8
Views
131
Replies
3
Views
61
Replies
0
Views
75
Replies
4
Views
282
Replies
3
Views
78
Legacy 456155
L