Generate all Possible Unique Combinations of two Excel Columns

Question

I have the following simplified data set which I need to create a unique list from and transpose the data from column B at the same time. I think I need to use INDEX, but I am unsure on the correct syntax for this scenario.

The data in column B is delimited by a space.

This is what my data looks like:

|---------------------|------------------|
|      Column A       |      Column B    |
|---------------------|------------------|
|          1          |       AA BB      |
|---------------------|------------------|
|          2          |       BB CC      |
|---------------------|------------------|
|          3          |       DD EE      |
|---------------------|------------------|

Required result

|---------------------|------------------|
|      Column A       |      Column B    |
|---------------------|------------------|
|          1          |         AA       |
|---------------------|------------------|
|          1          |         BB       |
|---------------------|------------------|
|          2          |         BB       |
|---------------------|------------------|
|          2          |         CC       |
|---------------------|------------------|
|          3          |         DD       |
|---------------------|------------------|
|          3          |         EE       |
|---------------------|------------------|

If unique, why does ***BB*** appear **twice** in the output ?? — Gary's Student, May 05 '19 at 21:13

score 1 · Accepted Answer · answered May 06 '19 at 01:18

To get your output table given your input table, you can use Power Query, from the UI, in just a few steps:

Split Column B by the space delimiter.
Select Column A and then select to unpivot other columns
Delete the extra column Attribute that appears when you unpivot.

This is the M code for that operation

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column2", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column2.1", "Column2.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column2.1", type text}, {"Column2.2", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Column1"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"})
in
    #"Removed Columns"

And the results:

Christopher Peisert · Answer 2 · 2019-05-06T04:38:44.410

Ron Rosenfeld's answer unpivots the data as the OP indicated by the required result.

If you need to create all combinations of data from two columns (rather than unpivoting), normalize the data by placing each set of values in its own column. In this example, Column B has two data entries per cell, which can be split using Data > Text to Columns. To work with unique entries, either use the standard Excel tool Data > Remove Duplicates, or in Excel Power Query Editor, right click the data column header and click Remove Duplicates.

Create separate queries for each column to be included in the combinations. By adding a custom column with a formula referring to the first data query, Power Query will perform a Full Outer Join across the two columns resulting in all combinations.