Sort table column left to right

gaudrco

Board Regular
Joined
Aug 16, 2019
Messages
203
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?
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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 ?
 
Upvote 0
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.
 
Upvote 0
Upvote 0
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.
 
Upvote 0
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")
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top