Forum Discussion
sort by column then by numbers in a row
alternatively you could just add a 'RANK' column and then use built in sorting to rearrange the current table instead of creating a separate table. Here is a relatively generic option (i.e. doesn't assume 9 is the max or the number of columns) to create that 'RANK' column:
=LET(in,A1:.G99,
max_count, COLUMNS(in),
rcount, SEQUENCE(ROWS(in)),
uniq_vals, SORT(UNIQUE(TOCOL(in)),,-1),
rankrow, BYROW(in,LAMBDA(r, CONCAT(TEXT(MMULT(--(r=uniq_vals),EXPAND(1,max_count,,1)),"00")))),
SORTBY(rcount,SORTBY(rcount,BYROW(in,SUM),-1,rankrow,-1)))where you just need to change the A1:.G99 to the corresponding range of values.
I currently 'ranks' them with 1 being the highest but replace both of the '-1' in the last line with '1' and you can have it 'rank' with 1 being the lowest and the highest having the biggest value.
I've put your formula into one line in the formula bar, in cell W6 as follows:
=LET(in,B6:T52,max_count, COLUMNS(in),rcount, SEQUENCE(ROWS(in)),uniq_vals, SORT(UNIQUE(TOCOL(in)),,-1),rankrow, BYROW(in,LAMBDA(r, CONCAT(TEXT(MMULT(--(r=uniq_vals),EXPAND(1,max_count,,1)),"00")))),SORTBY(rcount,SORTBY(rcount,BYROW(in,SUM),-1,rankrow,1)))
Direct copy from above, but I get a #NAME? error.
I have adjusted the cell references for my table. I want to rank from highest to lowest.
when working with the table, I filter and remove the blanks.
A #NAME? error, in this case, might indicate you don't have access to eta-Lambda reduction, e.g. BYROW(array,SUM) vs BYROW(array,LAMBDA(x,SUM(x))).
Having said that, I don't believe this method will help in the end anyways, as attempting to sort a dataset with a dynamic spilled array present will error with "You can't change part of an array."
To create a "Rank" helper column with which to sort by, you'll need to use a separate formula in each cell (non-spillable). Try the following:
On the Ribbon, go to Formulas > Define Name
- Name: RiderRank
- Refers to:
=LAMBDA(rider_ID,riders,results,[totals],IF(rider_ID="","",XMATCH(rider_ID,TAKE(SORT(HSTACK(riders,IF(ISOMITTED(totals),BYROW(results,LAMBDA(x,SUM(x))),totals),WRAPROWS(SORTBY(TOCOL(results),TOCOL(IFNA(SEQUENCE(ROWS(results)),results)),1,TOCOL(results),-1),COLUMNS(results))),SEQUENCE(,1+COLUMNS(results),2),-1),,1))))Then, in cell X6, input the following formula and copy/drag it down to cell X52:
=RiderRank(B6,$B$6:$B$52,$E$6:$S$52,$T$6:$T$52)You should then be able to sort the data by column X ("Rank").
djclements​ is absolutely right, you can't sort by a spill range. There is a simple 'fix' to that to make it row specific but I think the more concerning issue is the #Name? error. Do you have an older version of Excel? Either way a very simple solution is to add 2 helper columns: Max and MaxCount and then do a sort using 3 levels: Total Score, then Max, then Max Count:
if your version of excel doesn't allow this 'advanced' sort (I can't recall what was available back then) you can create a single 'ranking' by combining the total score, max, and max count something like:
=1000000*[@[Total Score]]+1000*MAX(Table1[@[1]:[13]])+COUNTIF(Table1[@[1]:[13]],MAX(Table1[@[1]:[13]]))that is based on making the range a table but if you prefer cell references it would be:
=1000000*R5+1000*MAX(E5:Q5)+COUNTIF(E5:Q5,MAX(E5:Q5))
