How to Get All Combinations of 2 Columns in Excel (4 Easy Ways)

In this article, we will discuss how to get all combinations of 2 columns in Excel. Users often need to combine the values of two columns to get an output. The combined output can be the representative of both the columns. It also allows users to permutate and get all the possible values possible from the combinations.

How to Get All Combinations of 2 Columns in Excel: 4 Suitable Ways

In this article, we will talk about 4 ways to get all combinations of 2 columns in Excel. Firstly, we will use the Power Query feature to get the job done. Secondly, we will combine the IF, ROW, COUNTA, and INDEX functions together to form a formula. Thirdly, we will use helper rows and combined functions to do the task. Finally, we will resort to a VBA Code to get all the possible combinations of 2 columns in Excel.

1. Using Excel Power Query Editor to Combine 2 Columns

The Power Query is a very powerful feature in Excel. It allows users to organize and manipulate data according to their desires. In this method, we will use this feature to merge two columns and get all the combinations of their values.

Steps:

• Firstly, select the B5 cell in the Gender column.
• Secondly, go to the Insert tab.
• Finally, select Table.
• As a result, a prompt will be on the screen.

• In the prompt, select B5:B6 as the table range and click on
• As a result, the range will be converted into a table.

• Repeat the process for the Category column and turn it into a table as well.

• Now, select the B5 cell and go to the Data tab.
• From the Data tab, select From Table/Range.

• Consequently, the column will be opened in the Power Query window.

• In the Power Query window, first, go to the Home tab.
• Then, select Close & Load To from the Close & Load option.
• Consequently, a prompt will be on the screen.

• From the prompt, first, choose the Only Create Connection option.
• Then, click on OK.

• Consequently, a connection table sign will appear on the right side of the sheet.

• Repeat the same steps for the Category table.
• As a result, there will be two table connections.

• Now, choose Table4.
• As a result, the Power Query window will be opened.

• In the Power Query window, first, go to the Add Column.
• Then, select Custom Column.

• Then, in the Custom Column prompt, write Table5.
• Next, click OK.

• As a result, the Custom column will be added beside the Gender column.
• Now, select the double arrow sign in the top right corner of the column.

• Next, select OK from the outstretching window.

• Consequently, the Category column will appear beside the Gender column.

• After that, go to the Add Column tab.
• From there, select Merge Columns.
• Consequently, a prompt will be on the screen.

• In the prompt, first, select Custom.
• Secondly, type “-” as the separator.
• Thirdly, rename the merged column as Products.
• Finally, choose OK.

• As a result, we will have all the combinations of the 2 columns in Power Query.

• Now, open a new worksheet.
• Then, go to the Data tab.
• From there, select Queries & Connections.
• As a result, the list of tables will appear on the right side of the sheet.

• After that, choose the B4 cell.
• Then, right-click on one of the tables.
• Finally, from the available options, select Load To.
• Consequently, a prompt will appear on the screen.

• From the prompt, first, select Table.
• Then, select the B4 cell as the cell to insert the table.
• Finally, click OK.

• Consequently, we will get all the combinations of the 2 columns.

2. Applying Combined Excel Functions

In this instance, we will combine the IF, ROW, COUNTA, INDEX, and MOD functions to form a gigantic formula. However, the purpose of this formula is very simple. We will use the formula to get all the combinations of 2 columns.

Steps:

• To begin with, click on the D5 cell and enter,

• Then, press Enter.

• As a result, we will have the first combination of the two columns.
• Now, lower the cursor down to autofill the rest of the cells.

3. Using Helper Columns and Combined Functions

In this example, we use different functions like the COUNTA, QUOTIENT, MOD, ROW, IF, COUNTIF, and INDEX functions in different columns and cells to arrange our data. Finally, we will use the CONCATENATE function to combine the two columns and get all the possible combinations of a set of numbers.

Steps:

• To start with, choose the C9 cell and type,

• Then, hit Enter.
• As a result, we will have the counting of the values in the Gender column.

• Next, select the C10 cell and type,

• Hit Enter.
• Consequently, we will get the number of values under the Category column.

• Next, choose the E5 cell and enter the following,

• Then, press Enter.

• As a result, we will assign the row number for the first value of the Gender column.
• Then, move the cursor down and autofill the rest of the cells.

• Next, click on the F5 cell and type the following,

• Press Enter. 

• Consequently, we will get the row number for the first value of the Category column.
• Next, lower the cursor down to autofill.

• Thereafter, choose the G5 cell and insert,

• Then, press the Enter button.

• As a result, we will have the first value of the Gender column associated with the value in the Gender Row.
• Finally, lower the cursor to autofill the rest of the cells.

• After that, choose the H5 cell and type,

• Then, press Enter.

• Consequently, we will have the first value of the Category column associated with the value in the Category Row.
• Move the cursor down to the last cell to autofill.

• Finally, choose the I5 cell and enter the following formula,

• Then, press Enter.

• As a result, we will have our first combination of the 2 columns.
• Finally, lower the cursor to autofill the rest of the cells.

4. Applying VBA Code

In this final method, we will resort to a VBA Code to get all the combinations of 2 columns in Excel. This code will take values from each column and run them in the loop to give a combined output.

Steps:

• Firstly, go to the Developer tab.
• From there, select the Visual Basic tab.
• As a result, a new window will be on the screen.

• From the Visual Basic window, first, select Insert.
• Then, choose Module from the drop-down list.
• Consequently, a new module will appear.

• In the module, write down the following code and save it.

Sub AllCombinationsTwoColumns()
Dim am_r1, am_r2 As Range
Dim am_r  As Range
Dim am_str As String
Dim am_int1, am_int2 As Integer
Dim am_str1, am_str2 As String
Set am_r1 = Range("B5:B6")
Set am_r2 = Range("C5:C7")
am_str = "-"
Set am_r = Range("D5")
For am_int1 = 1 To am_r1.Count
    am_str1 = am_r1.Item(am_int1).Text
    For am_int2 = 1 To am_r2.Count
        am_str2 = am_r2.Item(am_int2).Text
        am_r.Value = am_str1 & am_str & am_str2
        Set am_r = am_r.Offset(1, 0)
       Next
    Next
End Sub

• Finally, run the code by clicking on the triangular-shaped green button.

• As a result, we will have all the combinations of 2 columns in the D5:D10 cell range.

Download Practice Workbook

You can download the practice workbook here.

Conclusion

In this article, we have discussed 4 ways to get all the combinations of 2 columns in Excel. These methods will allow users to get the right combination from all the combinations of the values of the 2 columns. If you have any questions regarding this essay, feel free to let us know in the comments.

Related Articles

• How to Sum All Possible Combinations in Excel
• How to Find Combinations Without Repetition in Excel
• How to Find All Combinations of 1 Column in Excel
• How to Apply All Combinations of 3 Columns in Excel
• How to Create All Combinations of 4 Columns in Excel
• How to Show All Combinations of 5 Columns in Excel
• How to Create All Combinations of 6 Columns in Excel

<< Go Back to Excel COMBIN Function | Excel Functions | Learn Excel