Forum Discussion

jerbear's avatar
jerbear
Copper Contributor
Jul 30, 2020

Help sorting in Excel

I need help getting the after sort results in my photo.    
Need Help
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Aug 04, 2020

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.

 

 

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Aug 04, 2020

    Riny_van_Eekelen 

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

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Aug 04, 2020

        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

         

Resources