0

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!

JvdV
  • 70,606
  • 8
  • 39
  • 70
Indie
  • 37
  • 6

2 Answers2

5

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

Jos Woolley
  • 8,564
  • 2
  • 4
  • 9
  • I thought of `FilterXML` but I couldn't get it to be dynamic. Your result won't hold if the number of rows in either column changes, which was something OP noted would be ideal. – pgSystemTester May 22 '22 at 13:39
  • @pgSystemTester It's very easy to make dynamic ranges. In fact, I assumed that the OP would know how to do that, so I left it out. I can show you if you're not sure how? Otherwise, we could wait to see if the OP also needs help in that area. – Jos Woolley May 22 '22 at 14:52
  • yeah I actually looked at your formula again not too long after I commented and realized I could just do vector formula. -- this seems to be the best of both worlds: `=FILTERXML(""&TEXTJOIN("",,A1:INDEX(A:A,COUNTA(A:A),1)&TRANSPOSE(B1:INDEX(B:B,COUNTA(B:B),1)))&"","//b")` Nice answer. – pgSystemTester May 22 '22 at 17:19
  • Thank you both! Jos, `TOCOL` worked perfectly and is definitely the most concise and elegant solution here. When I tried the `FILTERXML` version it caused a resource allocation error. pgSystemTester your take on `FILTERXML` did the trick as well (plus your 'Index' version below.) – Indie May 22 '22 at 21:23
2

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)

enter image description here

pgSystemTester
  • 8,979
  • 2
  • 23
  • 49