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"