ARRANGE

=ARRANGE(a,o)
a
array
o
order argument, integer or array of integers. >=1. 2 or {4,2} . can not be 0 or ignored
rearranges columns of an array from left to right in any order
Xlambda

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
837
Office Version
  1. 365
Platform
  1. Windows
ARRANGE array arrange, rearranges columns of an array from left to right in any order.
Example: Lets consider "a" a 5 columns array. ARRANGE(a,{4,2}) will rearrange the columns indexes in this order from left to right 4,2 , and to follow, the remaining indexes in ascending order from left to right. Final outcome : 4,2,1,3,5
o: order argument, integer or array of integers. >=1. 2 or {4,2} . can not be 0 or ignored
Excel Formula: 
=LAMBDA(a,o,
    LET(c,COLUMNS(a),l,COLUMNS(o),r,ROWS(a),w,SEQUENCE(r),s,SEQUENCE(,c),
       x,FILTER(s,ISNA(XMATCH(s,o))),y,INDEX(a,w,IF(s<=l,o,INDEX(x,s-l))),z,AND(ISNUMBER(XMATCH(o,s))),
       IF(NOT(z),"check order",y)
    )
)
LAMBDA 7.0.xlsx
ABCDEFGHIJKLMNOPQR
1sample=ARRANGE(A2:D10,2)=ARRANGE(A2:D10,{2,4})=ARRANGE(A2:D10,{2,5})
2CodeProductPriceColorProductCodePriceColorProductColorCodePricecheck order
3CR-rCarlota26RedCarlotaCR-r26RedCarlotaRedCR-r26
4KK-bKiki13BlueKikiKK-b13BlueKikiBlueKK-b13(5 is out of range
5QD-rQuad43RedQuadQD-r43RedQuadRedQD-r43of total 4 clms)
6SH-rSunshine19RedSunshineSH-r19RedSunshineRedSH-r19
7CR-gCarlota24GreenCarlotaCR-g24GreenCarlotaGreenCR-g24=ARRANGE(A2:D10,)
8QD-bQuad41BlueQuadQD-b41BlueQuadBlueQD-b41check order
9SH-bSunshine18BlueSunshineSH-b18BlueSunshineBlueSH-b18
10KK-rKiki20RedKikiKK-r20RedKikiRedKK-r20(order argument
11can not be ignored or 0)
12to leave array unchanged o=1
13=ARRANGE(A2:D10,1)=ARRANGE(A2:D10,{2,4,3})
14CodeProductPriceColorProductColorPriceCode
15CR-rCarlota26RedCarlotaRed26CR-r
16KK-bKiki13BlueKikiBlue13KK-b
17QD-rQuad43RedQuadRed43QD-r
18SH-rSunshine19RedSunshineRed19SH-r
19CR-gCarlota24GreenCarlotaGreen24CR-g
20QD-bQuad41BlueQuadBlue41QD-b
21SH-bSunshine18BlueSunshineBlue18SH-b
22KK-rKiki20RedKikiRed20KK-r
23
ARRANGE post
Cell Formulas
RangeFormula
F1,K13,F13,P7,P1,K1F1=FORMULATEXT(F2)
F2:I10F2=ARRANGE(A2:D10,2)
K2:N10K2=ARRANGE(A2:D10,{2,4})
P2P2=ARRANGE(A2:D10,{2,5})
P8P8=ARRANGE(A2:D10,)
F14:I22F14=ARRANGE(A2:D10,1)
K14:N22K14=ARRANGE(A2:D10,{2,4,3})
Dynamic array formulas.
 
Upvote 1
Thanks a lot !! More on the way. ?
 
HSTACK, and CHOOSECOLS? - or did these come out later?
 
st001 said:
HSTACK, and CHOOSECOLS? - or did these come out later?
Click to expand...
Yes, they came out later. Keeping the concept of ARRANGE (not only extracting a certain sequence of columns, but reordering entire column indexes), 2 new functions using latest functions, on both directions.
ACHCLS(ar,ns) Array Choose Columns and ACHRWS(ar,ns) Array Choose Rows
ar: array
ns: new sequence order clms or rows (whatever index nr. are left out will be appended at the right end or at the bottom of the array)
Trick: I did not use CHOOSECOLS/ROWS and HSTACK/VSTACK on array itself, used them only directly to the "ns" new sequence argument.
ARCHCLS(ar,ns)
Excel Formula: 
=LAMBDA(ar, ns,
    LET(
        a, IF(ar = "", "", ar),
        s, SEQUENCE(, COLUMNS(a)),
        h, CHOOSECOLS(s, ns),
        x, XMATCH(s, h),
        f, FILTER(s, ISNA(x)),
        INDEX(a, SEQUENCE(ROWS(a)), HSTACK(h, f))
    )
)
ACHRWS(ar,ns)
Excel Formula: 
=LAMBDA(ar, ns,
    LET(
        a, IF(ar = "", "", ar),
        s, SEQUENCE(ROWS(a)),
        h, CHOOSEROWS(s, ns),
        x, XMATCH(s, h),
        f, FILTER(s, ISNA(x)),
        INDEX(a, VSTACK(h, f), SEQUENCE(, COLUMNS(a)))
    )
)
ARRANGE.xlsx
ABCDEFGHIJKLMNOPQR
1
2135-2-1=ACHCLS(B3#,{1,3,5,-2,-1})
3ar12345671356724
4891011121314810121314911
51516171819202115171920211618
62223242526272822242627282325
72930313233343529313334353032
83637383940414236384041423739
94344454647484943454748494446
10
11
12ar=ACHRWS(B13#,{1,3,5,-2,-1})
131181522293643181522293643
142916233037443101724313845
15331017243138455121926334047
1641118253239466132027344148
17551219263340477142128354249
18-26132027344148291623303744
19-171421283542494111825323946
20
Sheet3
Cell Formulas
RangeFormula
K2,K12K2=FORMULATEXT(K3)
B3:H9B3=SEQUENCE(7,7)
K3:Q9K3=ACHCLS(B3#,{1,3,5,-2,-1})
B13:H19B13=TRANSPOSE(SEQUENCE(7,7))
K13:Q19K13=ACHRWS(B13#,{1,3,5,-2,-1})
Dynamic array formulas.
 
ARRANGE.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
1CHOOSECOLS/ROWS vs ACHCLS/RWS when they share same "ns" argument
2
3135-2-1=CHOOSECOLS(B4#,{1,3,5,-2,-1})=ACHCLS(B4#,{1,3,5,-2,-1})
4ar1234567135671356724
5891011121314810121314810121314911
615161718192021151719202115171920211618
722232425262728222426272822242627282325
829303132333435293133343529313334353032
936373839404142363840414236384041423739
1043444546474849434547484943454748494446
11
12
13ar=CHOOSEROWS(B14#,{1,3,5,-2,-1})=ACHRWS(B14#,{1,3,5,-2,-1})
141181522293643181522293643181522293643
1529162330374431017243138453101724313845
163310172431384551219263340475121926334047
17411182532394661320273441486132027344148
185512192633404771421283542497142128354249
19-26132027344148291623303744
20-171421283542494111825323946
21
Sheet4
Cell Formulas
RangeFormula
J3,R13,J13,R3J3=FORMULATEXT(J4)
B4:H10B4=SEQUENCE(7,7)
J4:N10J4=CHOOSECOLS(B4#,{1,3,5,-2,-1})
R4:X10R4=ACHCLS(B4#,{1,3,5,-2,-1})
B14:H20B14=TRANSPOSE(SEQUENCE(7,7))
J14:P18J14=CHOOSEROWS(B14#,{1,3,5,-2,-1})
R14:X20R14=ACHRWS(B14#,{1,3,5,-2,-1})
Dynamic array formulas.
 
I used this to rearrange a column to the beginning since sort was not taking negative numbers for the sort index argument.
 
jaeiow said:
I used this to rearrange a column to the beginning since sort was not taking negative numbers for the sort index argument.
Click to expand...
Or you can use CHOOSECOLS directly to sort index array.
Book1
ABCDEFGHIJKLMNOPQRSTUVW
1sample
2=RANDARRAY(5,10,1,5,1)=SORT(B3#,{10,9,7})
353424555244213353131
445314553245342455524
534131321344531455324
654344555453413132134
742133531315434455545
8
9CHOOSECOLS applied directly to sort index array
10=SORT(B3#,CHOOSECOLS(SEQUENCE(,10),{-1,-2,-3}))
114213353131
124531455324
135342455524
143413132134
155434455545
16
Sheet5
Cell Formulas
RangeFormula
B2,M10,M2B2=FORMULATEXT(B3)
B3:K7B3=RANDARRAY(5,10,1,5,1)
M3:V7M3=SORT(B3#,{10,9,7})
M11:V15M11=SORT(B3#,CHOOSECOLS(SEQUENCE(,10),{-1,-2,-3}))
Dynamic array formulas.
 
You must log in or register to reply here.

Forum statistics

Threads
1,216,503
Messages
6,131,022
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