+ Reply to Thread
Results 1 to 11 of 11

Arrange columns by alphabetical column names

  1. #1
    Valued Forum Contributor
    Join Date
    12-02-2012
    Location
    Melbourne, VIC
    MS-Off Ver
    Excel 2016
    Posts
    750

    Arrange columns by alphabetical column names

    Is there a quick way to re-arrange the data (in a Table, as in defined Excel Table) so all column names/headers appear alphabetically?
    (in my case, the data has as many as 50 columns, so it is kinda time-consuming to drag and drop them around to get them in that order).

    in the attached sample, though the column headers are numbers, but should suffice the purpose.
    Attached Files Attached Files
    Last edited by jewelsharma; 01-05-2018 at 01:30 AM.

  2. #2
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    San Escobar but I could be everywhere
    MS-Off Ver
    All on PC except 365
    Posts
    9,870

    Cool Re: Arrange columns by alphabetical column names

    Do you mean something like this?
    Attached Files Attached Files
    sandy
    I want to see what you are trying to achieve, not how you are trying to do it
    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  3. #3
    Valued Forum Contributor
    Join Date
    12-02-2012
    Location
    Melbourne, VIC
    MS-Off Ver
    Excel 2016
    Posts
    750

    Re: Arrange columns by alphabetical column names

    Yes, that's what I'm after. What's a quick/easy way to achieve this.

  4. #4
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    San Escobar but I could be everywhere
    MS-Off Ver
    All on PC except 365
    Posts
    9,870

    Re: Arrange columns by alphabetical column names

    You have Ex2016 so you have PowerQuery also
    • Go to Data - Show queries
    • On the right you'll see Query window and table there
    • Right click on this table and select Edit
    • in new PowerQuery window on the right side you will see steps how it was done

    or another way
    • select your source table (ctrl+a)
    • copy
    • create new sheet (will be deleted later)
    • in A1 right click and paste as values
    • don't remove selection
    • copy pasted values
    • go to the blank cell and right click then paste TRANSPOSE
    • don't remove selection
    • then DATA - Sort (sort by column A - smallest to largest)
    • copy this range
    • then again paste TRANSPOSE somewhere in sheet 2
    • copy this range , go to your table
    • paste as values (it will change table to range)
    • recreate table again if you wish
    • delete sheet 2

    If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
    then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
    If you did it already - ignore it.
    Thank you.
    Last edited by sandy666; 01-05-2018 at 02:15 AM.

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,634

    Re: Arrange columns by alphabetical column names

    Other way
    select entire data > Disign (from main tab)
    under design > Convert Range
    then sort data -- Data > Sort > Options > Sort Left to Right > Sort by Row1 > ok > sort anything that look like a number, as number > ok

    Then Convert data range to table -- Insert > Table
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,372

    Re: Arrange columns by alphabetical column names

    That's a 'hidden' gem, Siva!
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  7. #7
    Valued Forum Contributor
    Join Date
    12-02-2012
    Location
    Melbourne, VIC
    MS-Off Ver
    Excel 2016
    Posts
    750

    Re: Arrange columns by alphabetical column names

    Yes, I was doing like that for non-table ranges. But for defined Tables the option to Sort Rows Left > Right is greyed out. Was wondering there's a direct way to work on the table itself, w/o converting back to range.

    Notwithstanding, thanks for your answer. I'm sure it benefits the community.

  8. #8
    Valued Forum Contributor
    Join Date
    12-02-2012
    Location
    Melbourne, VIC
    MS-Off Ver
    Excel 2016
    Posts
    750

    Re: Arrange columns by alphabetical column names

    Like the use of PowerQuery to achieve this. Nice work!

  9. #9
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    San Escobar but I could be everywhere
    MS-Off Ver
    All on PC except 365
    Posts
    9,870

    Re: Arrange columns by alphabetical column names

    Tables work with columns and content so you can't transpose Excel Table. That is why MS created PowerQuery . This is ETL tool - Extract - Transform - Load
    (but you can;t do that in-place)

    Welcome and Thanks

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,372

    Re: Arrange columns by alphabetical column names

    Yes, PowerQuery is a fantastic tool - take the time to get to know it a bit, and you won't regret it.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  11. #11
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B 2016
    Posts
    1,901

    Re: Arrange columns by alphabetical column names

    First convert table to range: Table Tools > Design > Convert to Range

    Then sort: Data > Sort > Options > Orientation > Sort left to right > Sort by [select Row 1] > OK

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How to Re-arrange the Items in Alphabetical Order?
    By jalalsalik in forum Excel General
    Replies: 2
    Last Post: 09-30-2015, 02:32 AM
  2. [SOLVED] Help! Same Names Merge into One then arrange the description by column
    By James Ray in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-04-2015, 10:26 AM
  3. Arrange columns of names & addresses into rows
    By SKMaverick in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-04-2010, 02:36 PM
  4. Replies: 3
    Last Post: 05-03-2006, 08:25 PM
  5. How to arrange your tabnames in alphabetical / chronological order
    By Dbase Beginner in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-18-2005, 09:12 AM
  6. [SOLVED] How do I arrange sheets in alphabetical order?
    By Supreme Grace in forum Excel General
    Replies: 2
    Last Post: 03-16-2005, 01:06 PM
  7. [SOLVED] How do I arrange sheets in alphabetical order?
    By gracebakky in forum Excel General
    Replies: 2
    Last Post: 03-16-2005, 07:06 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1