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?
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,772
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,875
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,210

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,128
Messages
5,857,531
Members
431,884
Latest member
Gcmoore63

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top