Excel – Every combination of column a and column b into column d and e

microsoft excel

I'm not really sure how to even ask this question. So I took a screenshot of what I'm trying to accomplish.

enter image description here

I'd like to output every combination of columns a and b into column d and e. I've seen many examples of how to combine a and b into one column, but I need them into separate columns.

I'm sure this is possible, I just don't know what to even search for to start

Best Answer

Two formula.

Put this in D1 and copy down:

=IFERROR(INDEX($A$1:INDEX(A:A,COUNTA(A:A)),INT((ROW(1:1)-1)/COUNTA(B:B))+1),"")

Put this in E1 and copy down:

=IF(D1<>"",INDEX($B$1:INDEX(B:B,COUNTA(B:B)),MOD((ROW(1:1)-1),COUNTA(B:B))+1),"")

enter image description here