Sort in two columns

[bines53]

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!

 

[p45cal]

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


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

 

[bines53]

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 !

 

[p45cal]

You can select a horizontal range and sort horizontally one row at a time:


Or you can do something like this on the activesheet:

Sub blah()
For Each rw In Range("A2:B17").Rows
  rw.Sort key1:=rw, order1:=xlAscending, Orientation:=xlLeftToRight
Next rw
End Sub

 

[p45cal]

Or maybe what you wanted all along:


Later edit: Which doesn't work because it's dependent on the order of the second row.

 

[p45cal]

So far, all I can find is this:

copied down.

 

[p45cal]

A guy called Sergei Baklan has a solution here: https://www.linkedin.com/pulse/excel-lambda-sort-range-rows-columns-sergei-baklan
It seems to work. Could be simplified if you always want to sort in the same way. It's a single cell formula which spills:

 

[bines53]

Hi p45cal,

Can you upload the file with the example you showed(sortrange)?

Thank you

 

[p45cal]

Attached has SortRange, as well as shorter version SortRows (no options) and the equivalent in-cell lambdas.

 

[bines53]

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

 

[Peter Bartholomew]

@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

= 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.

= 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!

 

[bines53]

Hi Peter,

Very nice !

Thank you!