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

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

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

Solution 1:

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

Solution 2:

use Microsoft's Get & Transform power tools for making a cartesian product of two variables (or table), if you use Excel 2016.
(For Excel 2010-2013, use the free MS add-in Power Query for Excel.)

Similar to the way described here, you can

• import both columns as separated table in Power Query (Excel 2016: ribbon Data > From Table)
• then add an helper column with the same value (e.g. 1),
• merge both table
• keep the original imported column

You could theoretically make a cartesian product not only from two column but also from two tables of several columns.

Here the M-Code of both table:

Table2

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "CrossColumn", each 1)
in
    #"Added Custom"

Table 1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "CrossColumn", each 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom",{"CrossColumn"},Table2,{"CrossColumn"},"Table2",JoinKind.Inner),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Column2"}, {"Column2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded {0}",{"CrossColumn"})
in
    #"Removed Columns"