AFLIP

AFLIP(a,[f])
a
array
[f]
flip argument ; 0 or omitted, flips horiz. ; 1, flips vert. ; 2, flips horiz. and vert.
flips an array horizontally and/or vertically
Xlambda

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
837
Office Version
  1. 365
Platform
  1. Windows
AFLIP flips an array horizontally and/or vertically. Does not call any other lambda.
VBA Code: 
=LAMBDA(a,[f],
    LET(r,ROWS(a),c,COLUMNS(a),sr,SEQUENCE(r),sc,SEQUENCE(,c),
      x,IF(f,ABS(sr-r-1),sr),y,IF(f=1,sc,ABS(sc-c-1)),
      INDEX(IF(a="","",a),x,y)
    )
)
Book1
ABCDEFGHIJKLMNOPQRST
1samplef,omitted
212345=AFLIP(A2:E5)if f <>(0,1,2) array flips both directions
36781054321f,3
41113141510876=AFLIP(A2:E5,3)
51617181920151413112019181716
6201918171615141311
710876
8f,154321
9=AFLIP(A2:E5,1)
101617181920
1111131415
1267810
1312345
14
15f,2
16=AFLIP(A2:E5,2)
172019181716
1815141311
1910876
2054321
21
Sheet1
Cell Formulas
RangeFormula
G2,G16,G9,M4G2=FORMULATEXT(G3)
G3:K6G3=AFLIP(A2:E5)
M5:Q8M5=AFLIP(A2:E5,3)
G10:K13G10=AFLIP(A2:E5,1)
G17:K20G17=AFLIP(A2:E5,2)
Dynamic array formulas.
 
Upvote 0
This, is my favorite Lambda not for any reason other than it demonstrates the power of what they can do. I cant think of a use for it in my world but it is excellent. Well done mate.
 
samdthompson said:
This, is my favorite Lambda not for any reason other than it demonstrates the power of what they can do. I cant think of a use for it in my world but it is excellent. Well done mate.
Click to expand...
Thanks a lot for your kind words!!?✌
Came out with some simple examples using AFLIP (there are variants aflip free, but, if we have it, we can use it)
1. Waffle charts using CF
LAMBDA 1.1.4.xlsx
ABCDEFGHIJKLMNOP
1Task 1: Build a waffle chart with conditional formating ( 10x10 cells )
2%CF values for each color
3Total100=AFLIP(ASCAN(AFLIP(C4:C6,1)),1)
4Green2585 =C10<=$D$4
5Red2860 =C10<=$D$5
6Blue3232 =C10<=$D$6
7CF formulas
8
9=AFLIP(SEQUENCE(10,10),1)
10919293949596979899100
1181828384858687888990
1271727374757677787980
1361626364656667686970
1451525354555657585960
1541424344454647484950
1631323334353637383940
1721222324252627282930
1811121314151617181920
1912345678910
20
21
22other functions
23ASCAN
24
AFLIP post 2
Cell Formulas
RangeFormula
D3,C9D3=FORMULATEXT(D4)
D4:D6D4=AFLIP(ASCAN(AFLIP(C4:C6,1)),1)
C10:L19C10=AFLIP(SEQUENCE(10,10),1)
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C10:L19Expression=C10<=$D$6textNO
C10:L19Expression=C10<=$D$5textNO
C10:L19Expression=C10<=$D$4textNO
 
2. function ALFCR(a,[lf],[cr]) Array Last/First values, by Columns/Rows, blanks excluded. Update of former ALFCR . Same functionality, same arguments.
This one calls AFLIP and uses new!! BYROW , BYCOL
The concept is simple, if we have formulas for extracting first clms/rows with values other than blanks/null strings, flipping the array correspondingly, will get last clms/rows values.
Hope that the example is visually explicit enough.
Excel Formula: 
=LAMBDA(a,[lf],[cr],
    LET(r,SEQUENCE(ROWS(a)),c,SEQUENCE(,COLUMNS(a)),x,IF(lf,a,IF(cr,AFLIP(a,1),AFLIP(a))),y,x<>"",
      IF(cr,INDEX(x,BYCOL(IF(y,r),LAMBDA(a,MIN(a))),c),INDEX(x,r,BYROW(IF(y,c),LAMBDA(a,MIN(a)))))
    )
)
LAMBDA 1.1.4.xlsx
ABCDEFGHIJKLMNOPQRSTUV
1Task 2: Extract first/last values of an array by clms/rows
2ALFCR(a,[lf],[cr])
3a: array
4lf: last/first argument: 0 or omitted, last values; 1 or <>0, first values
5cr: column/row argument: 0 or omitted, column (by each row); 1 or <>0 row (by each col)
6
7lf,cr,omitted (last clm)lf,1,cr,omitted (first clm)
8sample 1a=ALFCR(A9:G14)=ALFCR(A9:G14,1,)
925772
10101112131310
1115171820212115
1225262625
133032343430
1439424239
15
16lf,1,cr,1 (frst row)
17sample 2a=ALFCR(A18:G23,1,1)
1825715210115137
1910111213
201517182021
21222526lf,omitted,cr,1 (last row)
22303234=ALFCR(A18:G23,,1)
23394222301739263442
24
AFLIP post 3
Cell Formulas
RangeFormula
I8,I22,I17,O8I8=FORMULATEXT(I9)
I9:I14I9=ALFCR(A9:G14)
O9:O14O9=ALFCR(A9:G14,1,)
I18:O18I18=ALFCR(A18:G23,1,1)
I23:O23I23=ALFCR(A18:G23,,1)
Dynamic array formulas.
 
This is how ALFCR looks without AFLIP:
Excel Formula: 
=LAMBDA(a,[lf],[cr],
    LET(y,IF(a="","",a),r,SEQUENCE(ROWS(a)),c,SEQUENCE(,COLUMNS(a)),x,y<>"",
      bc,BYCOL(IF(x,r),LAMBDA(a,IF(lf,MIN(a),MAX(a)))),
      br,BYROW(IF(x,c),LAMBDA(a,IF(lf,MAX(a),MIN(a)))),
      IF(cr,INDEX(y,bc,c),INDEX(y,r,br))
    )
)
LAMBDA 1.1.4.xlsx
ABCDEFGHIJKLMNOPQRST
1ALFCR without AFLIP
2ALFCR(a,[lf],[cr])
3a: array
4lf: last/first argument: 0 or omitted, last values; 1 or <>0, first values
5cr: column/row argument: 0 or omitted, column (by each row); 1 or <>0 row (by each col)
6
7Note: This function can handle arrays that have full rows/clms with blanks/null strings
8
9lf,cr,omitted (last clm)lf,1,cr,omitted (first clm)
10sample 1a=ALFCR(A11:G16)=ALFCR(A11:G16,1,)
1125727
12101112131013
1315171820211521
14  
153032343034
1639423942
17
18lf,1,cr,1 (frst row)
19sample 2a=ALFCR(A20:G25,1,1)
202  57152115137
21111213
2215182021
232526lf,omitted,cr,1 (last row)
24 303234=ALFCR(A20:G25,,1)
2539 42153039263442
26
AFLIP post 4
Cell Formulas
RangeFormula
I10,I24,I19,O10I10=FORMULATEXT(I11)
I11:I16I11=ALFCR(A11:G16)
O11:O16O11=ALFCR(A11:G16,1,)
A14,E25,A24,C20:D20,G14A14=""
I20:O20I20=ALFCR(A20:G25,1,1)
I25:O25I25=ALFCR(A20:G25,,1)
Dynamic array formulas.
 
"snake" sequence using AFLIP
LAMBDA 1.1.4.xlsx
ABCDEFGHIJKLMNOPQRS
1"snake" sequence using AFLIP
2
3rows7lowest value each row
4clms8highest value each row
5
6=SEQUENCE(7,8)from this:tothis:snake sequence
71234567812345678
8910111213141516161514131211109
917181920212223241718192021222324
1025262728293031323231302928272625
1133343536373839403334353637383940
1241424344454647484847464544434241
1349505152535455564950515253545556
14
15=IF(ISODD(SEQUENCE(B3)),B7#,AFLIP(B7#))
1612345678
17161514131211109
181718192021222324
193231302928272625
203334353637383940
214847464544434241
224950515253545556
23
AFLIP post 5
Cell Formulas
RangeFormula
B6,K15B6=FORMULATEXT(B7)
B7:I13B7=SEQUENCE(7,8)
K16:R22K16=IF(ISODD(SEQUENCE(B3)),B7#,AFLIP(B7#))
Dynamic array formulas.
 
They say there always several ways to do things in Excel

I didn't see your formula and wrote my own, which is completely different :D

Excel Formula: 
=LAMBDA(a,LET(c,COUNTA(INDEX(a,1,)),r,COUNTA(INDEX(a,,1)),MAKEARRAY(r,c,LAMBDA(rr,cc,INDEX(a,rr,c+1-cc)))))
 
Dermot said:
They say there always several ways to do things in Excel
Click to expand...
Super cool !! Great work!! ✌️?
Can you post please some examples to see your function at work?
 
well, it's pretty simple
if you call my lambda Flipper, then it's = Flipper(A5:D10) where the range to flip is A5:D10
 
Please!!!! I insist. Is respectful for the viewers to present your function ✌️??
 
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