CODEWITHSUNDEEP
excelvbasorting
user9829408
user9829408

Reputation:

Numerically sorting multiple Excel columns individually

I have 52 randomized sets of 7 numbers in columns that I need to sort numerically, but when highlighting all of the data and trying to sort them from smallest to largest, it seems to only apply the sorting to the first column -

Screenshot for Reference

Upvotes: 0

Views: 834

Answers (1)

Caleb McNevin
Caleb McNevin

Reputation: 362

Excel is going to sort on the furthest left field (or 'Column') in your selection, and move the associated values for each row accordingly.

I've recorded a macro, then edited it for you, and I believe it should work. Just select all the values and run the macro:

Option Explicit

Sub SortColumnsIndividually()
    Dim Column As Range
    For Each Column In Selection.Columns
        ActiveSheet.Sort.SortFields.Clear
        ActiveSheet.Sort.SortFields.Add Key:=Column, _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveSheet.Sort
            .SetRange Column
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    Next Column
End Sub

Why did excel do this?

This happens because excel treats each row as a collection of related values. For example, consider the following table:

+-----------+-----------+--------+
| FirstName | LastName  | Salary |
+-----------+-----------+--------+
| Bob       | Smith     |  45000 |
| Jane      | Doe       |  55000 |
| Susan     | Black     |  48000 |
| Frank     | Daniels   |  51000 |
+-----------+-----------+--------+

Say you wanted to sort by Salary from smallest to largest. You would expect that when excel moves the salary values, it will also move the associated First and Last names, as opposed to sorting the salary field alone.

So, sorting this table by Salary in ascending order, you would expect this result:

+-----------+-----------+--------+
| FirstName | LastName  | Salary |
+-----------+-----------+--------+
| Bob       | Smith     |  45000 |
| Susan     | Black     |  48000 |
| Frank     | Daniels   |  51000 |
| Jane      | Doe       |  55000 |
+-----------+-----------+--------+

NOT this (See how the names are mapped to different salaries now?):

+-----------+-----------+--------+
| FirstName | LastName  | Salary |
+-----------+-----------+--------+
| Bob       | Smith     |  45000 |
| Jane      | Doe       |  48000 |
| Susan     | Black     |  51000 |
| Frank     | Daniels   |  55000 |
+-----------+-----------+--------+

Since the above case is representative of the vast majority of sorting tasks that Excel users do, this as an expected behavior for Excel.

Upvotes: 3

Related Questions