Forum Discussion
sort all columns
Hello,
i have a sheet with 5000 columns and 7 rows.
These are filled with numbers.
I would like to sort every column from lowest to highest order.
How can i achieve this?
You can sort all columns in your sheet from lowest to highest order using the following steps:
Select the range of cells that contains your data (all 5000 columns and 7 rows).
Click on the "Data" tab in the Excel Ribbon.
In the "Sort & Filter" group, click on the "Sort" button.
In the "Sort" dialog box, make sure that the "My data has headers" checkbox is unchecked.
In the "Sort By" section, choose the column you want to sort by (it doesn't matter which column you choose since you want to sort all columns).
In the "Sort On" section, choose "Values".
In the "Order" section, choose "Smallest to Largest".
Click "OK".
Excel will now sort each column in your sheet from lowest to highest order. This may take some time to complete, depending on the size of your data set. Once the sorting is complete, your data will be arranged in ascending order within each column.
Alternatively, if you are familiar with VBA, you can use a macro to automate this process. Here is an example macro that sorts all columns in ascending order:
Sub SortAllColumns() Dim lastRow As Long Dim lastColumn As Long Dim i As Long 'Find the last row and column with data in the active sheet lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row lastColumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column 'Loop through each column and sort it For i = 1 To lastColumn Range(Cells(1, i), Cells(lastRow, i)).Sort key1:=Cells(1, i), order1:=xlAscending, Header:=xlNo Next i End SubI hope this helps!
4 Replies
It should be possible using a simple Lambda function but unfortunately MS messed up big-time when specifying the functionality of helper functions. That is the dreaded "Nested arrays are not supported" #CALC! error. With sufficient effort one can define
= ByColλ(values, Sortλ)where
ByColλ = DROP( REDUCE("", SEQUENCE(COLUMNS(values)), LAMBDA(a, k, LET(column, CHOOSECOLS(values, k), HSTACK(a, Fnλ(column)))) ) ,,1) Sortλ = SORT(x)The formula is clunky and somewhat inefficient when all that is required is
= BYCOL(values, Sortλ)[Note: the formulas were copied from the AFE which generates LAMBDA function and the associated parameters so these do not appear within the definitions]
For me, the concept of the spreadsheet could be defined as a computational environment for the manipulation and presentation of 2D arrays (including or even especially arrays of arrays) so the #CALC! error is particularly galling! (I realise many spreadsheets are more focussed upon relational data, which is fine, and Power Query works well for such applications).
You can sort all columns in your sheet from lowest to highest order using the following steps:
Select the range of cells that contains your data (all 5000 columns and 7 rows).
Click on the "Data" tab in the Excel Ribbon.
In the "Sort & Filter" group, click on the "Sort" button.
In the "Sort" dialog box, make sure that the "My data has headers" checkbox is unchecked.
In the "Sort By" section, choose the column you want to sort by (it doesn't matter which column you choose since you want to sort all columns).
In the "Sort On" section, choose "Values".
In the "Order" section, choose "Smallest to Largest".
Click "OK".
Excel will now sort each column in your sheet from lowest to highest order. This may take some time to complete, depending on the size of your data set. Once the sorting is complete, your data will be arranged in ascending order within each column.
Alternatively, if you are familiar with VBA, you can use a macro to automate this process. Here is an example macro that sorts all columns in ascending order:
Sub SortAllColumns() Dim lastRow As Long Dim lastColumn As Long Dim i As Long 'Find the last row and column with data in the active sheet lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row lastColumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column 'Loop through each column and sort it For i = 1 To lastColumn Range(Cells(1, i), Cells(lastRow, i)).Sort key1:=Cells(1, i), order1:=xlAscending, Header:=xlNo Next i End SubI hope this helps!
- HI,
thanks for answering.
I tried the sort function from excel as you mentionned, but then only the first column is sorted, rest of columns changed order but not correctly.
I am no VBA specialist, so i copy pasted your code, ran it and this worked.
thanks a lot NikolinoDE- I'm glad that the suggested solution helped you.
I wish you continued success with Excel!
