# Help sorting in Excel

Occasional Visitor

# Help sorting in Excel

I need help getting the after sort results in my photo.

14 Replies

# Re: Help sorting in Excel

You may sort columns one by one without expanding the selection

# Re: Help sorting in Excel

@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.

# Re: Help sorting in Excel

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 )

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

# Re: Help sorting in Excel

Would adding an index column and unpivoting other columns involve fewer queries?

# Re: Help sorting in Excel

@Peter Bartholomew Guess so. Didn't think about that one. Much better. Thanks!

See attached.

# Re: Help sorting in Excel

The idea was in the air, if I found that an hour earlier than you that means nothing.

Even if LET() is on Current (Preview) now I still avoid to use it in MTC discussion, will wait availability at least on Current.

# Betreff: Help sorting in Excel

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, _
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)

# Re: Help sorting in Excel

That would do it!

# Re: Help sorting in Excel

To play with coding

let
Source = Excel.CurrentWorkbook(){[Name="tblNames"]}[Content],
SortedSource = Table.FromColumns(
List.Split(
List.Sort(
List.Distinct(
List.RemoveNulls(
List.Union(
Table.ToColumns(Source)
)
)
)
),
Table.RowCount(Source)
),
Table.ColumnNames(Source)
)
in
SortedSource

# Re: Help sorting in Excel

"Even if LET() is on Current (Preview) now I still avoid to use it in MTC discussion"

I was going to say that was because you are more responsible than I, and simply seek to help, whereas I look for challenges that allow me to develop the techniques I can apply to a range of problems.

With your nested PQ solution, I am not so sure!  Whereas, with the LET function, I introduced local names to provide documentation and remove nesting, you have travelled a similar road in the opposite direction, albeit with a striking indentation pattern.  I expect @Riny_van_Eekelen coped just fine, but it took me a while before I settled on reading from the deepest nesting outwards and I also had to come to terms with some list methods that I would not be able to access from the GUI!

I still have the feeling that I am not going to become fluent with such a style anytime soon!

Thanks anyway.

# Re: Help sorting in Excel

@Peter Bartholomew , if I do this query from scratch I'd do it approximately the same way as @Riny_van_Eekelen did. Reasons - more UI is better for shared solutions; UI is usually better from maintenance point of view. Plus from performance point of view Power Query is better optimized to work with tables rather than with lists. However, performance is half a technology, another half is the art. Approach depends on concrete case.

Nested list functions are only to demonstrate alternatives. I fully agree, from usability point of view same code organised by steps is much better

let
Source = Excel.CurrentWorkbook(){[Name="tblNames"]}[Content],

combineColumnsInOneList = List.Union(Table.ToColumns(Source)),
removeNullsFromList     = List.RemoveNulls(combineColumnsInOneList),
removeDuplicates        = List.Distinct(removeNullsFromList),
sortList                = List.Sort(removeDuplicates),
rowsInTable             = Table.RowCount(Source),
splitListOnFutureColumns= List.Split(sortList,rowsInTable),
generateSortedTable     = Table.FromColumns(
splitListOnFutureColumns,
Table.ColumnNames(Source)
)
in
generateSortedTable

Nested variant is bit better only from development point of view, at least for me.

# Re: Help sorting in Excel

The is nothing special about the result; the point is that this was the first time I had ever put together M code using the advanced editor!

let
Source = Excel.CurrentWorkbook(){[Name="tblNames"]}[Content],
SortedList = List.Sort(
List.Distinct(
List.RemoveNulls(
List.Union(
Table.ToColumns(Source)
)
)
)
),
n = 1+Number.IntegerDivide(List.Count(SortedList),4),
List.Split(SortedList,n),
Table.ColumnNames(Source)
)
in

Thank you for the model code and guidance.

# Re: Help sorting in Excel

The only what is discussable here is

n = 1+Number.IntegerDivide(List.Count(SortedList),4)

why the magic 4 appeared. But that's not clear from the specification - shall resulting table be the same size as the source, or or only same number of columns, or some number of rows and doesn't matter how many columns. If 4 is predefined when no problems, otherwise it shall be calculatable.

# Re: Help sorting in Excel

True.  The 4 should have been

Table.Column.Count(Source)

I wanted to change the functionality, just for the same of doing something different as an exercise.  The useful side effect is that the process copes better with the situation in which more than a quarter of the cells are null, which potentially reduces the output to 3 columns, depending on the algorithm.

I decided to maintain the number of columns but reduce the records.  I do not know what the OP would have wanted, but I suspect he has long since left the discussion.