Sorting/ordering values from smallest to biggest in an array-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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("