ARRANGE

[Xlambda]

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

=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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	sample					=ARRANGE(A2:D10,2)					=ARRANGE(A2:D10,{2,4})					=ARRANGE(A2:D10,{2,5})
2	Code	Product	Price	Color		Product	Code	Price	Color		Product	Color	Code	Price		check order
3	CR-r	Carlota	26	Red		Carlota	CR-r	26	Red		Carlota	Red	CR-r	26
4	KK-b	Kiki	13	Blue		Kiki	KK-b	13	Blue		Kiki	Blue	KK-b	13		(5 is out of range
5	QD-r	Quad	43	Red		Quad	QD-r	43	Red		Quad	Red	QD-r	43		of total 4 clms)
6	SH-r	Sunshine	19	Red		Sunshine	SH-r	19	Red		Sunshine	Red	SH-r	19
7	CR-g	Carlota	24	Green		Carlota	CR-g	24	Green		Carlota	Green	CR-g	24		=ARRANGE(A2:D10,)
8	QD-b	Quad	41	Blue		Quad	QD-b	41	Blue		Quad	Blue	QD-b	41		check order
9	SH-b	Sunshine	18	Blue		Sunshine	SH-b	18	Blue		Sunshine	Blue	SH-b	18
10	KK-r	Kiki	20	Red		Kiki	KK-r	20	Red		Kiki	Red	KK-r	20		(order argument
11																can not be ignored or 0)
12						to leave array unchanged o=1
13						=ARRANGE(A2:D10,1)					=ARRANGE(A2:D10,{2,4,3})
14						Code	Product	Price	Color		Product	Color	Price	Code
15						CR-r	Carlota	26	Red		Carlota	Red	26	CR-r
16						KK-b	Kiki	13	Blue		Kiki	Blue	13	KK-b
17						QD-r	Quad	43	Red		Quad	Red	43	QD-r
18						SH-r	Sunshine	19	Red		Sunshine	Red	19	SH-r
19						CR-g	Carlota	24	Green		Carlota	Green	24	CR-g
20						QD-b	Quad	41	Blue		Quad	Blue	41	QD-b
21						SH-b	Sunshine	18	Blue		Sunshine	Blue	18	SH-b
22						KK-r	Kiki	20	Red		Kiki	Red	20	KK-r
23
ARRANGE post

Cell Formulas
Range	Formula
F1,K13,F13,P7,P1,K1	F1	=FORMULATEXT(F2)
F2:I10	F2	=ARRANGE(A2:D10,2)
K2:N10	K2	=ARRANGE(A2:D10,{2,4})
P2	P2	=ARRANGE(A2:D10,{2,5})
P8	P8	=ARRANGE(A2:D10,)
F14:I22	F14	=ARRANGE(A2:D10,1)
K14:N22	K14	=ARRANGE(A2:D10,{2,4,3})
Dynamic array formulas.

 

[smozgur]

Cool! I love it!

 

[Xlambda]

Thanks a lot !! More on the way. ?

 

[st001]

HSTACK, and CHOOSECOLS? - or did these come out later?

 

[Xlambda]

HSTACK, and CHOOSECOLS? - or did these come out later?

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)

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

=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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2		1		3		5	-2	-1			=ACHCLS(B3#,{1,3,5,-2,-1})
3	ar	1	2	3	4	5	6	7			1	3	5	6	7	2	4
4		8	9	10	11	12	13	14			8	10	12	13	14	9	11
5		15	16	17	18	19	20	21			15	17	19	20	21	16	18
6		22	23	24	25	26	27	28			22	24	26	27	28	23	25
7		29	30	31	32	33	34	35			29	31	33	34	35	30	32
8		36	37	38	39	40	41	42			36	38	40	41	42	37	39
9		43	44	45	46	47	48	49			43	45	47	48	49	44	46
10
11
12		ar									=ACHRWS(B13#,{1,3,5,-2,-1})
13	1	1	8	15	22	29	36	43			1	8	15	22	29	36	43
14		2	9	16	23	30	37	44			3	10	17	24	31	38	45
15	3	3	10	17	24	31	38	45			5	12	19	26	33	40	47
16		4	11	18	25	32	39	46			6	13	20	27	34	41	48
17	5	5	12	19	26	33	40	47			7	14	21	28	35	42	49
18	-2	6	13	20	27	34	41	48			2	9	16	23	30	37	44
19	-1	7	14	21	28	35	42	49			4	11	18	25	32	39	46
20
Sheet3

Cell Formulas
Range	Formula
K2,K12	K2	=FORMULATEXT(K3)
B3:H9	B3	=SEQUENCE(7,7)
K3:Q9	K3	=ACHCLS(B3#,{1,3,5,-2,-1})
B13:H19	B13	=TRANSPOSE(SEQUENCE(7,7))
K13:Q19	K13	=ACHRWS(B13#,{1,3,5,-2,-1})
Dynamic array formulas.

 

[Xlambda]

ARRANGE.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1		CHOOSECOLS/ROWS vs ACHCLS/RWS when they share same "ns" argument
2
3		1		3		5	-2	-1		=CHOOSECOLS(B4#,{1,3,5,-2,-1})								=ACHCLS(B4#,{1,3,5,-2,-1})
4	ar	1	2	3	4	5	6	7		1	3	5	6	7				1	3	5	6	7	2	4
5		8	9	10	11	12	13	14		8	10	12	13	14				8	10	12	13	14	9	11
6		15	16	17	18	19	20	21		15	17	19	20	21				15	17	19	20	21	16	18
7		22	23	24	25	26	27	28		22	24	26	27	28				22	24	26	27	28	23	25
8		29	30	31	32	33	34	35		29	31	33	34	35				29	31	33	34	35	30	32
9		36	37	38	39	40	41	42		36	38	40	41	42				36	38	40	41	42	37	39
10		43	44	45	46	47	48	49		43	45	47	48	49				43	45	47	48	49	44	46
11
12
13		ar								=CHOOSEROWS(B14#,{1,3,5,-2,-1})								=ACHRWS(B14#,{1,3,5,-2,-1})
14	1	1	8	15	22	29	36	43		1	8	15	22	29	36	43		1	8	15	22	29	36	43
15		2	9	16	23	30	37	44		3	10	17	24	31	38	45		3	10	17	24	31	38	45
16	3	3	10	17	24	31	38	45		5	12	19	26	33	40	47		5	12	19	26	33	40	47
17		4	11	18	25	32	39	46		6	13	20	27	34	41	48		6	13	20	27	34	41	48
18	5	5	12	19	26	33	40	47		7	14	21	28	35	42	49		7	14	21	28	35	42	49
19	-2	6	13	20	27	34	41	48										2	9	16	23	30	37	44
20	-1	7	14	21	28	35	42	49										4	11	18	25	32	39	46
21
Sheet4

Cell Formulas
Range	Formula
J3,R13,J13,R3	J3	=FORMULATEXT(J4)
B4:H10	B4	=SEQUENCE(7,7)
J4:N10	J4	=CHOOSECOLS(B4#,{1,3,5,-2,-1})
R4:X10	R4	=ACHCLS(B4#,{1,3,5,-2,-1})
B14:H20	B14	=TRANSPOSE(SEQUENCE(7,7))
J14:P18	J14	=CHOOSEROWS(B14#,{1,3,5,-2,-1})
R14:X20	R14	=ACHRWS(B14#,{1,3,5,-2,-1})
Dynamic array formulas.

 

[jaeiow]

I used this to rearrange a column to the beginning since sort was not taking negative numbers for the sort index argument.

 

[Xlambda]

I used this to rearrange a column to the beginning since sort was not taking negative numbers for the sort index argument.

Or you can use CHOOSECOLS directly to sort index array.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1		sample
2		=RANDARRAY(5,10,1,5,1)											=SORT(B3#,{10,9,7})
3		5	3	4	2	4	5	5	5	2	4		4	2	1	3	3	5	3	1	3	1
4		4	5	3	1	4	5	5	3	2	4		5	3	4	2	4	5	5	5	2	4
5		3	4	1	3	1	3	2	1	3	4		4	5	3	1	4	5	5	3	2	4
6		5	4	3	4	4	5	5	5	4	5		3	4	1	3	1	3	2	1	3	4
7		4	2	1	3	3	5	3	1	3	1		5	4	3	4	4	5	5	5	4	5
8
9													CHOOSECOLS applied directly to sort index array
10													=SORT(B3#,CHOOSECOLS(SEQUENCE(,10),{-1,-2,-3}))
11													4	2	1	3	3	5	3	1	3	1
12													4	5	3	1	4	5	5	3	2	4
13													5	3	4	2	4	5	5	5	2	4
14													3	4	1	3	1	3	2	1	3	4
15													5	4	3	4	4	5	5	5	4	5
16
Sheet5

Cell Formulas
Range	Formula
B2,M10,M2	B2	=FORMULATEXT(B3)
B3:K7	B3	=RANDARRAY(5,10,1,5,1)
M3:V7	M3	=SORT(B3#,{10,9,7})
M11:V15	M11	=SORT(B3#,CHOOSECOLS(SEQUENCE(,10),{-1,-2,-3}))
Dynamic array formulas.