Page 1 of 4 123 ... LastLast
Results 1 to 10 of 37

Thread: Sort table column left to right

  1. #1
    Board Regular
    Join Date
    Aug 2019
    Posts
    150
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Sort table column left to right

    Is there way to sort table columns left to right? That is, the column headers sort alphabetically along with all the data below the headers. But here's the catch; I need just a selection of columns sorted.

    My table ("CompComparisonTable") has competitors as the column headers and with features as the rows. So the first column are features going down the rows and the second and third column have to do with my company so I want these to remain farthest left. Everything to the right of that are competitors. Right now, the spreadsheet is set up to have new competitors added to the end of the table, but after they are added, I would like the new competitor to sort alphabetically with the other competitors.

    Any ideas?

  2. #2
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,604
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Sort table column left to right

    Excel's built in Sort has a sort left to right option. With the Headers checkbox checked, it sounds like it should do what you want.

  3. #3
    Board Regular
    Join Date
    Aug 2019
    Posts
    150
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sort table column left to right

    I was looking at the feature but it is disabled for some reason. The headers checkbox is checked but when I go to Sort Options, the Left to Right sort option is disabled. Also, I was thinking that this feature would not sort the new competitors added automatically. I would imagine I would have to manually repeat the process each time a new competitor was added. Besides that, I don't think this feature would allow me to only sort a section of the table. I need the first 3 columns to remain static.

  4. #4
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,074
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Sort table column left to right

    Since your data is in actual table then you can't sort it Left to Right, you need to convert the table to range first, do the sort, then change it back to table .

    Besides that, I don't think this feature would allow me to only sort a section of the table.
    By section you mean some columns (not all)? Yes you can, just select the column then sort.

  5. #5
    Board Regular
    Join Date
    Aug 2019
    Posts
    150
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sort table column left to right

    My table must stay in a table format because the table is referenced all over my spreadsheet in VBA's. Is there a vba code for this excel function that will let me do this with a table?

  6. #6
    Board Regular
    Join Date
    Mar 2013
    Posts
    805
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sort table column left to right

    With everything in Excel there is usually more than one way to accomplish things.
    What's the reasoning for doing this, what's the end goal ?

  7. #7
    Board Regular
    Join Date
    Aug 2019
    Posts
    150
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sort table column left to right

    Hi NoSparks,

    You have helped me quite a bit with this project so you might have a good understanding of what I'm looking to do and how to do it.

    I have a button that when clicked, adds a new competitor to the end of the table. My end goal is for the table columns (Competitor Names) to be in alphabetical order. With every new competitor added, the column will be added to the table and then sorted in its appropriate alphabetical position in the table. The first three columns in my table need to stay farthest to the left and should not be affected by the alphabetical sorting.

  8. #8
    Board Regular
    Join Date
    Mar 2013
    Posts
    805
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sort table column left to right

    I realize that's what you want to do.
    What I don't know is why, what will you gain from this ?

    And if this is what you really need to accomplish that gain then Akuini's suggestion is the way to go
    here's a couple of links to help you out

    'convert table to range
    http://vba.relief.jp/excel-vba-conve...ange-of-cells/

    do your sort stuff normally

    'convert range to table
    https://analysistabs.com/excel-vba/tables-examples/

  9. #9
    Board Regular
    Join Date
    Aug 2019
    Posts
    150
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sort table column left to right

    The reason (gain) of this is it keeps the spreadsheet organized. I will not be the primary user of this spreadsheet, it is design to be used by basic users throughout the company. So the less that they have to do, the better. If every new competitor is sorted alphabetically, then it will be easier for users to find what they are looking for. It's a small gain but if I can get it working properly, I think it's worth it. So far, I have this:

    When a new competitor is added, this code will happen at the end (Incomplete):
    Code:
    Private Sub CommandButton1_Click()With Sheets("Competitor Comparison").ListObjects(1).Unlist
    Range("DynamicCompList").Select
    'This is a dynamic range that adjusts with each new competitor added. The range only includes the competitors.
    .SetRange Range("DynamicCompList")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlLeftToRight
            .SortMethod = xlPinYin
            .Apply
    'This should sort the competitor names alphabetically using the top row
    CompetitorComparison.ListObjects.Add(xlSrcRange, Range("DynamicCompTable"), , xlYes).Name = "CompComparisonTable"
    'DynamicCompTable is a dynamic range that also adjusts with each new competitor added. The range only includes all of my company columns and competitor columns. I think it must be don't this way because the the range will be adjusting with each new competitor added, so the code must reflect that.
    I know this code needs work but its the best I could put together with the little knowledge I know and the macro recorder.

  10. #10
    Board Regular
    Join Date
    Aug 2019
    Posts
    150
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sort table column left to right

    If that's hard to understand, maybe this will help illustrate the order:

    Code:
    Private Sub Command1_Click()
    
    'On sheet ("Competitor Comparison") convert ("CompComparisonTable") to range
    
    'Use ("DynamicCompList") to select the range for alphabetizing. Sort ("DynamicCompList") left to right alphabetically. I think the code looks something like this
    .SetRange Range("DynamicCompList")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlLeftToRight
            .SortMethod = xlPinYin
            .Apply
    
    'Convert ("DynamicCompTable") into a range. Table has headers. Name this table ("CompComparisonTable")

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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