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.
@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.
The 'natural' concatenation of the 2 lists would produce an array
It is unpivoting the array to a single list that is messy.
If one defines an index 'k' for the desired result array
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 )