Forum Discussion
Merging text column
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 on...
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 )