Help sorting in Excel

jerbear 

A small solution would be a VBA solution like the one in the attached file. Is only an additional suggestion 🙂

Sub Sort()
Dim strSpalte As String
Dim strBereich As String

'Parameter
strBereich = "A1:B11" 'Area
strSpalte = "A"  'column

'Sort
With Tabelle1 'Sheet
Range(strBereich).Sort _
 Key1:=Range(strSpalte & "1"), Order1:=xlAscending, _
 Header:=xlYes, MatchCase:=True
End With
End Sub

If you liked one of the suggested solutions, please click on it as the correct answer, if possible with a like (thumbs up). If none is the solution you are looking for, please give us a quick feedback.


Best regards,
Nikolino
I know I don't know anything (Socrates)

jerbear 

A formula solution, provided you have the correct version of Excel!

= LET(
  unpivot, LET(
     k, SEQUENCE(40,1,0),
     rowNum, MOD(k,10),
     colNum, QUOTIENT(k,10),
     INDEX(Table1,1+rowNum,1+colNum) ),
  sort, LET(
     sorted, SORT(unpivot),
     IF(sorted<>0, sorted, "") ),
  repivot, LET(
     idxArray, SEQUENCE(10,1,0)+10*SEQUENCE(1,4,0),
     INDEX(sort, 1+idxArray) ),
  repivot )

SergeiBaklan 

I refactored the formula because it was you that first demonstrated the nested LET to me!

jerbear In your example you have a range of 10 rows and 4 columns containing 35 names an 5 blanks. Your aim is to sort these in alphabetical order and display them side-by-side in groups of max 10.

I suspect that your real situation isn't as straightforward, but am offering you a power query (PQ) solution that does exactly what you ask for in the example. I am fully aware that this is not the most elegant or dynamic solution. A real expert in PQ can probably come-up with a better way.

jerbear 

You may sort columns one by one without expanding the selection