Dynamic range concatenate

[Luke777]

Hi All,

I have strings in B and C which I wish to concatenate in D.

Usually I'd simply put the formula

=B1&" "&C1

in D1 and drag down. In this case, however, I don't know how far to drag down to as the length of data in B:C (and A) is variable.

I know this can be done with a dynamic array formula, but I've not really got to grips with them yet - placing a formula in a single cell and watching it spill over is dark sorcery as far as I'm currently concerned.

Thanks!

 

[JamesCanale]

Odd solution. Blanks would be a problem...

MrExcelPlayground6.xlsx
	B	C	D
1	a	1	a 1
2	b	2	b 2
3	c	3	c 3
4	d	4	d 4
5	e	5	e 5
6	f	6	f 6
7	g	7	g 7
8	h	8	h 8
9	i	9	i 9
10	j	10	j 10
11	k	11	k 11
12	l	12	l 12
13	m	13	m 13
14	n	14	n 14
15	o	15	o 15
16	p	16	p 16
Sheet15

Cell Formulas
Range	Formula
D1:D16	D1	=INDEX(B:B,SEQUENCE(ROWS(FILTER(B:B,B:B<>""))))&" "&INDEX(C:C,SEQUENCE(ROWS(FILTER(B:B,B:B<>""))))
Dynamic array formulas.