9

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?

Dennis Vash
  • 50,196
  • 9
  • 100
  • 118
brunosan
  • 295
  • 2
  • 6
  • 17

4 Answers4

7

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
Brandon
  • 1,747
  • 17
  • 15
  • 1
    +1 but .. is there a way to do that with formulas? (without vba) – Dr. belisarius Jul 06 '11 at 01:01
  • @belisarius I ran across [this](http://www.get-digital-help.com/2009/03/27/sorting-text-cells-using-array-formula/) while looking around, although it's probably not as nice a solution as you're looking for. You could pull the items being sorted from another range by changing the first "List" to a different named range containing the same number of items as "List", ie `=INDEX(RangeToSort, MATCH(...` (leaving the rest the same). Then the "List" range would act as the sort key. I think that solution might have issues if there are duplicate items in the range used as the sort key though... – Brandon Jul 06 '11 at 07:01
  • Yep. Duplicate items will spoil that approach – Dr. belisarius Jul 06 '11 at 12:00
6

This is the manual method in an animated form:

enter image description here

MechtEngineer
  • 547
  • 6
  • 12
0

Thank you @brunosan :D

finally i got the answer because the @brunosan question after several days i didn't find answer in excel spreadsheet about my problem. Based on @brunosan question i tried the sort formulas, and suddenly i am trying another "sort" options on google spreadsheet.

so this is my problem that i had solved:

i have values in Column A, but the values in column A must be updating. The updated Data I put in Column B, so I kept the original data in column A for comparing later. with picture

before

but the trouble is the updated data in Column B (based on column A data) hasnt same structured anymore with Column A. You can see in the picture above. Several data have been deleted or lost, so i have an empty row.

The question is that i want to replace that updated data same structured with Column A. Which is the data doesnt exist leave blank. So i tried in google sheet with selected all column and with SORT RANGE and then choose SORT BY COLUMN A. and voila, the result that i wanted. :D see in the picture below.

after

you can imagine that i has a hundred data till million data, so much wasted time if i check one by one till drop :D.

nb: I am sorry about the blurred data :D

delapanlima dotcom
  • 1
0

Use the =SORTBY() function; its format is

=SORTBY(array, by_array1, [sort_order1], by_array2, [sort_order2]...),

but its simplest is

=SORTBY(array, by_array1, [sort_order1]),

which is the basic one. It works as you would expect. Further arrays are to sort within by_array(n-1) using by_array(n).

RuskiyJohn
  • 1