Forum Discussion

lacala11's avatar
lacala11
Copper Contributor
Mar 28, 2019

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 one query for this case. Please help me.

 

Excel Online

2 Replies

Sort By
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Mar 28, 2019

    lacala11 

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

    A1 B1A2 B1
    A1 B2A2 B2
    A1 B3A2 B3
    A1 B4A2 B4
    A1 B5A2 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 )

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Mar 28, 2019

    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.

     

Resources