Formula to Concatenate All Combinations of Values Two Columns Without VBA-CodePudding

I did some digging and couldn't find a similar question. This is easy enough with a VBA loop, but really trying to get the same result with cell formulas only due to compatibility/online sharing.

I have two columns and am trying to concatenat all possible values of the two.

Col1  Col2
A     1
B     2
C
...etc...

So the result is:

A1
A2
B1
B2
C1
C2
...etc...

Ideally looking for a solution that spills so I don't have to drag down a formula, but open to any suggestion that gets the desired result.

TYIA!

CodePudding user response：

Office 365 Beta Channel:

=TOCOL(A1:A3&TRANSPOSE(B1:B2))

Excel 2019 and later, though not suitable for large ranges:

=FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,A1:A3&TRANSPOSE(B1:B2))&"</b></a>","//b")

CodePudding user response：

If you're starting in cell A1 then this formula should work. It spills.

=INDEX(A:A,ROUNDUP(SEQUENCE(COUNTA(A:A)*COUNTA(B:B),1,1,1)/COUNTA(B:B),0),1)
&INDEX(B:B,MOD(SEQUENCE(COUNTA(A:A)*COUNTA(B:B),1,0,1),COUNTA(B:B)) 1,1)