Home » Sort Rows by Key Count

Sort Rows by Key Count

Sort the rows in columns A through H as per Sort key. If Sort key is n, then only first n cells in the row will be sorted and remaining cells will remain as it is. example – 8, 4, 5, 0, 9, 6 and Sort key is 4. Then answer would be 0, 4, 5, 8, 9, 6.

📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 223
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Sort Rows by Key Count with Power Query

Power Query solution 1 for Sort Rows by Key Count, proposed by Zoran Milokanović: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  S = Table.FromRows(
    List.Transform(
      Table.ToRows(Source), 
      each List.Sort(List.FirstN(_, List.Last(_)), {{each Number.From(_ = null)}, {each _}})
        & List.RemoveLastN(List.Skip(_, List.Last(_)))
    )
  )
in
  S
Power Query solution 2 for Sort Rows by Key Count, proposed by Luan Rodrigues: 
let
  Fonte = Tabela1, 
  add = Table.AddColumn(
    Fonte, 
    "Personalizar", 
    each [
      a = List.FirstN(Record.FieldValues(_), [Sort Key]), 
      b = if List.Sort(a){0} = null then List.Sort(List.RemoveNulls(a)) & {null} else List.Sort(a), 
      c = List.RemoveLastN(List.RemoveFirstN(Record.FieldValues(_), [Sort Key]), 1), 
      d = Table.FromRows({b & c & {[Sort Key]}}, Table.ColumnNames(Fonte))
    ][d]
  )[[Personalizar]], 
  exp = Table.ExpandTableColumn(add, "Personalizar", Table.ColumnNames(Fonte))
in
  exp
Power Query solution 3 for Sort Rows by Key Count, proposed by Alexis Olson: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  ReSort = Table.FromRows(
    List.Transform(
      Table.ToRows(Source), 
      each 
        let
          ColsToSort = List.NonNullCount(List.FirstN(_, List.Last(_)))
        in
          List.Sort(List.FirstN(_, ColsToSort)) & List.Skip(_, ColsToSort)
    ), 
    Table.ColumnNames(Source)
  )
in
  ReSort

Solving the challenge of Sort Rows by Key Count with Excel

Excel solution 1 for Sort Rows by Key Count, proposed by Bo Rydobon 🇹🇭: 
=LET(z,A2:I11,MAKEARRAY(ROWS(z),COLUMNS(z)-1,LAMBDA(r,c,LET(y,INDEX(z,r,),x,MIN(COUNT(y)-1,TAKE(y,,-1)),IF(c>x,INDEX(IF(y="","",y),c),SMALL(TAKE(y,,x),c))))))
Excel solution 2 for Sort Rows by Key Count, proposed by Bo Rydobon 🇹🇭: 
=MAP(A2:H11,LAMBDA(a,LET(r,ROWS(A2:a),c,COLUMNS(A2:a),x,INDEX(I2:I11,r),IFS(a="","",c>x,a,1,SMALL(TAKE(INDEX(A2:H11,r,),,x),c)))))
Excel solution 3 for Sort Rows by Key Count, proposed by Bo Rydobon 🇹🇭: 
=MAP(A2:H11,LAMBDA(a,LET(r,ROWS(A2:a),c,COLUMNS(A2:a),x,INDEX(I2:I11,r),IFS(a="","",c>x,a,1,SMALL(TAKE(INDEX(A2:H11,r,),,x),c)))))
Excel solution 4 for Sort Rows by Key Count, proposed by Bo Rydobon 🇹🇭: 
=LET(z,A2:I11,DROP(REDUCE(0,SEQUENCE(ROWS(z)),LAMBDA(a,n,LET(y,INDEX(z,n,),x,TAKE(y,,-1),v,HSTACK(SORT(TAKE(y,,x),,,1),DROP(y,,x)),
VSTACK(a,IF(v="","",v))))),1,-1))
Excel solution 5 for Sort Rows by Key Count, proposed by Rick Rothstein: 
=IFERROR(0+TEXTSPLIT(TEXTJOIN("/",0,BYROW(A2:I11,LAMBDA(r,TEXTJOIN("*",0,DROP(HSTACK(SORT(TAKE(r,,TAKE(r,,-1)),,,1),DROP(r,,TAKE(r,,-1))),,-1))))),"*","/"),"")
Excel solution 6 for Sort Rows by Key Count, proposed by John V.: 
=IFERROR(1/DROP(REDUCE(0,SEQUENCE(ROWS(I2:I11)),LAMBDA(a,v,VSTACK(a,LET(r,INDEX(A2:H11,v,),k,INDEX(I2:I11,v),p,SORT(TAKE(r,,k),,,1),IF(COLUMNS(r)-k,HSTACK(p,DROP(r,,k)),p))))),1)^-1,"")
Excel solution 7 for Sort Rows by Key Count, proposed by محمد حلمي: 
=DROP(REDUCE(0,I2:I11,LAMBDA(a,d,LET(v,OFFSET(d,,-8,,8),i,SORT(TAKE(v,,d),,,1),x,HSTACK(i,DROP(v,,d)),VSTACK(a,IFERROR(IF(x,x,""),i))))),1)
Excel solution 8 for Sort Rows by Key Count, proposed by محمد حلمي: 
=DROP(REDUCE(0,SEQUENCE(ROWS(A2:H11)),LAMBDA(a,d,LET(c,INDEX(A2:H11,d,),e,INDEX(I2:I11,d)>=8,n,IF(e,SORT(TOROW(IFS(e,c),2),,,1),c),VSTACK(a,IF(n,n,""))))),1)
Excel solution 9 for Sort Rows by Key Count, proposed by محمد حلمي: 
=LET(v,DROP(REDUCE(0,SEQUENCE(ROWS(A2:H11)),LAMBDA(a,d,LET(r,INDEX(A2:H11,d,),v,INDEX(I2:I11,d),VSTACK(a,HSTACK(SORT(TAKE(r,,v),,,1),IFERROR(DROP(r,,v),0)))))),1,-1),IF(v,v,""))
Excel solution 10 for Sort Rows by Key Count, proposed by محمد حلمي: 
=MAKEARRAY(ROWS(A2:H11),COLUMNS(A2:H11),LAMBDA(r,c,LET(e,INDEX(A2:H11,r,),s,INDEX(I2:I11,r),v,IFERROR(INDEX(HSTACK(SORT(TAKE(e,,s),,,1),DROP(e,,s)),,c),INDEX(SORT(e,,,1),,c)),IF(v,v,""))))
Excel solution 11 for Sort Rows by Key Count, proposed by محمد حلمي: 
=DROP(REDUCE(0,A2:A11,LAMBDA(a,d,LET(r,OFFSET(d,,,,8),v,OFFSET(d,,8),s,HSTACK(SORT(TAKE(r,,v),,,1),IFERROR(DROP(r,,v),0)),VSTACK(a,IF(s,s,""))))),1,-1)
Excel solution 12 for Sort Rows by Key Count, proposed by 🇰🇷 Taeyong Shin: 
=LET(d,IF(A2:H11,A2:H11,""),n,COLUMNS(d),MAKEARRAY(ROWS(d), n,LAMBDA(r,c,LET(Rec,INDEX(d,r,),@INDEX(SORTBY(Rec,IFS(SEQUENCE( ,n)<=INDEX(I2:I11,r),Rec)),c)))))
Excel solution 13 for Sort Rows by Key Count, proposed by Kris Jaganah: 
=LET(a,--TEXTSPLIT(ARRAYTOTEXT(BYROW(A2:I11,LAMBDA(x,LET(b,DROP(x,,-1),c,TAKE(x,,-1),d,SORT(TAKE(b,,c),,1,1),TEXTJOIN("-",1,IFERROR(HSTACK(d,TAKE(b,,c-8)),d)))))),"-",", "),IF(a=0,"",a))
Excel solution 14 for Sort Rows by Key Count, proposed by Julian Poeltl: 
=LET(S,TEXTSPLIT(TEXTJOIN("|",,BYROW(A2:I11,LAMBDA(N,LET(S,TAKE(N,,-1),A,DROP(N,,-1),IFERROR(TEXTJOIN(",",1,SORT(TAKE(A,,S),,,1),DROP(A,,S)),TEXTJOIN(",",1,SORT(TAKE(A,,S),,,1))))))),",","|"),IFERROR(--S,""))
Excel solution 15 for Sort Rows by Key Count, proposed by Timothée BLIOT: 
=IFERROR(LET(A,A2:I11,TEXTSPLIT(TEXTJOIN("/",,BYROW(A,LAMBDA(x,TEXTJOIN(":",,HSTACK(SORT(TAKE(TAKE(x,,8),,TAKE(x,,-1)),,1,1),IF(8-TAKE(x,,-1)>0,TAKE(TAKE(x,,8),,-(8-TAKE(x,,-1))),"")))))),":","/"))*1,"")
Excel solution 16 for Sort Rows by Key Count, proposed by Hussein SATOUR: 
=TEXTSPLIT(CONCAT(BYROW(A2:I11, LAMBDA(x, LET(
a, TAKE(x,,8), y, TAKE(x,,-1), b, DROP(a,,-COUNTBLANK(a)),
TEXTJOIN(",",,HSTACK(SORT(TAKE(b,,MIN(y, COUNT(a))),,,1), IFERROR(TAKE(b,, -COUNT(b)+MIN(y, COUNT(a))),"")))&"/")))), ",", "/",1,,"")
Excel solution 17 for Sort Rows by Key Count, proposed by Sunny Baggu: 
=LET(
 _tbl, A2:H11,
 _key, I2:I11,
 _r, SEQUENCE(ROWS(_key)),
 _res, DROP(
 REDUCE(
 "",
 _r,
 LAMBDA(a, v,
 VSTACK(
 a,
 IF(
 INDEX(_key, v, 1) = COLUMNS(_tbl),
 SORT(TAKE(INDEX(_tbl, v, ), , INDEX(_key, v, 1)), , , 1),
 HSTACK(
 SORT(TAKE(INDEX(_tbl, v, ), , INDEX(_key, v, 1)), , , 1),
 DROP(INDEX(_tbl, v, ), , INDEX(_key, v, 1))
 )
 )
 )
 )
 ),
 1
 ),
 IF(_res, _res, "")
)
Excel solution 18 for Sort Rows by Key Count, proposed by Pieter de B.: 
=LET(a,A2:I11,DROP(REDUCE(0,SEQUENCE(ROWS(a)),LAMBDA(x,y,IFNA(VSTACK(x,LET(d,INDEX(a,y,),k,TAKE(d,,-1),n,TOROW(DROP(d,,-1),1),s,SEQUENCE(,COLUMNS(n)),IF(s<=k,SORT(TAKE(n,,k),,,1),n))),""))),1))
Excel solution 19 for Sort Rows by Key Count, proposed by JvdV -: 
=IFERROR(0+TAKE(TEXTSPLIT(CONCAT(MAP(A2:A11,I2:I11,LAMBDA(x,y,TEXTJOIN("|",0,SORT(OFFSET(x,,,,y),,,1),OFFSET(x,,y,,9-y))))&"!"),"|","!"),10,8),"")
Excel solution 20 for Sort Rows by Key Count, proposed by Giorgi Goderdzishvili: 
=LET(
array,FILTER(A2:H2,A2:H2<>""),
srtKey,I2,
srtArray,SORT(TAKE(array,1,srtKey),1,1,TRUE),
nonSrt,
IF(srtKey<=COUNT(array),TAKE(array,1,-(COUNT(array)-srtKey)),""),
final,HSTACK(IFERROR(srtArray,""),IFERROR(nonSrt,"")),
final)
Excel solution 21 for Sort Rows by Key Count, proposed by Daniel Garzia: 
=LET(f,LAMBDA(r,LET(k,TAKE(r,,-1),i,DROP(TAKE(r,,k-9),,-1),IF(k=8,SORT(DROP(r,,-1),,,1),HSTACK(SORT(TAKE(r,,k),,,1),IF(i,i,""))))),d,A2:I11,REDUCE(f(INDEX(d,1,)),SEQUENCE(ROWS(d)-1,,2),LAMBDA(a,b,VSTACK(a,f(INDEX(d,b,))))))
Excel solution 22 for Sort Rows by Key Count, proposed by Quadri Olayinka Atharu: 
=LET(c,A2:H2,
k,I2,
a,SORT(TAKE(c,,k),,,1),
b,DROP(c,,k),
l,IF(b=0," ",b),
r,HSTACK(a,l),
IFERROR(r,SORT(c,,,k)))
Excel solution 23 for Sort Rows by Key Count, proposed by Diarmuid Early: 
= data (the vector of up to 8 inputs), k = key, and s is a sequence of 1-8.
So IF(s

&&&

Leave a Reply