I have a table of data which is consisted of 18 columns and 2.017 rows. I can get the row that has the highest (MAX) value in a cell but I need the row that has the most cells with higher values and have them in DESC order. I haven't managed yet to find a relevant post to this.

Here follows an example:

Using numbers up to 10 for illustration, the following shows the logic behind. (The actual numbers are those shown in Exhibit1)

Thank you

EDIT:

I am adding the below in order to try to clarify further. I am not sure if it is the correct path to go but I hope it makes sense.

In Exhibit2 I am indexing each column Desc (Based on Exhibit1) and then =SUM in the end of the row. Following this logic, the name having the lowest total is the one with the most high values (not the highest) in its row.

The result table is the following

CodePudding user response：

This set-up is volatile, so I would only adopt it if non-volatile alternatives are not forthcoming.

An additional column in your table with the following formula:

=SUM(COUNTIF(OFFSET([Column1],,TRANSPOSE(ROW(INDIRECT("1:"&COLUMNS(Table1[@[Column1]:[Column4]])))-1)),">="&Table1[@[Column1]:[Column4]]))

which you can then use to sort your table.

Note that this formula will most likely require committing with CTRL SHIFT ENTER for your version of Excel.

Amend the table and column names as required, noting that the part

Table1[@[Column1]:[Column4]]

as well as including the table name, should comprise the leftmost and rightmost of the contiguous columns to be interrogated.

CodePudding user response：

Although possible with formulas and helper tables/columns, this can also be accomplished using Power Query, available in Windows Excel 2010 and Excel 365 (Windows or Mac)

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
  • As we discussed in our Chat, I transform each column into a list of Ranked Entries; then sum the ranks for each row and sort as you have laid out.

M Code

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

//type all the columns
    data = Table.TransformColumnTypes(Source,{
        {"Order", Int64.Type}, 
        {"Name", type text}} &
        List.Transform(List.RemoveFirstN(Table.ColumnNames(Source),2), each {_, type number})
    ),

//Replace with ranks
//generate list of transforms to dynamically include all columns
    cols = List.RemoveFirstN(Table.ColumnNames(data),2),
    xForms = List.Transform(cols, (c)=> {c, each List.PositionOf(List.Sort(Table.Column(data,c),Order.Descending),_)}),
    ranks = Table.TransformColumns(data,xForms),

//add Index column to enable row-wise sums
// then add the sumRank column and delete the Index column
    #"Added Index" = Table.AddIndexColumn(ranks, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "sumRank", each 
        List.Sum(
            Record.ToList(
                Record.RemoveFields(#"Added Index"{[Index]},{"Order","Name","Index"})
            )
        )),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),

//join back with the original data table
//extract the sumRank column
    join = Table.NestedJoin(data,{"Order","Name"}, #"Removed Columns",{"Order","Name"}, "joined",JoinKind.FullOuter),
    #"Expanded joined" = Table.ExpandTableColumn(join, "joined", {"sumRank"}, {"sumRank"}),

//sort by the sumRank column, then remove it
    #"Sorted Rows" = Table.Sort(#"Expanded joined",{{"sumRank", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"sumRank"})
in  
    #"Removed Columns1"