Mar 14 2022 03:23 AM
I have a range of single digit numbers (please see below) and I want to sort each row Largest to Smallest (i.e., largest digits on the left and in descending order to the right). If I Custom sort each row it works, but I want to be able to sort a great number of rows in one go. How can I do this in Excel 365. Many thanks for your help.
1 | 0 | 0 | 1 | 0 | 0 |
1 | 0 | 1 | 1 | 0 | 3 |
1 | 0 | 1 | 1 | 0 | 1 |
1 | 1 | 0 | 0 | 0 | 2 |
0 | 0 | 1 | 3 | 0 | 0 |
1 | 0 | 1 | 2 | 0 | 2 |
0 | 0 | 2 | 2 | 0 | 0 |
2 | 0 | 2 | 0 | 1 | 2 |
0 | 0 | 1 | 1 | 0 | 1 |
1 | 0 | 1 | 0 | 0 | 2 |
0 | 0 | 3 | 0 | 1 | 1 |
Mar 14 2022 03:42 AM
You could adapt the following macro for your setup:
Sub SortRows()
Dim r As Long
Dim m As Long
Application.ScreenUpdating = False
m = Range("A" & Rows.Count).End(xlUp).Row
For r = 1 To m
Range("A" & r).EntireRow.Sort Key1:=Range("A" & r), Order1:=xlDescending, Header:=xlNo, Orientation:=xlSortRows
Next r
Application.ScreenUpdating = True
End Sub
Mar 14 2022 11:38 AM
Mar 14 2022 01:35 PM
SolutionIf 'row' is a relative reference to a single row, then
= SORT(row,,-1,1)
will create a sorted copy of the row which may be filled down to complete the table. To do the same thing with a single formula requires the latest version of 365
= MAKEARRAY(11,6,
LAMBDA(r,c,
INDEX(SORT(INDEX(data,r,),,-1,1),c)
)
)
Mar 14 2022 02:21 PM
Mar 14 2022 01:35 PM
SolutionIf 'row' is a relative reference to a single row, then
= SORT(row,,-1,1)
will create a sorted copy of the row which may be filled down to complete the table. To do the same thing with a single formula requires the latest version of 365
= MAKEARRAY(11,6,
LAMBDA(r,c,
INDEX(SORT(INDEX(data,r,),,-1,1),c)
)
)