How to independently sort multiple columns of this SEQUENCE output without effecting the positions of the columns and of the blanks?

R

Rnkhch

Well-known Member
Joined
Apr 28, 2018
Messages
576
Office Version
  1. 365
Platform
  1. Windows
Hello,

This question is related to my previous question below 😀:
www.mrexcel.com

How to generate the specified sequence of numbers based on incrementing portions of the sequence

Hello, Here is a rather complex sequence question 😀 Suppose I have the following partial LET function: =LET( b, {2,1,3,4,2}, c, 12, operation ) The numbers in b indicate the count of numbers in each piece of the final sequence. The first piece starts with 1, and each piece after the...
www.mrexcel.com www.mrexcel.com

I took Stephen's amazing solution and spiced it up to make my LAMBDA more feature-rich, and it works really well (below) :cool: :
Excel Formula: 
=LET(counts,{5,3,2,4},break,3,start,4,step,-2,sort,1,
   b,TOROW(counts),
   c,break,
   g,IF(ISOMITTED(step),1,step),
   m,SEQUENCE(MAX(b),,,ABS(IF(g,g,1))),
   d,TOROW(IFS(m<=(b*ABS(IF(g,g,1))),IF(g<=0,SEQUENCE(MAX(b),,,g),m)+SEQUENCE(,COUNT(b),0,c)),2,1),
   i,d+(IF(ISOMITTED(start),0,start-1)),
   f,IF(sort,SORT(i,,sort,1),i),
   IF(COLUMNS(counts)>=ROWS(counts),f,TRANSPOSE(f))
)

So now in addition to just "counts" and "break", the "start" and "step" can also be specified along with a "sort" option which works with the standard SORT with "1" and "-1" as arguments. Also now the function takes a vertical input as well, such as {3;2;5}, and the result is vertical spill in a single column. And this all works really well, and the output is obviously linear.

But then I thought to spice up this formula even more :cool: and add a "stack" feature as well (where "stack" can be turned on/off) in order to output the multiple counts as multiple columns (or rows in the case of vertical input). This, of course, requires that the "d" variable be taken out of TOROW. But I was not able to get the sorting to work 🧠. I tried both 1) to play with SORT parameters and/or 2) to play with the SEQUENCE components of "d" to get the sorting to work for stacked output, but so far I've been unsuccessful 🧐. Here is the incomplete formula for the stacked version (I haven't coded the actual stacking mechanism yet):

Excel Formula: 
=LET(counts,{5,3,2,4},break,3,start,4,step,-2,sort,,
   b,TOROW(counts),
   c,break,
   g,IF(ISOMITTED(step),1,step),
   m,SEQUENCE(MAX(b),,,ABS(IF(g,g,1))),
   d,IFS(m<=(b*ABS(IF(g,g,1))),IF(g<=0,SEQUENCE(MAX(b),,,g),m)+SEQUENCE(,COUNT(b),0,c)),
   i,d+(IF(ISOMITTED(start),0,start-1)),
   f,IFERROR(IF(sort,SORT(i,,sort,1),i),""),
   IF(COLUMNS(counts)>=ROWS(counts),f,TRANSPOSE(f))
)

I thought that the most reasonable sort for stack output would be to sort all columns independently, so the question is what's the best way to do this kind of sorting? I have also included some examples in the XL2BB below.

Thanks for any input 🤗

post.xlsx
ABCDEFGHIJKLMNOPQRST
1
2sort: omitted420-2-4753108131197
3sort: 1-4-2023457789101113
4sort: -1131110987754320-2-4
5
6sort:omittedsort:1(intended output)sort:-1(intended output)
7471013-4387471013
825811-2510925811
90390711039
10-27213-27
11-44-4
12
13
14sort: omittedsort: 1sort: -1sort:omitted
154-413420-2-4
162-211753
170010108
18-229131197
19-438
20747sort:1(intended output)
21557-4-2024
22375357
231074810
24883791113
251392
2611100sort:-1(intended output)
27911-2420-2-4
28713-4753
29108
30131197
31
32
33sort: omitted-5-3.75-1.98-0.211.56-2.5-0.731.04-1.250.522.294.065.837.601.77
34sort: 1-5-3.75-2.5-1.98-1.25-0.73-0.2100.521.041.561.772.294.065.837.6
35sort: -17.65.834.062.291.771.561.040.520-0.21-0.73-1.25-1.98-2.5-3.75-5
36
37sort:omittedsort:1(intended output)sort:-1(intended output)
38-5-3.75-2.5-1.250-5-3.75-2.5-1.250-51.561.047.61.77
39-1.98-0.730.521.77-1.98-0.730.521.77-0.21-0.735.830
40-0.211.042.29-0.211.042.29-1.98-2.54.06
411.564.061.564.06-3.752.29
425.835.830.52
437.67.6-1.25
44
45
46sort: omittedsort: 1sort: -1sort:omitted
47-5-57.6-5
48-3.75-3.755.83-3.75-1.98-0.211.56
49-1.98-2.54.06-2.5-0.731.04
50-0.21-1.982.29-1.250.522.294.065.837.6
511.56-1.251.7701.77
52-2.5-0.731.56
53-0.73-0.211.04sort:1(intended output)
541.0400.52-5
55-1.250.520-3.75-1.98-0.211.56
560.521.04-0.21-2.5-0.731.04
572.291.56-0.73-1.250.522.294.065.837.6
584.061.77-1.2501.77
595.832.29-1.98
607.64.06-2.5sort:-1(intended output)
6105.83-3.75-5
621.777.6-51.56-0.21-1.98-3.75
631.04-0.73-2.5
647.65.834.062.290.52-1.25
651.770
66
Sheet1
Cell Formulas
RangeFormula
C2:P2C2=LET(counts,{5,3,2,4},break,3,start,4,step,-2,sort,, b,TOROW(counts), c,break, g,IF(ISOMITTED(step),1,step), m,SEQUENCE(MAX(b),,,ABS(IF(g,g,1))), d,TOROW(IFS(m<=(b*ABS(IF(g,g,1))),IF(g<=0,SEQUENCE(MAX(b),,,g),m)+SEQUENCE(,COUNT(b),0,c)),2,1), i,d+(IF(ISOMITTED(start),0,start-1)), f,IF(sort,SORT(i,,sort,1),i), IF(COLUMNS(counts)>=ROWS(counts),f,TRANSPOSE(f)) )
C3:P3C3=LET(counts,{5,3,2,4},break,3,start,4,step,-2,sort,1, b,TOROW(counts), c,break, g,IF(ISOMITTED(step),1,step), m,SEQUENCE(MAX(b),,,ABS(IF(g,g,1))), d,TOROW(IFS(m<=(b*ABS(IF(g,g,1))),IF(g<=0,SEQUENCE(MAX(b),,,g),m)+SEQUENCE(,COUNT(b),0,c)),2,1), i,d+(IF(ISOMITTED(start),0,start-1)), f,IF(sort,SORT(i,,sort,1),i), IF(COLUMNS(counts)>=ROWS(counts),f,TRANSPOSE(f)) )
C4:P4C4=LET(counts,{5,3,2,4},break,3,start,4,step,-2,sort,-1, b,TOROW(counts), c,break, g,IF(ISOMITTED(step),1,step), m,SEQUENCE(MAX(b),,,ABS(IF(g,g,1))), d,TOROW(IFS(m<=(b*ABS(IF(g,g,1))),IF(g<=0,SEQUENCE(MAX(b),,,g),m)+SEQUENCE(,COUNT(b),0,c)),2,1), i,d+(IF(ISOMITTED(start),0,start-1)), f,IF(sort,SORT(i,,sort,1),i), IF(COLUMNS(counts)>=ROWS(counts),f,TRANSPOSE(f)) )
C7:F11,M7:P11C7=LET(counts,{5,3,2,4},break,3,start,4,step,-2,sort,, b,TOROW(counts), c,break, g,IF(ISOMITTED(step),1,step), m,SEQUENCE(MAX(b),,,ABS(IF(g,g,1))), d,IFS(m<=(b*ABS(IF(g,g,1))),IF(g<=0,SEQUENCE(MAX(b),,,g),m)+SEQUENCE(,COUNT(b),0,c)), i,d+(IF(ISOMITTED(start),0,start-1)), f,IFERROR(IF(sort,SORT(i,,sort,1),i),""), IF(COLUMNS(counts)>=ROWS(counts),f,TRANSPOSE(f)) )
C15:C28C15=LET(counts,{5;3;2;4},break,3,start,4,step,-2,sort,, b,TOROW(counts), c,break, g,IF(ISOMITTED(step),1,step), m,SEQUENCE(MAX(b),,,ABS(IF(g,g,1))), d,TOROW(IFS(m<=(b*ABS(IF(g,g,1))),IF(g<=0,SEQUENCE(MAX(b),,,g),m)+SEQUENCE(,COUNT(b),0,c)),2,1), i,d+(IF(ISOMITTED(start),0,start-1)), f,IF(sort,SORT(i,,sort,1),i), IF(COLUMNS(counts)>=ROWS(counts),f,TRANSPOSE(f)) )
D15:D28D15=LET(counts,{5;3;2;4},break,3,start,4,step,-2,sort,1, b,TOROW(counts), c,break, g,IF(ISOMITTED(step),1,step), m,SEQUENCE(MAX(b),,,ABS(IF(g,g,1))), d,TOROW(IFS(m<=(b*ABS(IF(g,g,1))),IF(g<=0,SEQUENCE(MAX(b),,,g),m)+SEQUENCE(,COUNT(b),0,c)),2,1), i,d+(IF(ISOMITTED(start),0,start-1)), f,IF(sort,SORT(i,,sort,1),i), IF(COLUMNS(counts)>=ROWS(counts),f,TRANSPOSE(f)) )
E15:E28E15=LET(counts,{5;3;2;4},break,3,start,4,step,-2,sort,-1, b,TOROW(counts), c,break, g,IF(ISOMITTED(step),1,step), m,SEQUENCE(MAX(b),,,ABS(IF(g,g,1))), d,TOROW(IFS(m<=(b*ABS(IF(g,g,1))),IF(g<=0,SEQUENCE(MAX(b),,,g),m)+SEQUENCE(,COUNT(b),0,c)),2,1), i,d+(IF(ISOMITTED(start),0,start-1)), f,IF(sort,SORT(i,,sort,1),i), IF(COLUMNS(counts)>=ROWS(counts),f,TRANSPOSE(f)) )
G15:K18G15=LET(counts,{5;3;2;4},break,3,start,4,step,-2,sort,, b,TOROW(counts), c,break, g,IF(ISOMITTED(step),1,step), m,SEQUENCE(MAX(b),,,ABS(IF(g,g,1))), d,IFS(m<=(b*ABS(IF(g,g,1))),IF(g<=0,SEQUENCE(MAX(b),,,g),m)+SEQUENCE(,COUNT(b),0,c)), i,d+(IF(ISOMITTED(start),0,start-1)), f,IFERROR(IF(sort,SORT(i,,sort,1),i),""), IF(COLUMNS(counts)>=ROWS(counts),f,TRANSPOSE(f)) )
C33:R33C33=LET(counts,{1,4,3,6,2},break,1.25,start,-5,step,1.77,sort,, b,TOROW(counts), c,break, g,IF(ISOMITTED(step),1,step), m,SEQUENCE(MAX(b),,,ABS(IF(g,g,1))), d,TOROW(IFS(m<=(b*ABS(IF(g,g,1))),IF(g<=0,SEQUENCE(MAX(b),,,g),m)+SEQUENCE(,COUNT(b),0,c)),2,1), i,d+(IF(ISOMITTED(start),0,start-1)), f,IF(sort,SORT(i,,sort,1),i), IF(COLUMNS(counts)>=ROWS(counts),f,TRANSPOSE(f)) )
C34:R34C34=LET(counts,{1,4,3,6,2},break,1.25,start,-5,step,1.77,sort,1, b,TOROW(counts), c,break, g,IF(ISOMITTED(step),1,step), m,SEQUENCE(MAX(b),,,ABS(IF(g,g,1))), d,TOROW(IFS(m<=(b*ABS(IF(g,g,1))),IF(g<=0,SEQUENCE(MAX(b),,,g),m)+SEQUENCE(,COUNT(b),0,c)),2,1), i,d+(IF(ISOMITTED(start),0,start-1)), f,IF(sort,SORT(i,,sort,1),i), IF(COLUMNS(counts)>=ROWS(counts),f,TRANSPOSE(f)) )
C35:R35C35=LET(counts,{1,4,3,6,2},break,1.25,start,-5,step,1.77,sort,-1, b,TOROW(counts), c,break, g,IF(ISOMITTED(step),1,step), m,SEQUENCE(MAX(b),,,ABS(IF(g,g,1))), d,TOROW(IFS(m<=(b*ABS(IF(g,g,1))),IF(g<=0,SEQUENCE(MAX(b),,,g),m)+SEQUENCE(,COUNT(b),0,c)),2,1), i,d+(IF(ISOMITTED(start),0,start-1)), f,IF(sort,SORT(i,,sort,1),i), IF(COLUMNS(counts)>=ROWS(counts),f,TRANSPOSE(f)) )
C38:G43C38=LET(counts,{1,4,3,6,2},break,1.25,start,-5,step,1.77,sort,, b,TOROW(counts), c,break, g,IF(ISOMITTED(step),1,step), m,SEQUENCE(MAX(b),,,ABS(IF(g,g,1))), d,IFS(m<=(b*ABS(IF(g,g,1))),IF(g<=0,SEQUENCE(MAX(b),,,g),m)+SEQUENCE(,COUNT(b),0,c)), i,d+(IF(ISOMITTED(start),0,start-1)), f,IFERROR(IF(sort,SORT(i,,sort,1),i),""), IF(COLUMNS(counts)>=ROWS(counts),f,TRANSPOSE(f)) )
C47:C62C47=LET(counts,{1;4;3;6;2},break,1.25,start,-5,step,1.77,sort,, b,TOROW(counts), c,break, g,IF(ISOMITTED(step),1,step), m,SEQUENCE(MAX(b),,,ABS(IF(g,g,1))), d,TOROW(IFS(m<=(b*ABS(IF(g,g,1))),IF(g<=0,SEQUENCE(MAX(b),,,g),m)+SEQUENCE(,COUNT(b),0,c)),2,1), i,d+(IF(ISOMITTED(start),0,start-1)), f,IF(sort,SORT(i,,sort,1),i), IF(COLUMNS(counts)>=ROWS(counts),f,TRANSPOSE(f)) )
D47:D62D47=LET(counts,{1;4;3;6;2},break,1.25,start,-5,step,1.77,sort,1, b,TOROW(counts), c,break, g,IF(ISOMITTED(step),1,step), m,SEQUENCE(MAX(b),,,ABS(IF(g,g,1))), d,TOROW(IFS(m<=(b*ABS(IF(g,g,1))),IF(g<=0,SEQUENCE(MAX(b),,,g),m)+SEQUENCE(,COUNT(b),0,c)),2,1), i,d+(IF(ISOMITTED(start),0,start-1)), f,IF(sort,SORT(i,,sort,1),i), IF(COLUMNS(counts)>=ROWS(counts),f,TRANSPOSE(f)) )
E47:E62E47=LET(counts,{1;4;3;6;2},break,1.25,start,-5,step,1.77,sort,-1, b,TOROW(counts), c,break, g,IF(ISOMITTED(step),1,step), m,SEQUENCE(MAX(b),,,ABS(IF(g,g,1))), d,TOROW(IFS(m<=(b*ABS(IF(g,g,1))),IF(g<=0,SEQUENCE(MAX(b),,,g),m)+SEQUENCE(,COUNT(b),0,c)),2,1), i,d+(IF(ISOMITTED(start),0,start-1)), f,IF(sort,SORT(i,,sort,1),i), IF(COLUMNS(counts)>=ROWS(counts),f,TRANSPOSE(f)) )
G47:L51G47=LET(counts,{1;4;3;6;2},break,1.25,start,-5,step,1.77,sort,, b,TOROW(counts), c,break, g,IF(ISOMITTED(step),1,step), m,SEQUENCE(MAX(b),,,ABS(IF(g,g,1))), d,IFS(m<=(b*ABS(IF(g,g,1))),IF(g<=0,SEQUENCE(MAX(b),,,g),m)+SEQUENCE(,COUNT(b),0,c)), i,d+(IF(ISOMITTED(start),0,start-1)), f,IFERROR(IF(sort,SORT(i,,sort,1),i),""), IF(COLUMNS(counts)>=ROWS(counts),f,TRANSPOSE(f)) )
Dynamic array formulas.
 
Sort by date Sort by votes
Awesome, thanks 🤗 I was overthinking it 😁 I'll finalize the LAMBDA and ask your opinion before posting it in the LAMBDA section :cool:
 
Upvote 0

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Hi @djclements

PART 1)
I had a lot of fun with this 😅 and finished my initial plan for the LAMBDA which looks like this:
LAMBDA(reference, sort_order, sort_by, output,LET(...))

where sort_by is on/off switch for sorting by columns (off) or by rows (on), and output gets three arguments: 0 for errors staying where they are; 1 for errors piling at the bottom or right of the spill (depending on sort_by; and 2 for errors piling at the top or left (again depending on sort_by)

Here is the LET:
a) I combined your s variables from post #6 and #10 formulas into a single s where I used a bunch of IFs to control vertical vs. horizontal sorting; this is for output 0
b) I combined the modified versions of the two f variables of your post #2 into a single f; this is for outputs 1 and 2
c) I added support for all errors by changing the h*w in your posts #6 and #10 EXPAND(SORTBY(v,b,1,v,o),h*w) term into VSTACK(SORTBY(v,b,1,v,o),FILTER(x,NOT(ISNUMBER(x)))). So basically, I replaced 'EXPAND which simply adds #NAs to the end of the list' by 'all the errors that are filtered out of the linearized reference' :cool:
Excel Formula: 
=LET(
   reference,B2:F8,
   sort_order,-1,
   sort_by,0,
   output,1,
   i,reference,
   p,sort_by,
   z,output,
   o,sort_order,
   x,TOCOL(i,,IF(OR(z,p),0,1)),
   w,COLUMNS(i),
   h,ROWS(i),
   c,SEQUENCE(,w),
   r,SEQUENCE(h),
   e,ISERROR(i),
   t,NOT(e),
   q,IF(p,0,1),
   v,TOCOL(i,3,q),
   b,TOCOL(IFS(t,IF(p,r,c)),3,q),
   s,SORTBY(VSTACK(SORTBY(v,b,1,v,o),FILTER(x,NOT(ISNUMBER(x)))),
      VSTACK(b,TOCOL(IFS(e,IF(p,r,c)),3,q)),1,
      VSTACK(TOCOL(IFS(t,IF(p,c,r)),3,q),TOCOL(IFS(e,IF(p,c,r)),3,q)),1
   ),
   f,IF(
      p,
      WRAPROWS(SORTBY(x,TOCOL(IF({1},r,i)),1,TOCOL(e),SWITCH(z,1,1,2,-1),x,o),w),
      WRAPCOLS(SORTBY(x,TOCOL(IF({1},c,i)),1,TOCOL(e),SWITCH(z,1,1,2,-1),x,o),h)
   ),
   y,IF(p,WRAPROWS(s,w),WRAPCOLS(s,h)),
   IF(z,f,y)
)

This works really well :cool: (so no questions for part 1 😁, but please let me know if anything can be enhanced here especially what I did for the EXPAND replacement)

PART 1 example:
test.xlsx
ABCDEFGHIJKLM
1PART 1 ex.
2111213141717273747
321222#VALUE!#REF!616263646
43#N/A233343515253445
5414243444414243344
651525#DIV/0!45312233143
761626364621122#VALUE!41
87172737471#N/A21#DIV/0!#REF!
9
2
Cell Formulas
RangeFormula
H2:L8H2=LET(reference,B2:F8,sort_order,-1,sort_by,0,output,1,i,reference,p,sort_by,z,output,o,sort_order,x,TOCOL(i,,IF(OR(z,p),0,1)),w,COLUMNS(i),h,ROWS(i),c,SEQUENCE(,w),r,SEQUENCE(h),e,ISERROR(i),t,NOT(e),q,IF(p,0,1),v,TOCOL(i,3,q),b,TOCOL(IFS(t,IF(p,r,c)),3,q),s,SORTBY(VSTACK(SORTBY(v,b,1,v,o),FILTER(x,NOT(ISNUMBER(x)))),VSTACK(b,TOCOL(IFS(e,IF(p,r,c)),3,q)),1,VSTACK(TOCOL(IFS(t,IF(p,c,r)),3,q),TOCOL(IFS(e,IF(p,c,r)),3,q)),1),f,IF(p,WRAPROWS(SORTBY(x,TOCOL(IF({1},r,i)),1,TOCOL(e),SWITCH(z,1,1,2,-1),x,o),w),WRAPCOLS(SORTBY(x,TOCOL(IF({1},c,i)),1,TOCOL(e),SWITCH(z,1,1,2,-1),x,o),h)),y,IF(p,WRAPROWS(s,w),WRAPCOLS(s,h)),IF(z,f,y))
E6E6=1/0
Dynamic array formulas.





PART 2)
But last minute, I thought to enhance my LAMBDA even more and added support for not just dealing with errors, but also blanks and logicals in case the reference contains those too (I tested the native SORT function and noticed that only numbers and texts are really sortable, so it makes sense to leave everything else, i.e. blanks and logicals and errors, out of the process).

Output 0 is working well, and I almost managed to get output 1 and 2 to work (minus a tiny issue 😅). I changed only i, e, v, and s variables a bit:
Excel Formula: 
=LET(
   reference,B2:F9,
   sort_order,-1,
   sort_by,1,
   output,0,
   i,IF(LEN(TRIM(reference))=0,"",reference),
   p,sort_by,
   z,output,
   o,sort_order,
   x,TOCOL(i,,IF(OR(z,p),0,1)),
   w,COLUMNS(i),
   h,ROWS(i),
   c,SEQUENCE(,w),
   r,SEQUENCE(h),
   e,ISERROR(i)+(IFERROR(i,"")="")+ISLOGICAL(i),
   t,NOT(e),
   q,IF(p,0,1),
   v,LET(u,TOCOL(i,3,q),FILTER(u,NOT((IFERROR(u,"")="")+ISLOGICAL(u)))),
   b,TOCOL(IFS(t,IF(p,r,c)),3,q),
   s,SORTBY(VSTACK(SORTBY(v,b,1,v,o),FILTER(x,ISERROR(x)+(IFERROR(x,"")="")+ISLOGICAL(x))),
      VSTACK(b,TOCOL(IFS(e,IF(p,r,c)),3,q)),1,
      VSTACK(TOCOL(IFS(t,IF(p,c,r)),3,q),TOCOL(IFS(e,IF(p,c,r)),3,q)),1),
   f,IF(
      p,
      WRAPROWS(SORTBY(x,TOCOL(IF({1},r,i)),1,TOCOL(e),SWITCH(z,1,1,2,-1),x,o),w),
      WRAPCOLS(SORTBY(x,TOCOL(IF({1},c,i)),1,TOCOL(e),SWITCH(z,1,1,2,-1),x,o),h
   )
   ),
   y,IF(p,WRAPROWS(s,w),WRAPCOLS(s,h)),
   IF(z,f,y))

I used the LEN(TRIM(reference))=0 check to make sure any type of blank including space blanks are supported.

Q1: for outputs 1 and 2, the errors stay rock solid at their places (i.e. either at the top/bottom or right/left depending on sort_by) when I flip sort_order between 1 and -1, and blanks seem to stay rock solid as well as long as no logicals are present. But as soon as logicals come in, the blanks and logicals don't stay where they are and move around in their own section (highlighted in the examples below). Any idea how I can fix this? 🙃

PART 2 example:
test.xlsx
ABCDEFGHIJKLM
1PART 2 ex.
2111213141111213141
3212FALSE32#REF!212243243
4book13#VALUE!43413253444
54#N/A243444515263745
651525 45716283846
7note1626#DIV/0!4681747
8717TRUE3747book18FALSE#VALUE!48
9818283848note#N/ATRUE#DIV/0!#REF!
10
11note18283848
12book17263747
13816253446
14715243245
15513213144
16412TRUE43
17211FALSE#VALUE!41
181#N/A#DIV/0!#REF!
19
3
Cell Formulas
RangeFormula
H2:L9H2=LET(reference,B2:F9,sort_order,1,sort_by,,output,1,i,IF(LEN(TRIM(reference))=0,"",reference),p,sort_by,z,output,o,sort_order,x,TOCOL(i,,IF(OR(z,p),0,1)),w,COLUMNS(i),h,ROWS(i),c,SEQUENCE(,w),r,SEQUENCE(h),e,ISERROR(i)+(IFERROR(i,"")="")+ISLOGICAL(i),t,NOT(e),q,IF(p,0,1),v,LET(u,TOCOL(i,3,q),FILTER(u,NOT((IFERROR(u,"")="")+ISLOGICAL(u)))),b,TOCOL(IFS(t,IF(p,r,c)),3,q),s,SORTBY(VSTACK(SORTBY(v,b,1,v,o),FILTER(x,ISERROR(x)+(IFERROR(x,"")="")+ISLOGICAL(x))),VSTACK(b,TOCOL(IFS(e,IF(p,r,c)),3,q)),1,VSTACK(TOCOL(IFS(t,IF(p,c,r)),3,q),TOCOL(IFS(e,IF(p,c,r)),3,q)),1),f,IF(p,WRAPROWS(SORTBY(x,TOCOL(IF({1},r,i)),1,TOCOL(e),SWITCH(z,1,1,2,-1),x,o),w),WRAPCOLS(SORTBY(x,TOCOL(IF({1},c,i)),1,TOCOL(e),SWITCH(z,1,1,2,-1),x,o),h)),y,IF(p,WRAPROWS(s,w),WRAPCOLS(s,h)),IF(z,f,y))
E7E7=1/0
H11:L18H11=LET(reference,B2:F9,sort_order,-1,sort_by,,output,1,i,IF(LEN(TRIM(reference))=0,"",reference),p,sort_by,z,output,o,sort_order,x,TOCOL(i,,IF(OR(z,p),0,1)),w,COLUMNS(i),h,ROWS(i),c,SEQUENCE(,w),r,SEQUENCE(h),e,ISERROR(i)+(IFERROR(i,"")="")+ISLOGICAL(i),t,NOT(e),q,IF(p,0,1),v,LET(u,TOCOL(i,3,q),FILTER(u,NOT((IFERROR(u,"")="")+ISLOGICAL(u)))),b,TOCOL(IFS(t,IF(p,r,c)),3,q),s,SORTBY(VSTACK(SORTBY(v,b,1,v,o),FILTER(x,ISERROR(x)+(IFERROR(x,"")="")+ISLOGICAL(x))),VSTACK(b,TOCOL(IFS(e,IF(p,r,c)),3,q)),1,VSTACK(TOCOL(IFS(t,IF(p,c,r)),3,q),TOCOL(IFS(e,IF(p,c,r)),3,q)),1),f,IF(p,WRAPROWS(SORTBY(x,TOCOL(IF({1},r,i)),1,TOCOL(e),SWITCH(z,1,1,2,-1),x,o),w),WRAPCOLS(SORTBY(x,TOCOL(IF({1},c,i)),1,TOCOL(e),SWITCH(z,1,1,2,-1),x,o),h)),y,IF(p,WRAPROWS(s,w),WRAPCOLS(s,h)),IF(z,f,y))
Dynamic array formulas.






PART 3)
While I was typing this, I thought of one more feature to add to my LAMBDA, and that is sorting different columns (or rows) independently of each other 🙃 so that the user would enter either a single sort_order (which would apply to all columns/rows) or multiple sort_orders

Q2: I haven't done any coding for this, but I was thinking to calculate both sort_orders (1 and -1) and then pick the correct column (or row) for each sort_order that user enters such as {1,1,-1,1,-1}. Would this be the best way of doing this, or can you do some magic to the existing code to handle this automatically? 🙃


Thanks a lot for all your help 🤗
 
Upvote 0
@Rnkhch A couple of comments...

The [ignore] argument of TOCOL should really only be set to 2 - ignore errors in this situation, because it needs to be consistent with the logical_test1 argument of the IFS function. Also, TOCOL does not treat empty strings ("") as blanks, so this would need to be handled in the logical_test1 argument anyways.

To overcome the output issue you've described (when set to 1 or 2), a different approach is needed. Instead of including the ignored values (blanks, errors and logical) in one of the sort fields, you could simply reverse the VSTACK order when the output is set to 2, and only sort by the final [by_array2] argument when the output is set to 0 (or omitted).

It might also be a good idea to have your own [ignore] argument where the user could choose which values are to be excluded from sorting. For example:

Excel Formula: 
=LAMBDA(array,[ignore],[ignore_mode],[sort_order],[sort_by_row],
    LET(
        a, IF(TRIM(array)="","",array),
        m, ignore_mode,
        o, sort_order,
        c, NOT(sort_by_row),
        cλ, LAMBDA(t,f,IF(c,t,f)),
        h, ROWS(a),
        w, COLUMNS(a),
        i, SEQUENCE(h),
        j, SEQUENCE(,w),
        x, CHOOSE(
            ignore+1,
            0,
            IFERROR(a="",0),
            ISERROR(a),
            ISLOGICAL(a),
            IFERROR(a,"")="",
            IFERROR(a="",0)+ISLOGICAL(a),
            ISERROR(a)+ISLOGICAL(a),
            (IFERROR(a,"")="")+ISLOGICAL(a)
        ),
        s, IF(
            OR(x),
            LET(
                y, NOT(x),
                vλ, LAMBDA(arr,incl,FILTER(TOCOL(arr,,c),TOCOL(incl,,c))),
                nλ, LAMBDA(arr,incl,TOCOL(IFS(incl,arr),2,c)),
                sλ, LAMBDA(one,two,IF(m=2,VSTACK(two,one),VSTACK(one,two))),
                _v0, vλ(a,y),
                _n1, nλ(cλ(j,i),y),
                _n2, nλ(cλ(j,i),x),
                _v1, SORTBY(_v0,_n1,1,_v0,o),
                _v2, vλ(a,x),
                SORTBY(
                    sλ(_v1,_v2),
                    sλ(_n1,_n2),1,
                    IF(m,EXPAND(1,h*w,,1),VSTACK(nλ(cλ(i,j),y),nλ(cλ(i,j),x))),1
                )
            ),
            LET(
                v, TOCOL(a,,c),
                SORTBY(v,TOCOL(IF({1},cλ(j,i),a),,c),1,v,o)
            )
        ),
        cλ(WRAPCOLS(s,h),WRAPROWS(s,w))
    )
)

[ignore]:
0 - none
1 - blanks
2 - errors
3 - logical
4 - blanks and errors
5 - blanks and logical
6 - errors and logical
7 - blanks, errors and logical

[ignore_mode]:
0 - keep in place
1 - flush bottom/right
2 - flush top/left

[sort_order]:
1 - ascending
-1 - decending

[sort_by_row]:
FALSE - sort by column
TRUE - sort by row

I'm sure there's a lot of ways this could be modified and/or optimized, but for the basic tests I put it through, it seemed to work as expected.

Lastly, if you wanted to allow an array of values to set the [sort_order] for each row/column, you'd probably have to sort the whole thing twice (once ascending and once descending), then use something like IF(TOCOL(IF({1},sort_order=1,array),,c),ascending,descending), although it may be a bit more complicated than that.
 
Upvote 0
Amazing 😮 🐄 Thanks much 🤗 You are absolute number one, not relative 🙃

I'll try to tackle the array of [sort_order] values and see what I can do. I'll check with you 🙃
 
Upvote 0
@Rnkhch My first attempt at supporting a [sort_order] array...

Excel Formula: 
=LAMBDA(array,[ignore],[ignore_mode],[sort_order],[sort_by_row],
    LET(
        a, IF(TRIM(array)="","",array),
        m, ignore_mode,
        o, sort_order,
        c, NOT(sort_by_row),
        cλ, LAMBDA(t,f,IF(c,t,f)),
        h, ROWS(a),
        w, COLUMNS(a),
        i, SEQUENCE(h),
        j, SEQUENCE(,w),
        x, CHOOSE(
            ignore+1,
            0,
            IFERROR(a="",0),
            ISERROR(a),
            ISLOGICAL(a),
            IFERROR(a,"")="",
            IFERROR(a="",0)+ISLOGICAL(a),
            ISERROR(a)+ISLOGICAL(a),
            (IFERROR(a,"")="")+ISLOGICAL(a)
        ),
        s, IF(
            OR(x),
            LET(
                y, NOT(x),
                vλ, LAMBDA(arr,incl,FILTER(TOCOL(arr,,c),TOCOL(incl,,c))),
                nλ, LAMBDA(arr,incl,TOCOL(IFS(incl,arr),2,c)),
                sλ, LAMBDA(one,two,IF(m=2,VSTACK(two,one),VSTACK(one,two))),
                _v1, vλ(a,y),
                _v2, vλ(a,x),
                _n1, nλ(cλ(j,i),y),
                _b1, sλ(_n1,nλ(cλ(j,i),x)),
                _b2, IF(m,EXPAND(1,h*w,,1),VSTACK(nλ(cλ(i,j),y),nλ(cλ(i,j),x))),
                IF(
                    TYPE(o)=64,
                    SWITCH(
                        TOCOL(IF({1},cλ(TOROW(o),TOCOL(o)),a),,c),
                        1, SORTBY(sλ(SORTBY(_v1,_n1,1,_v1,1),_v2),_b1,1,_b2,1),
                        -1, SORTBY(sλ(SORTBY(_v1,_n1,1,_v1,-1),_v2),_b1,1,_b2,1)
                    ),
                    SORTBY(sλ(SORTBY(_v1,_n1,1,_v1,o),_v2),_b1,1,_b2,1)
                )
            ),
            LET(
                v, TOCOL(a,,c),
                n, TOCOL(IF({1},cλ(j,i),a),,c),
                IF(
                    TYPE(o)=64,
                    SWITCH(
                        TOCOL(IF({1},cλ(TOROW(o),TOCOL(o)),a),,c),
                        1, SORTBY(v,n,1,v,1),
                        -1, SORTBY(v,n,1,v,-1)
                    ),
                    SORTBY(v,n,1,v,o)
                )
            )
        ),
        cλ(WRAPCOLS(s,h),WRAPROWS(s,w))
    )
)

When sorting by column, the [sort_order] array would need to contain the same number of elements as the array has columns; likewise, when sorting by row, the [sort_order] array would need to contain the same number of elements as the array has rows. If you wanted to allow a mismatched number of elements and return the original unsorted data for any rows/columns where no sort order was specified, you could use EXPAND-TAKE to ensure the correct number of elements are present. For example, the two SWITCH statements shown above could be modified as follows:

Excel Formula: 
                    SWITCH(
                        TOCOL(IF({1},cλ(EXPAND(TAKE(TOROW(o),,w),,w,0),EXPAND(TAKE(TOCOL(o),h),h,,0)),a),,c),
                        1, SORTBY(sλ(SORTBY(_v1,_n1,1,_v1,1),_v2),_b1,1,_b2,1),
                        -1, SORTBY(sλ(SORTBY(_v1,_n1,1,_v1,-1),_v2),_b1,1,_b2,1),
                        TOCOL(a,,c)
                    ),

                    SWITCH(
                        TOCOL(IF({1},cλ(EXPAND(TAKE(TOROW(o),,w),,w,0),EXPAND(TAKE(TOCOL(o),h),h,,0)),a),,c),
                        1, SORTBY(v,n,1,v,1),
                        -1, SORTBY(v,n,1,v,-1),
                        v
                    ),

Again, I didn't put this through its paces, but initial tests appear to work as expected. Enjoy!
 
Upvote 0
@djclements Phenomenal 😮 🐄 Exactly what I wanted 🎉 Thanks much 🤗

I did a bunch of tests, and it works pretty well. It is also pretty fast; for a grid 1,000 x 1,000 (one million cells) it takes between ~4-8 seconds on my system depending on which parameters/arguments are used.

I also noticed that in the [sort_order] array, I can skip values from the middle of the array by putting blank, 0, or pretty much anything else besides 1 and -1, which is great. I'll probably restrict it to just blank ("") in the final post. Also it ignores any additional sort_order numbers than the actual number of columns/rows, which is also great 🎉

I'll start working on posting it in the LAMBDA forum and hopefully post it by the end of the weekend. In case you think of anything else (edits or new features) please let me know 🙃

Also I was thinking to make the [sort_order] plural as [sort_orders] so the user can readily see they could include an array too if they wanted.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Problem with BYROW function to spill filtered results
Replies
5
Views
611
Peter_SSs
Peter_SSs
P
  • Solved
Lambda to count pairs and output array based on criteria
2
Replies
19
Views
454
Phantom1
P
P
  • Question Question
Creating Sub Totals for a Sort, Unique, Filter, Choose Cols dynamically
Replies
6
Views
291
ExcelToDAX
E
S
  • Question Question
Using output of LAMBDA within a function
Replies
3
Views
152
Phuoc
P
P
  • Solved
Update Equally Distributed Formula
2
Replies
19
Views
976
felixstraube
F

Forum statistics

Threads
1,222,753
Messages
6,168,011
Members
452,160
Latest member
Bekerinik

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top