Help sorting in Excel

Copper Contributor

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

 

excel sort issue.png

 

14 Replies

@jerbear 

You may sort columns one by one without expanding the selection

image.png

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

Screenshot 2020-08-04 at 07.41.35.png

 

 

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

 

 

@Sergei Baklan 

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

@Riny_van_Eekelen 

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

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

 

:)

See attached.

@Peter Bartholomew 

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.

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

@Riny_van_Eekelen 

That would do it! :) 

@Riny_van_Eekelen 

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

 

@Sergei Baklan 

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

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

@Sergei Baklan 

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),
    LoadToTable = Table.FromColumns(
        List.Split(SortedList,n),
        Table.ColumnNames(Source)
    )
in
    LoadToTable

Thank you for the model code and guidance.

@Peter Bartholomew 

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.

@Sergei Baklan 

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.