JOIN Arrays in Formula

[MrRajKumar]

Hello,

I am using M365.

Below is my data setup.

A2:A10 - Item names, say A-I
B2:B10 - Its values, say 1-9

So in C2, I would like to get values joined as follows (A2 value space B2 value comma A3 space B3, A4 space B4 etc...)

A 1, B 2, C 3, D 4, E 5...I 9

Both this range has its own spill.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D
1
2	A	1		A 1, B 2, C 3, D 4, E 5, F 6, G 7, H 8, I 9
3	B	2
4	C	3
5	D	4
6	E	5
7	F	6
8	G	7
9	H	8
10	I	9
Sheet5

Cell Formulas
Range	Formula
A2:A10	A2	=CHAR(SEQUENCE(9,,65))
B2:B10	B2	=SEQUENCE(9)
D2	D2	=TEXTJOIN({" ",", "},,A2#:B2#)
Dynamic array formulas.

 

[MrRajKumar]

Thank you Paul.

How can I modify the formula if ranges are in horizontal;

A2:I2 & A3:I3

 

[Fluff]

How about

=TEXTJOIN({" ",", "},,WRAPROWS(TOCOL(A2:I3,,1),2))

 

[Rick Rothstein]

How about

Fluff.xlsm
	A	B	C	D
1
2	A	1		A 1, B 2, C 3, D 4, E 5, F 6, G 7, H 8, I 9
3	B	2
4	C	3
5	D	4
6	E	5
7	F	6
8	G	7
9	H	8
10	I	9
Sheet5

Cell Formulas
Range	Formula
A2:A10	A2	=CHAR(SEQUENCE(9,,65))
B2:B10	B2	=SEQUENCE(9)
D2	D2	=TEXTJOIN({" ",", "},,A2#:B2#)
Dynamic array formulas.

You did not need to specify two separate ranges in your TEXTJOIN function call; this would also work...
=TEXTJOIN({" ",", "},,A2:B10)

 

[Rick Rothstein]

How about

=TEXTJOIN({" ",", "},,WRAPROWS(TOCOL(A2:I3,,1),2))

This would also work...
=TEXTJOIN({" ",", "},,TRANSPOSE(A2:I3))

 

[Fluff]

this would also work...
=TEXTJOIN({" ",", "},,A2:B10)

Yes I know, but the OP said

Both this range has its own spill.

So I did them as spill ranges.

 

[Rick Rothstein]

Yes I know, but the OP said

So I did them as spill ranges.

Okay, I see. I had wondered exactly what the OP meant when he said that.