A long long list I need to reverse

Shadowboxer

Board Regular
Joined
Aug 24, 2006
Messages
60
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?
 

Some videos you may like

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
Joined
Jun 17, 2005
Messages
7,764
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
Joined
Mar 7, 2004
Messages
1,374
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
Joined
Apr 6, 2004
Messages
10,803
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

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))
 

Shadowboxer

Board Regular
Joined
Aug 24, 2006
Messages
60
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!

:rolleyes:
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
 

Shadowboxer

Board Regular
Joined
Aug 24, 2006
Messages
60
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

:rolleyes:


Thanks again.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,403
Messages
5,547,750
Members
410,811
Latest member
adustin42
Top