1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Transposing Excel Columns

Discussion in 'Business Applications' started by hip48, Jan 21, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. hip48

    hip48 Thread Starter

    Joined:
    Jun 10, 2005
    Messages:
    9
    If you have Excel data on columns A,B and C sorted by column A, is there a simple way of transposing the columns so you can view the columns in the order of B,C,A sorted by column B, without rearranging the columns by dragging them to new locations?
     
  2. WhitPhil

    WhitPhil Gone but never forgotten Trusted Advisor

    Joined:
    Oct 4, 2000
    Messages:
    8,684
    Ensure that your worksheet is saved first.

    Then,select the rows/columns that contain you data then

    Data > Sort
    select col B as sort

    Note that when you exit it will ask if you wish to save, since you have now resorted the data. Answer No, unless you really want it saved that way.

    Or, were you really asking how to sort by B AND have it on the left side of column C?
     
  3. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    To sort your columns i.e.
    make Column B move to Column A
    make Column C move to Column B
    make Column A move to Column C

    you need field headings in row one that will allow you to sort by column.

    So lets say your field headings in row1 are

    C | A | B

    Make sure you are in one of the cells of your data range

    Then select Data, Sort

    Then click on Options and select Orientation, Sort Left to Right and then click on OK

    Then in the first sort by option, click on the drop down icon and select row1 and then click on OK

    Your columns of data will be moved across based on the alpha basis in Row !

    Bear in mind that all the data in column B in the example will finsih up in Column A, Data in Column C will finish up in Column B and data in Column A will finish up in Column C

    Hope that helps.
     
  4. WhitPhil

    WhitPhil Gone but never forgotten Trusted Advisor

    Joined:
    Oct 4, 2000
    Messages:
    8,684
    Villian

    Your solution doesn't sort by columns, it sorts by rows. All of the data will no longer be column based since it will be moved based on the sort. That is what the orientation change does. Leaving it as default will sort by columns.

    And, you don't need Field Headings, you just Select "No Header Row".
     
  5. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    WhitPhil

    What I meant by that was if you sort left to right on the following example

    3 2 1
    2 3 1

    The result will be

    1 2 3
    1 3 2

    You cant select No Header row in that situation.

    The reason I said C A B
    is that you have to sort by a row in this case row one
    It will then use the letter in A1 B1 & C1

    and take the data in column B and shift it into column A
    and take the data in column C and shift it into column B
    and take the data in column A and shift it into column C

    So I am not clear what you are on about
     
  6. WhitPhil

    WhitPhil Gone but never forgotten Trusted Advisor

    Joined:
    Oct 4, 2000
    Messages:
    8,684
    Villan:

    Thanks. Let me clarify where I'm coming from:

    If I the OP's post correctly, they have data as follows, sorted by column A

    Joe Technician Level 1
    Ralph Senior Tech Level 5
    Tom Junior Tech Level 8

    That is, the data is in sequence by name.

    They then want that sorted by Column B so that it results in

    Tom Junior Tech Level 8
    Ralph Senior Tech Level 5
    Joe Technician Level 1

    IE: Sorted by Title

    They also indicated they wanted Column B first but they haven't responded. A simple column cut, insert before the sort would accomodate this.

    If you were to sort this data left to right using Row 1 it would end up as

    Joe Level 1 Technician
    Ralph Level 5 Senior Tech
    Tom Level 8 Junior Tech

    Because Joe is already the lowest sort order in row 1, then Level then Technician

    If you select the data and sort by row 2 it results in

    Level 1 Joe Technician
    Level 5 Ralph Senior Tech
    Level 8 Tom Junior Tech

    because Level 5 is lower than Ralph and Senior so that column comes first.

    If you sort by row 3 the result is

    Technician Level 1 Joe
    Senior Tech Level 5 Ralph
    Junior Tech Level 8 Tom

    because Junior is before Level which is before Tom
     
  7. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    Best to wait WhitPhil :)
     
  8. WhitPhil

    WhitPhil Gone but never forgotten Trusted Advisor

    Joined:
    Oct 4, 2000
    Messages:
    8,684
    Agreed. But while we do, is that what you thought a Left to Right sort was going to do?

    I've never used that and, after testing it, am not sure when I would use it. I saw a post somewhere that wanted to sort their column headers, and I can see using it then.
     
  9. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    .. to interject MHO.. (LOL!) I think it would be just select columns A:C | Data | Sort | Sort by B | Ok, select column A, right click the column header, select Cut, right click column C header, select Insert Cut Cells.
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/537245

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice