Sort table column left to right

[gaudrco]

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?

 

[NoSparks]

Will definitely put an end to any concern about auto filling to a newly added competitor.

 

[gaudrco]

I had that concern that if I reorganize my table (alphabetize) then that will screw up the autofill. However, if I also sort the source data for the autofill table, then both will be the same and the autofill will match again. Fortunately, the source data is not in a table so its less steps. I figured if I could get help with the table, I could manage the rest. Does that make sense?

P.S. That was a good catch NoSparks

 

[NoSparks]

That was why I asked what you would gain.
Appears to me you'll be scrolling horizontally to find the competitor you want. Could be a pain if lots of competitors.
Think I'd have sorted the competitors into a combo drop down and have a selection from there take you to that competitor.

Not sure it will be necessary to sort source data if an R1C1 formula is written to the newly inserted columns.
If you select a cell in the databodyrange of the original table before anything is sorted,
goto the VBA and hit Crtl+g to bring up the Immediate window,
type in ? activecell.FormulaR1C1 you'll see the same thing in every cell.
So I feel that for the new and yet to be sorted column, that same R1C1 formula could still be used, at least until somebody adds or deletes a column in the table, then you're screwed.

 

[gaudrco]

Thank you for you input. Perhaps this isn't necessary. I will probably leave this feature out. With all the macros I am currently using, it seems a bit late to use the combo drop down.

 

[gaudrco]

I guess I don't give up.

I put together this code from pieces that I have gotten from you, NoSparks, and everything is working great except one part. Here is the code:

Private Sub CommandButton1_Click()
'Converts table on "Competitor Comparison" sheet to a range
ActiveSheet.ListObjects(1).Unlist
'Sorts range left to right
Range("DynamicCompList").Sort Key1:=Range("DynamicCompList"), Order1:=xlAscending, Header:=xlYes, Orientation:=xlLeftToRight
'Converts range back to a table
ActiveSheet.ListObjects.Add(xlSrcRange, Range("DynamicCompTable"), , xlYes).Name = _
        "CompComparisonTable"
        
 Range("A8").Select
'Removes the filter dropdowns
 Selection.AutoFilter


'This next line is not working right. Range of object error. Must reference sheet "Competitor Comparison Data"
Range("DynamicDataList").Sort Key1:=Range("DynamicDataList"), Order1:=xlAscending, Header:=xlYes, Orientation:=xlLeftToRight

'Corrects the sorted column formulas by filling right
Dim ws As Worksheet, oLo As ListObject


Set ws = Sheets("Competitor Comparison")
With ws
    Set oLo = .ListObjects("CompComparisonTable")
With oLo
    Set rng = .DataBodyRange.Offset(, 1).Resize(, .ListColumns.Count - 1)
With rng
.FillRight
End With
End With
End With


End Sub

So the line that I marked is not working right when I run the code on ("Competitor Comparison") but it does work if I assign it to a button on the sheet where to range is ("Competitor Comparison Data"). So what I think needs to be corrected is the line of code must refer to ("Competitor Comparison Data") sheet, then after that, the rest of the code must refer to ("Competitor Comparison") sheet

 

[NoSparks]

Here's what I was playing around with at the time you were planning to abandon this.

I didn't know what the actual vba requirement would be for the horizontal sort so used the macro recorder to see.
The message box left in the code will show you the info saved to variables prior to unlisting for use with sorting and re-establishment of the table.
You'll notice I didn't want the sort to actually happen until I was sure the ranges were right. You can remove that stuff after testing.

Sub TableToRangeSortAndBack()
    Dim ws As Worksheet, oLo As ListObject      'sheet and table
    Dim tblName As String, tblRng As String     'table name and overall range
    Dim srtRng As String, srtKey As String      'range to sort, key to sort on

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Set ws = Sheets("Competitor Comparison")
Set oLo = ws.ListObjects("CompComparisonTable")

'get specs of existing table
tblName = oLo.Name
tblRng = oLo.Range.Address(False, False)
srtRng = Range(tblRng).Offset(, 3).Resize(, oLo.ListColumns.Count - 3).Address(False, False)
srtKey = oLo.HeaderRowRange(4).Resize(, oLo.ListColumns.Count - 3).Address(False, False)

' This message can be commented out after testing
MsgBox "The table name is in the variable 'tblName'" & vbLf & Space(15) & tblName & vbLf & vbLf & _
       "The table range is in the variable 'tblRng'" & vbLf & Space(15) & tblRng & vbLf & vbLf & _
       "The range to use for sort Key is in the variable 'srtKey'" & vbLf & Space(15) & srtKey & vbLf & vbLf & _
       "The range to sort is in the variable 'srtRng'" & vbLf & Space(15) & srtRng

Stop    'to make sure you're looking at this
Exit Sub    'so the sort won't happen by accident

'convert table to standard range
oLo.Unlist

'sort only from col D left to right
    ws.Sort.SortFields.Clear
    ws.Sort.SortFields.Add Key:=Range(srtKey), _
         SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ws.Sort
        .SetRange Range(srtRng)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlPinYin
        .Apply
    End With

're-create the table
ws.ListObjects.Add(xlSrcRange, Range(tblRng), , xlYes).Name = tblName

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

And I believe the fillright line in the other macro, which you moved to later in the macro, can be replaced with this.

oLo.DataBodyRange(1, oLo.ListColumns.Count).FormulaR1C1 = "=IFERROR(INDEX('Competitor Comparison Data'!C[4],MATCH(R3C2,'Competitor Comparison Data'!C4,0)+ROWS(R8C1:RC1)-1),""No Match"")"

You add the new competitor as per normal from the Information Sheet, using the r1c1 formula above, then you can sort the table and hopefully all will be well.

 

[Akuini]

Just an idea:
Why not do it manually?
Copy the intended columns to another range, it will be just range not a table, so you can do the horizontal sorting, then copy it back to the table then delete the range.
Of course you can write a macro to do that too.

But well, with or without a macro, I'm not sure about how it would affect your formula.

 

[gaudrco]

NoSparks,

Thank you very much for this. I can tell you spent a lot of time and I appreciate it. It does work so that is great. If I could change something, it would be this:

The "Competitor Comparison" sheet formula data is being pulled from "Competitor Comparison Data". This sheet is set up very similarly where the competitors are in columns. When the Sorting Button is click, I would like the "Competitor Comparison Data" sheet columns to also sort; that way the two sheets match each other in terms of competitor order. Then the FillRight formula would get executed. So if I added "Apple" as a competitor, initially it would appear at the end of the tables until the button is click (this is working). Then once the button is clicked, "Apple" would jump to the beginning of the table on the "Competitor Comparison" sheet (also working) and jump to the beginning of the "Competitor Comparison Data sheet" (not working). Then the FillRight code would correct the reference formulas such that "Apple" would have a new reference formula of H:H instead of W:W.

The range on the "Competitor Comparison Data sheet in not a real table. (And this is where one of my other forum threads come into play https://www.mrexcel.com/forum/excel-questions/1110553-dynamic-range-expands-new-columns.html) I wasn't able to figure out this part but here it is:

The range of the competitors that get sorted is from H5:V3000 (the competitor names are in the top row). The range should expand with new competitors added.

 

[NoSparks]

How is the "Competitor Comparison Data" sheet populated ?

 

[gaudrco]

Its populated manually. This is where users will enter raw data. So if they have information about a company, they can enter it here. When the "Add Competitor" button is pressed, a new column for that competitor shows up at the end of this range. So that's why I would like this range sorted as well.