Merging text column

[lacala11]

Hi all, 

I want to merge 2 column A and B and have result in column C (follow picture). In this case I use  CONCATENATE($A$1," ",B2)  and  CONCATENATE($A$2," ",B2) but it's slowly. Can I use only one query for this case. Please help me.

[#2]

@lacala11 , that could be as

=OFFSET($A$1,INT((ROW()-1)/ROWS($B$1:$B$5)),0)&" "& OFFSET($B$1,MOD(ROW()-1,ROWS($B$1:$B$5)),0)

and drag it down. Is to be adjusted to your actual ranges and/or you may use dynamic ranges.

[#3]

@lacala11 

The 'natural' concatenation of the 2 lists would produce an array

A1 B1	A2 B1
A1 B2	A2 B2
A1 B3	A2 B3
A1 B4	A2 B4
A1 B5	A2 B5

It is unpivoting the array to a single list that is messy. 

If one defines an index 'k' for the desired result array

= ROW(result)

then row and column indices 'I', 'J' for the corresponding cell in the concatenation are given by

= 1 + MOD( k-1, 5 )

= CEILING( k, 5) / 5

respectively, where the 5s correspond to the number of rows in B.  The result array is given by

= INDEX( concat, I, J )