• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sort in two columns

bines53

Active Member
Hello friends,

I want you to sort these two columns,

0​
0​
0​
2​
0​
4​
0​
6​
2​
0​
2​
2​
2​
4​
2​
6​
4​
0​
4​
2​
4​
4​
4​
6​
6​
0​
6​
2​
6​
4​
6​
6​
The minimum will appear in the first column, and the maximum in the second column.
with a sort or sortby function.


0​
0​
0​
2​
0​
4​
0​
6​
0​
2​
2​
2​
2​
4​
2​
6​
0​
4​
2​
4​
4​
4​
4​
6​
0​
6​
2​
6​
4​
6​
6​
6​

Thank you!
 
as below in cell D2, then in E2:
=MAX(A2:B2)
copied down:
80264

then if you want, right-click and drag the second pair of columns over the first pair and choose Copy here as Values only
 
Hi p45cal,

I asked for a solution with a sorting function.

I'm quite afraid that there is no solution, with sorting functions.

Thank you !
 
You can select a horizontal range and sort horizontally one row at a time:
80266

Or you can do something like this on the activesheet:
Code:
Sub blah()
For Each rw In Range("A2:B17").Rows
  rw.Sort key1:=rw, order1:=xlAscending, Orientation:=xlLeftToRight
Next rw
End Sub
 
Or maybe what you wanted all along:
80267

Later edit: Which doesn't work because it's dependent on the order of the second row.
 
Last edited:
Attached has SortRange, as well as shorter version SortRows (no options) and the equivalent in-cell lambdas.
 

Attachments

  • Chandoo48509.xlsx
    13.1 KB · Views: 7
Hi p45cal,

The lambda function is foreign to me, I haven't found time to learn, I hope to devote more effort to it. Thank you very much for your help.

David
 
@bines53
David,
Lambda is well worth the time invested. It is at the core of modern Excel and changes the way in which solutions are planned and implemented. Instead of working at a uniformly detailed level, functionality can be built at the detailed level but then combined to form solutions to conceptually more advanced problems.

Pascal.
Your combination of SMALL and MAKEARRAY works really well for this problem! My congratulations. I also note your reference to Sergei Balkan's work. Sergei is very good, well worth following. As Sergei suggests the solution to sorting by row should be
Code:
= BYROW(range, LAMBDA(r, SORT(r)))
It was only a monumentally poor piece of decision making on behalf of Microsoft that causes this to fail. The 'logic' was 'Excel has always failed to process arrays of arrays so, for the sake of backward compatibility, modern Excel should also fail'!

The problem I have found with MAKEARRAY is that one often finishes performing the same array calculation repeatedly but picking one result out at a time, so the calculation can become spectacularly inefficient if you have 1000s or columns. In the attached, I have used Thunks and VSTACK, though there is an element of overkill applied to the present problem.
Code:
= LET(
      sortedϑ, BYROW(data, SortRowλ),
      REDUCE(hdr, sortedϑ, StackRowλ)
  );
 
SortRowλ
= LAMBDA(r,LAMBDA(SORT(r,,-1,1)));

StackRowλ
= LAMBDA(acc,ϑ,VSTACK(acc,ϑ()));
The steps involve sorting row by row but using a further LAMBDA to return the result as a column of functions which take a null parameter string (thunks).. The second Lambda function expands each of these back to a row and stacks them vertically to give the result.

Overall, what should have been a simple formula is turned into an advanced programming exercise, That is not good!
 

Attachments

  • SortByRow.xlsx
    13.4 KB · Views: 5
Back
Top