CODEWITHSUNDEEP
arraysexcelexcel-formulaexcel-365
Indie
Indie

Reputation: 47

Formula to Concatenate All Combinations of Values Two Columns Without VBA

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!

Upvotes: 1

Views: 4409

Answers (2)

pgSystemTester
pgSystemTester

Reputation: 9932

Updated Nov 2023

Here's a simplified formula using the latest functions that only requires the inputs of ranges put in once, in this case columns A:A and B:B

=LET(first_Range,A:A,second_Range,B:B,TOCOL(FILTER(first_Range,first_Range<>"")
&TRANSPOSE(FILTER(second_Range,second_Range<>""))))

Original Answer

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

Upvotes: 3

Jos Woolley
Jos Woolley

Reputation: 9062

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

Upvotes: 5

Related Questions