CODEWITHSUNDEEP
sortinggoogle-sheetssplitvlookupflatten
Max
Max

Reputation: 940

Sorting/ordering values from smallest to biggest in an array

I have a formula like this : =ArrayFormula(sort(INDEX($B$1:$B$10,MATCH(E1,$A$1:$A$10,0))))

in columns A:B:

    a   1
    b   2
    c   3
    d   4
    e   5
    f   6
    g   7
    h   8
    i   9
    j   10

and

 the data to convert in E:H
a   c   f   e
f   a   c   b
b   a   c   d

I get the following results using the above formula

in columns L:O:
1   3   6   5
6   1   3   2
2   1   3   4

My desired output is like this:

1   3   5   6
1   2   3   6
1   2   3   4

I'd like to arrange the numbers from smallest to biggest in value. I can do this with additional helper cells. but if possible i'd like to get the same result without any additional cells. can i get a little help please? thanks.

Upvotes: 1

Views: 211

Answers (3)

Tom Sharpe
Tom Sharpe

Reputation: 34180

Here's another way using Makearray with Index to get the current row and Small to get the smallest, next smallest etc. within the row:

=ArrayFormula(makearray(3,4,lambda(r,c,small(index(vlookup(E1:H3,A1:B10,2,false),r,0),c))))

enter image description here

Or you could change the order (might be a little faster) as you don't need to vlookup the entire array, just the current row:

=ArrayFormula(makearray(3,4,lambda(r,c,small(vlookup(index(E1:H3,r,0),A1:B10,2,false),c))))

It's interesting (to me at any rate) that you can interrogate the row and column number of the current cell using Map or Scan, so this is also possible:

=ArrayFormula(map(E1:H3,lambda(cell,small(vlookup(index(E1:H3,row(cell),0),A1:B10,2,false),column(cell)-column(E:E)+1))))

Thanks to @JvdV for this insight (which may be obvious to some but wasn't to me) shown here in Excel.

Upvotes: 3

TheMaster
TheMaster

Reputation: 50445

To sort by row, use SORT BYROW. But unfortunately, nested array results aren't supported in BYROW. So, we need to JOIN and SPLIT the resulting array.

=ARRAYFORMULA(SPLIT(BYROW(your_formula,LAMBDA(row,JOIN("🌆",SORT(TRANSPOSE(row))))),"🌆"))

Upvotes: 3

player0
player0

Reputation: 1

try:

=INDEX(TRIM(SPLIT(FLATTEN(QUERY(QUERY(QUERY(SPLIT(FLATTEN(E1:H3&"×​"&ROW(E1:H3)), "​"), 
 "select max(Col1) group by Col1 pivot Col2"), "offset 1", 0),,9^9)), "×")))

2

or if you want numbers:

=INDEX(IFNA(VLOOKUP(TRIM(SPLIT(FLATTEN(QUERY(QUERY(QUERY(SPLIT(FLATTEN(E1:H3&"×​"&ROW(E1:H3)), "​"), 
 "select max(Col1) group by Col1 pivot Col2"), "offset 1", 0),,9^9)), "×")), A:B, 2, 0)))

1

Upvotes: 2

Related Questions