Menu
I want to order Column A based on the values of Column B.
In Google Sheets that is simply: =SORT(A1:A100,B1:B100,TRUE)
How to do that in Excel?
366
To sort the DataFrame based on the values in a single column, you'll use . sort_values() . By default, this will return a new DataFrame sorted in ascending order.
Decide which column will be sorted, then click a cell in the column. Click Data and select Sort Sheet by column, A-Z (ascending) or Sort Sheet by column, Z-A (descending). In our example, we'll select Sort Sheet by column, A-Z. The sheet will be sorted according to your selection.
To do it manually, you can highlight all the columns you want sorted, then click "Custom Sort..." under "Sort & Filter" in the "Home" tab. This brings up a dialog where you can tell it what column to sort by, add multiple sort levels, etc.
If you know how to do something manually in Excel and want to find out how to do it programmatically using VBA, you can simply record a macro of yourself doing it manually and then look at the source code it generates. I did this to sort columns A and B based on column B and pulled the relevant code from what was generated:
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B1:B6"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:B6")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
Note that the automatically generated code almost always has unnecessary bloat though. But, it's a nice way to figure out what functions you might need to use or research more. In this case, you could trim it down to something like this:
Range("A1:B6").Sort Key1:=Range("B1:B6"), Order1:=xlAscending
If you only want to reorder the contents of column A without touching column B (even though you're using it as the sort key), you probably need to make a temporary copy, sort it, and copy back only column A. This is because Excel's Sort function requires the sort key to be in the range being sorted. So, it might look like this:
Application.ScreenUpdating = False
Range("A1:B6").Copy Destination:=Range("G1:H6")
Range("G1:H6").Sort Key1:=Range("H1:H6"), Order1:=xlAscending
Range("G1:G6").Copy Destination:=Range("A1:A6")
Range("G1:H6").Clear
Application.ScreenUpdating = True
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
