Sorting grouped cells in excel based on one column with alpha and numerical values

Question

So my original dataset is structured such that each row represents one street and then there are three sets of columns that represent a recommendation for a design feature to change (like add trees), then another column that has a number in it (which is like how important that change is), and then another column that explains whether to increase or decrease that design feature. There could be up to 15 sets of these three columns. However, right now, the order or the columns is random - but I want the most important features (highest scoring) to show up first (the most left column) and the least scoring feature last (the most right column), but the order of the columns still has to be feature, score, increase/decrease, and so on.

So I thought this was way too hard - so I figured I would first transpose this so that I would have the values for feature, score, increase/decrease across three rows instead of columns. And then I found out I could group rows (same with columns, but still think this is easier with rows). I thought that by grouping rows that I could then sort by the score and it would keep the feature, score, increase/decrease together, but sorted by the feature with the highest score to the lowest, but it didn't work.

I have included pics of an example dataset of the original configuration (columns) vs. the transposed one (rows) to help explain what I mean above. Any thoughts??

showing where I am stuck.

Tried again - still stuck. This is what I see when I copy/pasted the sample data into the power query:

Answer 1

I suggest you use Power Query, available in Windows Excel 2010+ and Excel 365 (Windows or Mac).

The algorithm is similar:

• Transpose
• Pivot on what is now the 2nd column (was the second row in your data)
  • In Power Query you can pivot without aggregating.
• This gives you separate columns for feature, score and direction
• Then merely sort on the score column

To use Power Query

• Select some cell in your Data Table
• Data => Get&Transform => from Table/Range or from within sheet
• When the PQ Editor opens: Home => Advanced Editor
• Make note of the Table Name in Line 2
• Paste the M Code below in place of what you see
• Change the Table name in line 2 back to what was generated originally.
• Read the comments and explore the Applied Steps to understand the algorithm

Data Source

M Code

let

//change next lines to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}}),

//Transpose, then pivot and sort
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Pivoted Column" = Table.Pivot(#"Transposed Table", List.Distinct(#"Transposed Table"[Column2]), "Column2", "Column3"),
    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"score", Order.Descending}})
in
    #"Sorted Rows"

Results