Combine Columns Dynamic Array Formulas

[lrobbo314]

In a previous thread @Peter_SSs used a technique using Index with arrays to combine multiple columns to 1 single column array.

=IF(F3=F2,"",LET(a,FILTER(B$3:E$22,F$3:F$22=F3),r,ROWS(a),seq,SEQUENCE(r*COLUMNS(a),,0),arr,INDEX(a,MOD(seq,r)+1,seq/r+1),TEXTJOIN(", ",1,UNIQUE(FILTER(arr,arr<>0)))))

I wanted to know if it was possible to do something similar, but instead combine the 6 column table below to a 2 column table.

Book1
	A	B	C	D	E	F	G	H	I
1	5	10	6	12	7	12		5	10
2	4	8	7	13	8	13		4	8
3	3	5	4	8	4	8		3	5
4	5	12	4	15	3	9		5	12
5	4	15	2	8	5	17		4	15
6								6	12
7								7	13
8								4	8
9								4	15
10								2	8
11								7	12
12								8	13
13								4	8
14								3	9
15								5	17
Sheet3

 

[StephenCrump]

	A	B	C	D	E	F	G	H	I
1	MyData							NoCols (N)	2
2	5	10	6	12	7	12		5	10
3	4	8	7	13	8	13		4	8
4	3	5	4	8	4	8		3	5
5	5	12	4	15	3	9		5	12
6	4	15	2	8	5	17		4	15
7								6	12
8								7	13
9								4	8
10								4	15
11								2	8
12								7	12
13								8	13
14								4	8
15								3	9
16								5	17
Sheet1

Cell Formulas
Range	Formula
H2:I16	H2	=INDEX(MyData,MOD(SEQUENCE(ROWS(MyData)*COLUMNS(MyData)/N,,0),ROWS(MyData))+SEQUENCE(,N,1,0),SEQUENCE(,N)+N*INT(SEQUENCE(ROWS(MyData)*COLUMNS(MyData)/N,,0)/ROWS(MyData)))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
MyData	=Sheet1!$A$2:$F$6	H2
N	=Sheet1!$I$1	H2

Or more succinctly
=LET(M,ROWS(MyData)*COLUMNS(MyData)/N,R, ROWS(MyData),INDEX(MyData,MOD(SEQUENCE(M,,0),R)+SEQUENCE(,N,1,0),SEQUENCE(,N)+N*INT(SEQUENCE(M,,0)/R)))

 

[jasonb75]

Something like this?

Edit:- ignore this, just noticed that the results in the first column are wrong from rows 6 to 15

Book3
	A	B	C	D	E	F	G	H	I
1	5	10	6	12	7	12		5	10
2	4	8	7	13	8	13		4	8
3	3	5	4	8	4	8		3	5
4	5	12	4	15	3	9		5	12
5	4	15	2	8	5	17		4	15
6								10	12
7								8	13
8								5	8
9								12	15
10								15	8
11								6	12
12								7	13
13								4	8
14								4	9
15								2	17
Sheet5

Cell Formulas
Range	Formula
H1:I15	H1	=LET(arr,A1:F5,r,ROWS(arr),c,2,seq,SEQUENCE(COUNT(arr)/c,,0),INDEX(arr,MOD(seq,r)+1,(INT(seq/r)+1)*SEQUENCE(,c)))
Dynamic array formulas.

 

[jasonb75]

Corrected version, fixed it too late to edit previous reply.

Book3
	A	B	C	D	E	F	G	H	I
1	5	10	6	12	7	12		5	10
2	4	8	7	13	8	13		4	8
3	3	5	4	8	4	8		3	5
4	5	12	4	15	3	9		5	12
5	4	15	2	8	5	17		4	15
6								6	12
7								7	13
8								4	8
9								4	15
10								2	8
11								7	12
12								8	13
13								4	8
14								3	9
15								5	17
Sheet5

Cell Formulas
Range	Formula
H1:I15	H1	=LET(arr,A1:F5,r,ROWS(arr),c,2,seq,SEQUENCE(COUNT(arr)/c,,0),INDEX(arr,MOD(seq,r)+1,(INT(seq/r))*c+SEQUENCE(,c)))
Dynamic array formulas.

 

[Peter_SSs]

@jasonb75
You no doubt already know but that will not work if there are any text values, blanks or errors in the range.

This was my effort. (This & Stephens return zero for blanks in the range)

21 02 22.xlsm
	A	B	C	D	E	F	G	H	I
1	5	10	6	12	7	12		5	10
2	4	8	7	13	8	13		4	8
3	3	5	4	8	4	8		3	5
4	5	12	4	15	3	9		5	12
5	4	15	2	8	5	17		4	15
6								6	12
7								7	13
8								4	8
9								4	15
10								2	8
11								7	12
12								8	13
13								4	8
14								3	9
15								5	17
6 cols to 2

Cell Formulas
Range	Formula
H1:I15	H1	=LET(rng,A1:F5,rws,ROWS(rng),num,rws*COLUMNS(rng)/2,seq,SEQUENCE(num,,0),INDEX(rng,MOD(seq,rws)+1,2*INT(seq/rws)+{1,2}))
Dynamic array formulas.

 

[StephenCrump]

Thanks Peter

Borrowing @jasonb75's more succinct use of the LET, I'd modify the general case:

=LET(arr,A1:F5,r,ROWS(arr),c,2,seq,SEQUENCE(r*COLUMNS(arr)/c,,0),INDEX(arr,MOD(seq,r)+1,(INT(seq/r))*c+SEQUENCE(,c)))

 

[jasonb75]

You no doubt already know but that will not work if there are any text values, blanks or errors in the range.

Now you mention it, Peter, it is something that I'm aware of.
I don't appear to be capable of thinking after midnight, at that time such possibilities often appear to elude me.

 

[Peter_SSs]

I don't appear to be capable of thinking after midnight, at that time such possibilities often appear to elude me.

?

 

[lrobbo314]

Thanks everyone for the answers. Very cool. Stepping through the formula, I can see how you guys made it work, but how did you guys figure this out? I banged my head against the screen for a while trying to figure out the logic of it.

Just for info I am marking Stephen's answer as the answer just because I thought it was a nice touch that just changing the 1 variable let you control the number of columns it splits the data into. But again, thanks to all for the responses.

 

[Peter_SSs]

.. but how did you guys figure this out? I banged my head against the screen for a while ..

Most likely because I used a brick wall instead of a screen.