Search code examples
excelexcel-formuladynamic-arraysarray-merge

Excel : How to merge two dynamic arrays into one dynamic array, with alternating rows?

I have two dynamic arrays, A1# and B1#. I want to combine them into a dynamic array, C1#, like so:

Column A Column B Column C
x 4 x
y 5 4
z 6 y
5 ...

Currently, I am using the solution provided via WeAreOne's answer to this, of which I have also seen a few different similar answers:

=IF(ISEVEN(ROW())=FALSE,INDEX($A$1:$A$3,ROUND((ROWS($A$1:A1)/2),0)),INDEX($B$1:$B$3,ROUND((ROWS($B$1:B1)/2),0)))

(entered into C1 and dragged down).

However, the arrays I am merging are often changing length drastically, and so I am looking for the neater solution of returning a dynamic array, rather than dragging down my current formula in anticipation of the merged array's length and also having to alter the references to the initial arrays.

Solution

  • Using TOCOL()

    enter image description here

    • Formula used in cell C2

    =DROP(TOCOL(A:B,3),2)

    As Ike Ma'am mentioned in comments, you can use it this way as well,

    enter image description here

    • Formula used in cell C2

    =TOCOL(A2#:B2#,3)