Mar 28 2019 03:45 AM - edited Mar 28 2019 03:49 AM
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.
Mar 28 2019 04:20 AM
@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.
Mar 28 2019 04:30 AM
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 )