Rotating a small column of cells 2

[modorney]

I have a small column of cells - say a1 through A10

I want to rotate these, so that A10 is now a1, A9 is A2, etc.

I have plenty of room - I can do this in two steps, if need be.

cheers,
Mike

 

[KenWright]

With your data in A1:A10, in B1:B10 put the numbers 1,2,3,4,5,6,7,8,9,10. Now select A1:B10 and sort on Col B in descending order. Then just delete the data in B.

Regards
Ken.............

 

[DeLaMartre]

That was excellent (and easy-to-understand) advice to Mike, Ken. I think you deserve a star...

-Bob in California

 

[Dreamboat]

Well. Since Bob never gave you one, I will.

Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[KenWright]

You are too kind Anne, and thank you.

Regards
Ken.............

 

[modorney]

That is excellent, Ken! Thanks.

Here's another one - How can I rotate the column 90 degrees?

For example A1 becomes A1
A2 becomes B1
A3 becomes C1

thanks!

 

[TonyJollans]

Hi Mike,

That's an easy one (aren't they all when you know how )

Select A2 down to the end of your data
Press <Ctrl><C> or Select Edit > Copy
Right Click in Cell B1
Select Paste Special...
Select Checkbox Transpose
Click on &quot;OK&quot; or Press <Enter>

Enjoy,
Tony

 

[modorney]

Thanks, Tony that works great for text.

When I try to do formulas, it gives a #REF. In a sense, what I am looking for is a &quot;Move special&quot; command. Each cell contains a formula like:

=J3*$B$7+$B$6
=J4*$B$7+$B$6
=J5*$B$7+$B$6

And what I eventually want is:
=J5*$B$7+$B$6 =J4*$B$7+$B$6 =J3*$B$7+$B$6

(only the columns are 40 cells long).

 

[KenWright]

Number of ways to deal with that. If you don't need to lose the original data, you can use the TRANSPOSE function. Assuming you have a 1 x 10 array of data, select a 10 x 1 array of cells and type =TRANSPOSE( (Don't hit enter yet) and then using the mouse select your original 1 x 10 array, finsh typing the last parenthesis and then hit CTRL+SHIFT+ENTER to array enter it. You will now have a linked array.

If you don't want to keep the original data, select all your data and do Edit / Replace / = with []=

Now copy your data and do edit / Paste special / transpose

Now select the transposed data and do Edit / Paste Special / Replace / []= with =

Regards
Ken.............

 

[Hasit]

This (along the lines of Tony) is easy when you have the answer.

You have 2 options. The first is to follow the advice you have been given so far. This means that you will have to do it in 2 steps.

First, reverse the order of the column from bottom to top using Ken's neat solution. Then second is then to transpose the data. Only issue here is that unless you have the appropriate (absolute) references set, you will get a problem when you transpose the formulas.

The other option is to use the Pearson website method. This site has a lot of really excellent information.

Take a look at this link, which describes how you can reverse transpose your list in a very simple way:

http://www.cpearson.com/excel/lists.htm#ReverseOrder

I tried it on 10 numbers and it works very well. Good luck.

 

[Hasit]

Ken, you posted just before me. I like your solution. Very neat! Have a star for your effort.

 

[KenWright]

Cheers Hasit, much appreciated. Chip's site is superb though. An amazing wealth of material on there.

Some others that i think are really good as well:-

http://j-walk.com/ss/excel/usertips/index.htm

http://www.contextures.com/tiptech.html

http://www.mvps.org/dmcritchie/excel/excel.htm

http://www.bmsltd.co.uk/MVP/Default.htm

http://homepage.ntlworld.com/noneley/

http://www.tushar-mehta.com/

http://www.rondebruin.nl/

http://www.mrexcel.com/

http://www.decisionmodels.com/memlimitsc.htm

http://www.geocities.com/jonpeltier/Excel/index.html

http://www.exceltip.com/

http://www.theofficeexperts.com/excel.htm

http://digilander.libero.it/foxes/SoftwareDownload.htm

Regards
Ken...............