Sort a column by values in another column WITHOUT changing that second column

[d0rian]

See image below for what I'm trying to do. I have two columns of values...I want to tell Excel to sort col B (ascending), using values in col A, but I don't want col A to be sorted as well!
Like of course I can do a standard highlight of cols A and B, and it'll sort both rows by whatever column's values I want...but how do I tell Excel to sort one column only by using values in a secondary column which I don't also want sorted.

(hope to do this in VBA, but I'd be interested in how to achieve it manually too without resorting to a bunch of unnecessary steps / helper columns)

 

[Peter_SSs]

1. Can you confirm that you are still using Excel 2007?
2. Do you want the results still in columns A:B, or elsewhere as shown in your image?

 

[d0rian]

1. Can you confirm that you are still using Excel 2007?
2. Do you want the results still in columns A:B, or elsewhere as shown in your image?

Ha, I'm actually in progress of (finally) trying to migrate my file to 365...and part of that is trying to rewrite clunky processes like this one.
How come? Is there functionality in 365 for what I'm trying to do that doesn't exist in 2007?

I want the results in column B (the only column I want sorted). So col A will remain as it is, but col B will get sorted by the values in Col A without actually sorting col A itself.

 

[Peter_SSs]

Is there functionality in 365 for what I'm trying to do that doesn't exist in 2007?

Not if you want the results replacing the original data in column B. If you could have the results in other columns then you can do it in 365 with a single formula in a single cell as shown in F1 below.

d0rian.xlsm
	A	B	C	D	E	F	G
1	45	Apple				45	Grape
2	80	Pear				80	Apple
3	76	Banana				76	Banana
4	5	Grape				5	Pear
Sheet1

Cell Formulas
Range	Formula
F1:G4	F1	=LET(a,A1:A4,HSTACK(a,SORTBY(B1:B4,a)))
Dynamic array formulas.

To do it with vba try this with a copy of your workbook.

Sub Reorder_One_Column()
  Dim AL As Object
  Dim a As Variant
  Dim i As Long
 
  Set AL = CreateObject("System.Collections.ArrayList")
  With Range("A1", Range("B" & Rows.Count).End(xlUp))
    a = .Value
    For i = 1 To UBound(a)
      AL.Add Format(a(i, 1), "00000|") & a(i, 2)
    Next i
    AL.Sort
    With .Columns(2)
      .Value = Application.Transpose(AL.ToArray)
      .Replace What:="*|", Replacement:="", LookAt:=xlPart
    End With
  End With
End Sub

BTW, if you are now using 365 please update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[kevin9999]

The 'cheats' way

Sub Sort_Neighbour()
    Application.ScreenUpdating = 0
    a = Range("A1", Cells(Rows.Count, "A").End(xlUp))
    Range("A1").CurrentRegion.Sort Key1:=Range("A1"), Order1:=1, Header:=0
    Range("A1").Resize(UBound(a, 1)).Value = a
End Sub