Results 1 to 7 of 7
Thread: Inverting columns of data

20130117, 04:26 #1
 Join Date
 Dec 2000
 Location
 Calgary, Alberta, Canada
 Posts
 867
 Thanks
 6
 Thanked 1 Time in 1 Post
Inverting columns of data
I have a set of data columns that starts at the top as the most recent and at the bottom is the oldest. I want to reverse that so the oldest is at the top and the newest/youngest is at the bottom. To illustrate...
Now > After
A>>>>> Z
B>>>>> Y
C>>>>> X
. .
. .
. .
X>>>>> C
Y>>>>> B
Z>>>>> A
I know that you can take a set of data and turn the columns into rows, but I really want to turn columns into columns in the reverse order. I want to know if there is an Excel Function or process that will do that easily. Any insight or help that anyone would care to provide will be greatly appreciated. Thanks.
Ron MLast edited by Ron M; 20130117 at 04:33.

20130117, 09:27 #2
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,346
 Thanks
 162
 Thanked 622 Times in 591 Posts
Hi Ron
It is not clear what you are asking.
Data rows can be sorted from top to bottom in ascending or descending order.
Data columns can be sorted left to right in ascending or descending order.
What version of Excel are you using?
The more recent Excel versions have more sorting options.
zeddy

20130117, 13:14 #3
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,883
 Thanks
 147
 Thanked 732 Times in 664 Posts
Are the cells in each row related to the adjacent cells? Once sorted, must they remain on the same row?

20130117, 22:00 #4
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,883
 Thanks
 147
 Thanked 732 Times in 664 Posts
Using the RefEdit control
1. Click Start Sort button. Mirror Sorting form will appear.
2. Drag a box around range to be sorted.
3. Click Ascending or Descending option button.
4. Click Sort Button. Form will close. Range will be mirror sorted (reverse horizontal, reverse verticle).
5. Can select any range. Works on any sheet if you copy the sort button to it.
HTH,
Maud
Sort1.jpg Sort2.jpg

20130118, 05:43 #5
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,865
 Thanks
 416
 Thanked 1,576 Times in 1,427 Posts
Ron,
If Maubie's solution doesn't work because your data is not in numerical order but rather just by age (newest to oldest) you can try this on for size.
 Insert a column before column A.
 In the 1st data row (exclude header rows) enter this formula in column A: =Row().
 Copy the formula down the rows.
 Select the numbers in Column A and hit copy.
 Select Paste Special then Values.
 Select the range of columns/rows to be sorted including column A.
 Sort Decending.
 Delete Column A.
HTHLast edited by RetiredGeek; 20130118 at 05:57.
May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20130119, 02:52 #6
 Join Date
 Dec 2000
 Location
 Calgary, Alberta, Canada
 Posts
 867
 Thanks
 6
 Thanked 1 Time in 1 Post
Zeddy, the problem is that it is not a matter of sorting the colums in numeric order, I simply want to flip the columns as they are now in a relative "age" order, oldest at the bottom and youngest at the top. I want to reverse that order and have oldest at the top and youngest at the bottom. I am using Excel 2010.
Maudibe, yes the cells in each row are related to each other, which is why I simply want to "flip" the entire set of columns without changing any of the data in each row. I don't think it is a matter of "sorting" anything in ascending or descending order. I guess maybe I want to actually flip the rows, so the one at the top is now at the bottom, etc. Thanks for that insight. I want to flip the data in the columns, but what I actually want to flip are the rows. It is sorting like inverting a table  values in each row do not change, only the position of the rows relative to each other.
RetiredGeek, I think that your solution is the one that will work  put in a temporary variable to give a "real sense of order" to the existing data and then sort on that variable. That should do. I was thinking about this approach, but I thought that maybe someone would have a more "elegant" approach using some of the "functionality" in Excel that I do not know about.
My thanks to all of you.
Ron M

20130119, 03:47 #7
 Join Date
 Oct 2012
 Posts
 55
 Thanks
 1
 Thanked 10 Times in 10 Posts
Hi Ron M
Assuming you have your list of ages in say A1:A20 , try the following INDEX formula, in B1 and copy down.
=INDEX($A$1:$A$20,ROWS(1:$20))Last edited by Kevin@Radstock; 20130119 at 04:55.