Sorting/ordering values from smallest to biggest in an array

Question

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.

Make sure to add input and expected output as **text table** (NOT as IMAGE) to the question. [Click here](https://webapps.stackexchange.com/a/161855/) to create a table easily. Adding such tables greatly increases your chances of getting a elegant answer, as **it is easier to copy/paste**. If you share spreadsheets, your question maybe closed, as questions here must be [self contained](https://meta.stackoverflow.com/a/260455). Your table should be a [mre].[Your email address can also be accessed by the public](https://meta.stackoverflow.com/questions/394304/), if you share Google files. — TheMaster, Oct 15 '22 at 09:22

score 3 · Answer 1 · answered Oct 15 '22 at 09:29

3

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))))),""))

answered Oct 15 '22 at 09:29

TheMaster

45,448
6
62
85

score 3 · Answer 2 · edited Oct 17 '22 at 10:05

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))))

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.

score 2 · Accepted Answer · answered Oct 15 '22 at 09:28

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)), "×")))

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)))

Sorting/ordering values from smallest to biggest in an array

3 Answers3

Linked