CODEWITHSUNDEEP
excelexcel-formuladynamic-arrays
Adam W.
Adam W.

Reputation: 179

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

Upvotes: 0

Views: 116

Answers (1)

Axuary
Axuary

Reputation: 1507

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)))

Upvotes: 3

Related Questions