Spill dynamic array across multiple columns with a defined depth

[EMoscosoCam]

Hello

Is it possible to distribute a dynamic array into multiple columns? For example, consider an array with 22 values, and you want to spill the values in sequence across 6 columns with a maximum row count of 4:

In this example a have put numbers in sequence for the sake of simplicity, but my real array is made of texts.


Thanks!

 

[KRice]

What version of Excel are you using (update profile to show that)? If you're using Excel 365, look into WRAPROWS and WRAPCOLS.

MrExcel_20220826.xlsx
	A	B	C	D	E	F	G	H
1
2	a		a	b	c
3	b		d	e	f
4	c		g	h	i
5	d		j	k
6	e
7	f
8	g		a	d	g	j
9	h		b	e	h	k
10	i		c	f	i
11	j
12	k
13			a	c	e	g	i	k
14			b	d	f	h	j
15
Sheet7

Cell Formulas
Range	Formula
C2:E5	C2	=WRAPROWS(A2:A12,3,"")
C8:F10	C8	=WRAPCOLS(A2:A12,3,"")
C13:H14	C13	=WRAPCOLS(A2:A12,ROUNDUP(COUNTA(A2:A12)/6,0),"")
Dynamic array formulas.

Edited to show how to force 6 columns in last example.

 

[KRice]

You might need to experiment as those functions have some odd behavior (at least to me it seems that way). If you have 22 values, have a look at the last example below:

MrExcel_20220826.xlsx
	A	B	C	D	E	F	G	H
2	a		a	b	c
3	b		d	e	f
4	c		g	h	i
5	d		j	k
6	e
7	f
8	g		a	d	g	j
9	h		b	e	h	k
10	i		c	f	i
11	j
12	k		a	e	i	m	q	u
13	l		b	f	j	n	r	v
14	m		c	g	k	o	s
15	n		d	h	l	p	t
16	o
17	p
18	q
19	r
20	s
21	t
22	u
23	v
Sheet7

Cell Formulas
Range	Formula
C2:E5	C2	=WRAPROWS(A2:A12,3,"")
C8:F10	C8	=WRAPCOLS(A2:A12,3,"")
C12:H15	C12	=WRAPCOLS(A2:A23,CEILING(COUNTA(A2:A23),6)/6,"")
Dynamic array formulas.

 

[EMoscosoCam]

Thanks a lot! The functions WRAPCOLS() and WRAPROWS() are in the "Office Insider Beta", I don't have it yet (Excel 365 v2206). However, it seems that they have just what I need.

 

[Eric W]

Easier with WRAPROWS, but not terribly hard without it:

Book1
	A	B	C	D	E	F	G	H
1	Array		Column1	Column2	Column3	Column4	Column5	Column6
2	1		1	5	9	13	17	21
3	2		2	6	10	14	18	22
4	3		3	7	11	15	19
5	4		4	8	12	16	20
6	5
7	6
8	7
9	8
10	9
11	10
12	11
13	12
14	13
15	14
16	15
17	16
18	17
19	18
20	19
21	20
22	21
23	22
24
Sheet1

Cell Formulas
Range	Formula
A2:A23	A2	=SEQUENCE(22)
C2:H5	C2	=IFERROR(INDEX(A2#,TRANSPOSE(SEQUENCE(ROUNDUP(ROWS(A2#)/4,0),4))),"")
Dynamic array formulas.

 

[Peter_SSs]

Another way

22 08 28.xlsm
	A	B	C	D	E	F	G	H
1	Array
2	1		1	5	9	13	17	21
3	2		2	6	10	14	18	22
4	3		3	7	11	15	19
5	4		4	8	12	16	20
6	5
7	6
8	7
9	8
10	9
11	10
12	11
13	12
14	13
15	14
16	15
17	16
18	17
19	18
20	19
21	20
22	21
23	22
24
MAKEARRAY

Cell Formulas
Range	Formula
A2:A23	A2	=SEQUENCE(22)
C2:H5	C2	=LET(rws,4,MAKEARRAY(rws,6,LAMBDA(r,c,IFERROR(INDEX(A2#,(c-1)*rws+r),""))))
Dynamic array formulas.

 

[Fluff]

The functions WRAPCOLS() and WRAPROWS() are in the "Office Insider Beta", I don't have it yet (Excel 365 v2206).

They have now been released to the monthly channel, I got them on Friday & am on v2207 so if you run an update you may get them.

 

[EMoscosoCam]

They have now been released to the monthly channel, I got them on Friday & am on v2207 so if you run an update you may get them.

Thanks! I am also in the monthly channel and I have just passed from 2205 to 2206. Maybe is a region thing, but good to know the the rollout is coming

 

[EMoscosoCam]

Thanks a lot for your answers! Using Eric W answer as a base, I wrote a WRAPROWS() imitation:

= LAMBDA(vector, wrap_count, IFERROR(INDEX(vector, TRANSPOSE(SEQUENCE(ROUNDUP(ROWS(vector)/wrap_count,0), wrap_count))),""))

 

[KRice]

Thanks for the update describing the LAMBDA variant. I'm in the monthly channel and updated this past Thursday to v2207 (similar to @Fluff)...looks like WRAPROWS, WRAPCOLS, and several other functions (e.g., HSTACK and VSTACK) were included.