Order a column based on another column

Question

I want to order column A based on the values ​​of column B.

In Google Sheets, this is simple: =SORT(A1:A100,B1:B100,TRUE)

How to do it in Excel ?

Answer 1

To do this manually, you can select all the sorted columns, and then click "Custom Sort ..." in the "Sort and Filter" section of the "Home" tab. This brings up a dialog in which you can specify which column is sorted, add several sort levels, etc.

If you know how to do something manually in Excel and want to learn how to do it programmatically using VBA, you can simply record the macro that you do manually and then look at the source code that it creates. I did this to sort columns A and B based on column B and pulled the appropriate code from what was created:

 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 automatically generated code almost always has unnecessary bloat. But, this is a good way to find out what features you might need to use or explore more. In this case, you can trim it like this:

 Range("A1:B6").Sort Key1:=Range("B1:B6"), Order1:=xlAscending 

If you want to reorder the contents of column A without touching column B (even if you use it as a sort key), you probably need to make a temporary copy, sort it and copy only column A.This is because the Excel sort function requires so that the sort key is in the sort range. 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 

Answer 2

Thanks @brunosan: D

Finally, I got the answer because the question @brunosan after a few days I did not find the answer in the Excel table about my problem. Based on the @brunosan question, I tried sorting formulas, and suddenly I try to use other sorting options in google spreadsheet.

so this is my problem i solved:

i has values ​​in column A, but the values ​​in column A must be updated. I put the updated data in column B, so I saved the original data in column A for comparison later. with image

before

but the problem is that the updated data in column B (based on the data of column A) no longer has such a structure with column A. You can see in the picture above. Several data was deleted or lost, so I have an empty line.

The question is that I want to replace the updated data that was structured in column A. What does not exist, the data remains empty. So I tried on the google sheet with the whole column selected and with SORT RANGE, and then select SORT BY COLUMN A. and voila, the result I need .: D see the picture below.

after

you can imagine that I have a hundred data to a million data, so much time lost if I check one by one before the fall: D.

nb: I'm sorry about the blurry data: D

Answer 3

This is a manual method in animated form: