AFUSBYROW

=AFUSBYROW(a,f,u,s)
a
array
f
filter argument, 0 or ignored, filters out blanks,-1,filters text,1 filters numbers
u
unique argument, 0 or ignored, no unique extraction, 1, unique values by rows, 2, all unique, unique values of whole array
s
sort argument, 0 or ignored, no sorting,1,ascending order,-1,descending order
Array Filter Unique Sort By Rows
Xlambda

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
837
Office Version
  1. 365
Platform
  1. Windows
AFUSBYROW Array Filter Unique Sort By Rows. Calls AFLAT
Great NEWS!!! Yesterday, 28Jul21, smozgur was so kind to let me know about the new released functions BYROW, BYCOL ..etc. Saw MrExcel video about them and jumped to test their functionality. Amazing functions. Then I realized they can not return array results as I wished for, and I promised yesterday that I will write ones that does, filtering, sorting, unique extraction, separate and combined, for each row.
Here it is AFUSBYROW (non recursive) ?. Was easy, I have only used the functionality of previous other old functions like AFILTER , AUNQSRT , AUNIQUE, but this time, embedded in one function only.
I am positive that future Excel functions will cover this, but till then, is fun.
The formula is larger than I wanted to be, because it has some tweaks, (lot of calculations behind the scenes, for big data arrays):
- wrong input arguments checked before any other calculation is performed
- compact nested LET in LET structure, no variables calculated if not triggered by specific input arguments
- because of AFLAT, can handle arrays that have r*c<1 048 576 values (real estate rows limitations of a spreadsheet) , For an array of 100000 rows by 10 columns , calculation time less than 4s.
Excel Formula: 
=LAMBDA(a,f,u,s,
    LET(d,SUM(MMULT({1,1,1},--(CHOOSE({1,2,3},f,u,s)={0,0,-1;-1,1,0;1,2,1}))),IF(d<>3,"check arguments",
      LET(n,ROWS(a),r,SEQUENCE(n),t,AFLAT(a),q,AFLAT(IF(a="","",r)),qf,CHOOSE({1,2},q,t),x,SWITCH(f,0,qf,-1,FILTER(qf,ISTEXT(t)),1,FILTER(qf,ISNUMBER(t))),
         y,IF(u,UNIQUE(x),x),z,SWITCH(s,0,y,1,SORT(y,{1,2}),-1,SORT(y,{1,2},{1,-1})),v,INDEX(z,,1),w,INDEX(z,,2),e,UNIQUE(w),IF(u=2,SWITCH(s,0,e,1,SORT(e),-1,SORT(e,,-1)),
           LET(x,INDEX(FREQUENCY(v,r),r),c,MAX(x),s,SEQUENCE(,c),y,SEQUENCE(n,c)/(x>=s),IFERROR(XLOOKUP(y,AFLAT(y),w,,,2),"")))))
   )
)
LAMBDA 1.1.0.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAE
1sampleIntroduction: Very first glimpse on new!! BYROW function
2132like in MrExcel video
3425=BYROW(A2:C3,LAMBDA(a,MAX(a)))=BYROW(A2:C3,LAMBDA(a,AGGREGATE(14,6,a,2)))
432second largest value of each row,
554 also works because returns a single value
6works fineworks fine
7Seems that any function that returns a single result, applied to every row of an array , will produce an array of results accordingly
8Note 1:top 2 largest values of each rows does not work, because it does not return a single result k={1,2} is triggering an array
9=BYROW(A2:C3,LAMBDA(a,LARGE(a,{1,2})))
10#CALC!
11Probably, to solve this, still will need a recursive approach. Will see the other functions potential asap.
12Covered this already with 2 types of recursive functions, 2 different methods, regarding the "engine" that "stacks" the results iteration by iteration.
13One method, found in T_A1GGH, (ARF post Task 10) uses extra arguments "ai" for "stacking" and "i" for "counting", but no other function needed ,
14and the other method, found in AAGREGATE that does not need any of extra arguments, but calls another function for "stacking" (APPEND2V)
15=T_A1GGH(A2:C3,14,6,{1,2},,)=AAGGREGATE(A2:C3,12,{1,2})
163232
175454
18Anyhow , the fact that for single results we don't need to use recursion anymore, it's a huge step. Lot of functions will be rewritten.
19I will see how I can integrate BYROW when a single result is returned, and use recursion for arrays results only when we trigger them.
20
21Note 2:SORT each row of an array, independently, also does not work (array outcome), and AFUSBYROW here tries to address this kind of functionality.
22=BYROW(A2:C3,LAMBDA(a,SORT(a)))
23#CALC!
240,0,0 only blanks removed0,2,0 all unique
25f,u,s, arguments valuessample=AFUSBYROW(H26:N31,,,)=AFUSBYROW(H26:N31,,2,)
26fus122z4az122z4az1
27-10-18bb814c148bb814c1421,2,-1 nr.only,all unique,desc.
28010d3535dd3535dz=AFUSBYROW(H26:N31,1,2,-1)
291216fg8g86fg8g8414
30textno uniquedescending995955995955a9text only,all unique,asc.
31no blanksuniqueno sortd3a344d3a34488-1,2,1
32numericall uniqueascendingb6=AFUSBYROW(H26:N31,-1,2,1)
33-1,1,-1 text,unq.,desc.1,0,1 only nr. ascending1,1,1 nr.,unq,asc.145a
34-1,0,0 only text=AFUSBYROW(H26:N31,-1,1,-1)=AFUSBYROW(H26:N31,1,,1)=AFUSBYROW(H26:N31,1,1,1)c4b
35zazza1224124d3c
36bbccb88141481432d
37ddd33553551f
38fgggf688686g
3955599959fz
40dada334434g
41=AFUSBYROW(H26:N31,-1,,)9
42
43using ACOMBINE these are all possible combinations 3^3=27
44fus=ISNUMBER(S40)
45-10-1Obs: No need to say, numbers are extracted as numbersTRUE
46-100
47-101input errors, wron argument values
48-11-1=AFUSBYROW(H26:N31,2,,)
49-110check arguments
50-111
51-12-1AFUSBYCOL will follow probably using double transpose this one
52-120
53-121other functions on minisheet
5400-1 T_A1GGH (ARF post Task 10)
55000AAGREGATE
56001
5701-1
58010
59011
6002-1
61020
62021
6310-1
64100
65101
6611-1
67110
68111
6912-1
70120
71121
72
AFUSBYROW
Cell Formulas
RangeFormula
E3,L3,F48,R44,R34,K34,AB32,Z28,X25,P25,B22,B15,G15,B9E3=FORMULATEXT(E4)
E4:E5E4=BYROW(A2:C3,LAMBDA(a,MAX(a)))
L4:L5L4=BYROW(A2:C3,LAMBDA(a,AGGREGATE(14,6,a,2)))
B10B10=BYROW(A2:C3,LAMBDA(a,LARGE(a,{1,2})))
B16:C17B16=T_A1GGH(A2:C3,14,6,{1,2},,)
G16:H17G16=AAGGREGATE(A2:C3,12,{1,2})
B23B23=BYROW(A2:C3,LAMBDA(a,SORT(a)))
P26:V31P26=AFUSBYROW(H26:N31,,,)
X26:X41X26=AFUSBYROW(H26:N31,,2,)
Z29:Z37Z29=AFUSBYROW(H26:N31,1,2,-1)
AB33:AB39AB33=AFUSBYROW(H26:N31,-1,2,1)
E34E34=FORMULATEXT(F35)
A35:C40A35=AFUSBYROW(H26:N31,-1,,)
F35:G40F35=AFUSBYROW(H26:N31,-1,1,-1)
K35:P40K35=AFUSBYROW(H26:N31,1,,1)
R35:T40R35=AFUSBYROW(H26:N31,1,1,1)
A41A41=FORMULATEXT(A35)
R45R45=ISNUMBER(S40)
F49F49=AFUSBYROW(H26:N31,2,,)
Dynamic array formulas.
 
Upvote 0
AFUSBYCOL Array Filter Unique Sort By Columns. Calls AFUSBYROW (above, same arguments) . This is what I call "double transpose", when we need to change the "orientation" of an "oriented" function.
If Fr(a) is row oriented, to get to a function column oriented we use: Fc(a)=transpose(Fr(transpose(a))
Important note: AFUSBYROW returns its results left aligned, AFUSBYCOL returns its results top aligned. On both functions, all unique values return a vertical 1D array.
AFUSBYCOL(a,f,u,s)=LAMBDA(a,f,u,s,LET(t,TRANSPOSE(IF(a="","",a)),x,AFUSBYROW(t,f,u,s),IF(u<>2,TRANSPOSE(IF(x="","",x)),x)))
Excel Formula: 
=LAMBDA(a,f,u,s,LET(t,TRANSPOSE(IF(a="","",a)),x,AFUSBYROW(t,f,u,s),IF(u<>2,TRANSPOSE(IF(x="","",x)),x)))
LAMBDA 1.1.0.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH
10,0,0-1,0,01,0,0
2sample=AFUSBYCOL($A$3:$F$9,,,)=AFUSBYCOL($A$3:$F$9,-1,,)=AFUSBYCOL($A$3:$F$9,1,,)all combinations
318d69d18d69dzbdfd183693fusDescription
42bf932b3f93abdga2858931-10-1text descending
52b3ga2b5g5azcg21438542-100text
6z8585z8389324145943-101text ascending
74143934145g54-1,0,154-11-1unique text descending
8ac5g54acd854=AFUSBYCOL($A$3:$F$9,-1,,1)55-110unique text
9z14d854z14abdfa206-111unique text
1011zbdgd1,0,17-12-1all unique text descending
110,2,00.1.0zcg=AFUSBYCOL($A$3:$F$9,1,,1)8-120all unique text
12=AFUSBYCOL(A3:F9,,2,)=AFUSBYCOL($A$3:$F$9,,1,)31836539-121all unique text ascending
13118d69d-1,0,-12838531000-1descending
1420,2,12b3f53=AFUSBYCOL($A$3:$F$9,-1,,-1)214585411000no blanks
15z=AFUSBYCOL(A3:F9,,2,1)z145gazcdgd41459412001ascending
16414c84zbdga91301-1numbers descending
17a2aabf914010numbers
1883-1,2,11412115011numbers ascending
19b4=AFUSBYCOL(A3:F9,-1,2,1)-1,1,01,0,-11602-1all unique descending
20145a=AFUSBYCOL($A$3:$F$9,-1,1,)=AFUSBYCOL($A$3:$F$9,1,,-1)17020all unique
21c6b0,1,1zbdfd414589418021all unique ascending
22d8c=AFUSBYCOL($A$3:$F$9,,1,1)acga21458941910-1numbers descending
2339d183653528369320100numbers
24514f2145894-1,1,11835321101numbers ascending
256ag4bdfa=AFUSBYCOL($A$3:$F$9,-1,1,1)52211-1unique numbers descending
26fbzacgdabdfa523110unique numbers
27gc9zzcgd1924111unique numbers ascending
289d1561,1,02512-1all unique numbers descending
2917f1,2,-1-1,1,-1=AFUSBYCOL($A$3:$F$9,1,1,)26120all unique numbers
30g=AFUSBYCOL(A3:F9,1,2,-1)=AFUSBYCOL($A$3:$F$9,-1,1,-1)18369327121all unique numbers ascending
31z14zcdgd2145854
321890,1,-1abfa4
338=AFUSBYCOL(A3:F9,,1,-1)423
346zcdg9d1,1,1
355ab5f5a=AFUSBYCOL($A$3:$F$9,1,1,1)
364414384183653
37328632145894
38214
3911324
40251,1,-1
41=AFUSBYCOL($A$3:$F$9,1,1,-1)
424145894
43283653
441
4522
46
AFUSBYCOL
Cell Formulas
RangeFormula
H2,V41,V35,H33,O30,E30,V29,O25,H22,O20,V20,E19,C15,O14,H12,A12,V11,O8,V2,O2H2=FORMULATEXT(H3)
H3:M9H3=AFUSBYCOL($A$3:$F$9,,,)
O3:T5O3=AFUSBYCOL($A$3:$F$9,-1,,)
V3:AA8V3=AFUSBYCOL($A$3:$F$9,1,,)
AC4:AC30AC4=SEQUENCE(27)
O9:T11O9=AFUSBYCOL($A$3:$F$9,-1,,1)
V12:AA17V12=AFUSBYCOL($A$3:$F$9,1,,1)
A13:A28A13=AFUSBYCOL(A3:F9,,2,)
H13:M17H13=AFUSBYCOL($A$3:$F$9,,1,)
O15:T17O15=AFUSBYCOL($A$3:$F$9,-1,,-1)
C16:C31C16=AFUSBYCOL(A3:F9,,2,1)
E20:E26E20=AFUSBYCOL(A3:F9,-1,2,1)
O21:T22O21=AFUSBYCOL($A$3:$F$9,-1,1,)
V21:AA26V21=AFUSBYCOL($A$3:$F$9,1,,-1)
H23:M27H23=AFUSBYCOL($A$3:$F$9,,1,1)
O26:T27O26=AFUSBYCOL($A$3:$F$9,-1,1,1)
V30:AA32V30=AFUSBYCOL($A$3:$F$9,1,1,)
E31:E39E31=AFUSBYCOL(A3:F9,1,2,-1)
O31:T32O31=AFUSBYCOL($A$3:$F$9,-1,1,-1)
H34:M38H34=AFUSBYCOL(A3:F9,,1,-1)
V36:AA38V36=AFUSBYCOL($A$3:$F$9,1,1,1)
V42:AA44V42=AFUSBYCOL($A$3:$F$9,1,1,-1)
Dynamic array formulas.
 
Since the functions were created same day when lambda helper functions emerged, was not familiar with omitted arguments.
Here are the functions with f,u,s arguments that can be omitted. These are the only modifications, same functionality kept.
AFUSBYROW
Excel Formula: 
=LAMBDA(a,[f],[u],[s],
    LET(d,SUM(MMULT({1,1,1},--(CHOOSE({1,2,3},f,u,s)={0,0,-1;-1,1,0;1,2,1}))),IF(d<>3,"check arguments",
      LET(n,ROWS(a),r,SEQUENCE(n),t,AFLAT(a),q,AFLAT(IF(a="","",r)),qf,CHOOSE({1,2},q,t),x,SWITCH(f,0,qf,-1,FILTER(qf,ISTEXT(t)),1,FILTER(qf,ISNUMBER(t))),
         y,IF(u,UNIQUE(x),x),z,SWITCH(s,0,y,1,SORT(y,{1,2}),-1,SORT(y,{1,2},{1,-1})),v,INDEX(z,,1),w,INDEX(z,,2),e,UNIQUE(w),IF(u=2,SWITCH(s,0,e,1,SORT(e),-1,SORT(e,,-1)),
           LET(x,INDEX(FREQUENCY(v,r),r),c,MAX(x),s,SEQUENCE(,c),y,SEQUENCE(n,c)/(x>=s),IFERROR(XLOOKUP(y,AFLAT(y),w,,,2),"")))))
   )
)
AFUSBYCOL
Excel Formula: 
=LAMBDA(a,[f],[u],[s],LET(t,TRANSPOSE(IF(a="","",a)),x,AFUSBYROW(t,f,u,s),IF(u<>2,TRANSPOSE(IF(x="","",x)),x)))[/COLOR]
LAMBDA 1.1.2.xlsx
ABCDEFGHIJKLMNO
1sample
2a2a3
3xax3
4
5f,u,s, all omitted - filters only blanksf,ignored,u,1,s,omitted
6=AFUSBYROW(A2:F3)=AFUSBYROW(A2:F3,,1)
7a2a3a23
8xax3xa3
9
10=AFUSBYCOL(A2:F3)=AFUSBYCOL(A2:F3,,1)
11ax2ax3ax2ax3
12a3
13
Sheet1
Cell Formulas
RangeFormula
A6,I10,A10,I6A6=FORMULATEXT(A7)
A7:D8A7=AFUSBYROW(A2:F3)
I7:K8I7=AFUSBYROW(A2:F3,,1)
A11:F12A11=AFUSBYCOL(A2:F3)
I11:N11I11=AFUSBYCOL(A2:F3,,1)
Dynamic array formulas.
 
You must log in or register to reply here.

Forum statistics

Threads
1,216,505
Messages
6,131,026
Members
449,616
Latest member
PsychoCube

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