Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    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 M
    Last edited by Ron M; 2013-01-17 at 04:33.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 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

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 646 Times in 590 Posts
    Are the cells in each row related to the adjacent cells? Once sorted, must they remain on the same row?

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 646 Times in 590 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
    Attached Files Attached Files

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 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.


    1. Insert a column before column A.
    2. In the 1st data row (exclude header rows) enter this formula in column A: =Row().
    3. Copy the formula down the rows.
    4. Select the numbers in Column A and hit copy.
    5. Select Paste Special then Values.
    6. Select the range of columns/rows to be sorted including column A.
    7. Sort Decending.
    8. Delete Column A.


    HTH
    Attached Files Attached Files
    Last edited by RetiredGeek; 2013-01-18 at 05:57.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    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

  7. #7
    Star Lounger
    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; 2013-01-19 at 04:55.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •