Search code examples
excelexcel-formuladynamic-arrays

Combine to columns with a single formula

Using the table below, the formula =A1:A3 is a dynamic array that reproduces the values of column A.

column A column B
1 4
2 5
3 6

What I would like to do is use a single formula to reference column A and column B but have both columns return in a single column like below. Is that possible without using VBA?

column D
1
2
3
4
5
6
Solution

  • Try this =FILTERXML("<t><s>"&TEXTJOIN("</s><s>",,TRANSPOSE(A1:B3))&"</s></t>","//s"). It should work unless you are on Mac.

    If size is an issue as @BigBen points out you can try this.

    =LET(column1, A1:A3,
     column2, B1:B3,
     size1, ROWS(column1),
     q, SEQUENCE(size1 + ROWS(column2)),
     IF(q>size1, INDEX(column2,q-size1), INDEX(column1,q)))