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 the
**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.

**P:S-**Here, we could not use the

**Close & Load**command to load the

**Power Query**table in Excel sheet because we created the tables by applying

**Connection Only**. So, we had to transform it into table before inserting it into an Excel sheet.

**Read More: ****How to Create All Combinations of 4 Columns in Excel**

### 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,

`=IF(ROW()-ROW($D$5)+1>COUNTA($B$5:$B$6)*COUNTA($C$5:$C$7),"", INDEX($B$5:$B$6,INT((ROW()-ROW($D$5))/COUNTA($C$5:$C$7)+1))&"-"&INDEX($C$5:$C$7,MOD(ROW()-ROW($D$5),COUNTA($C$5:$C$7))+1))`

- Then, press
**Enter.**

** **

**Formula Breakdown**

**ROW()-ROW($D$5)+1>COUNTA($B$5:$B$6)*COUNTA($C$5:$C$7):**Here, the**ROW()**returns the value of the row of the**D5**cell which is So as the**ROW($D$5).**Now, the formula becomes**5-5+1>COUNTA($B$5:$B$6)*COUNTA($C$5:$C$7)**. The**COUNTA($B$5:$B$6)**and**COUNTA($C$5:$C$7)**returns the number of non-empty cells in the range**B5:B6**and**C5:C7**which are**2**and**3**respectively. So the expression becomes**1>2*3**or**1>6**which is**False.**- Output:
**False**

- Output:
**INDEX($B$5:$B$6,INT((ROW()-ROW($D$5))/COUNTA($C$5:$C$7)+1)):**The**(ROW()-ROW($D$5)**returns The**COUNTA($C$5:$C$7)**expression counts the number of non-empty cell in the**C5:C7**range and returns**3.**So, the expression becomes**INDEX($B$5:$B$6,INT(0/3+1))**and will become**INDEX($B$5:$B$6,INT(0+1))**or**INDEX($B$5:$B$6,INT(1)) .**The**INT**function will return**1.**The final expression will be**INDEX($B$5:$B$6,1).**Finally, the**INDEX**function will return the 1st value of the**B5:B6**range which is**Male.**- Output:
**Male**

- Output:
**INDEX($C$5:$C$7,MOD(ROW()-ROW($D$5),COUNTA($C$5:$C$7))+1)):**The**MOD(ROW()-ROW($D$5),COUNTA($C$5:$C$7))+1**will become**MOD(0,3)+1.**The**MOD**function will return the remainder of the division between**0**and**3**which is So, the**INDEX($C$5:$C$7,MOD(ROW()-ROW($D$5),COUNTA($C$5:$C$7))+1))**becomes**INDEX($C$5:$C$7,0+1))**or**INDEX($C$5:$C$7,1)).**Thus, the expression will return the first value of the cell range**C5:C7**and that will be**Jacket.**- Output:
**Jacket**

- Output:
**IF(ROW()-ROW($D$5)+1>COUNTA($B$5:$B$6)*COUNTA($C$5:$C$7),””, INDEX($B$5:$B$6,INT((ROW()-ROW($D$5))/COUNTA($C$5:$C$7)+1))&”-“&INDEX($C$5:$C$7,MOD(ROW()-ROW($D$5),COUNTA($C$5:$C$7))+1)):**This expression becomes**IF(False,#NA, “Male” &”-“& “Jacket”).**So, the expression is asking to concatenate the**Male**, – , and**Jacket**if the condition is- Output:
**Male-Jacket**

- Output:

- 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.

**Read More: ****How to Apply All Combinations of 3 Columns in Excel**

### 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,

`=COUNTA(B5:B6)`

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

`=COUNTA(C5:C7)`

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

- Next, choose the
**E5**cell and enter the following,

`=QUOTIENT(ROW(E5)-4,$C$10)+IF(MOD(ROW(E5)-4,$C$10)=0,0,1)`

- Then, press
**Enter.**

** **

**Formula Breakdown**

**QUOTIENT(ROW(E5)-4,$C$10):**The**ROW(E5)-4**expression will become**5-4**or So the overall expression be**QUOTIENT(1,$C$10)**or**QUOTIENT(1,3).**The**QUOTIENT**function will return the integer part of the division of**1**by**3**that is**0.**- Output:
**0**

- Output:
**IF(MOD(ROW(E5)-4,$C$10)=0,0,1):**The**ROW(E5)-4**expression will return The**MOD(ROW(E5)-4,$C$10)**will become**MOD(1,3)**which returns**1.**So, the overall expression will be**IF(1=0,0,1)**or**IF(False,0,1).**Finally, the formula will return**1**.- Output:
**1**

- Output:
**QUOTIENT(ROW(E5)-4,$C$10)+IF(MOD(ROW(E5)-4,$C$10)=0,0,1):**The entire expression will become**0+1**or- Output:
**1**

- Output:

** **

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

- Next, click on the
**F5**cell and type the following,

`=COUNTIF($E$5:E5,E5)`

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

`=INDEX($B$5:$B$6,E5)`

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

`=INDEX($C$5:$C$7,F5)`

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

`=CONCATENATE(G5,"-",H5)`

- 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.

**Read More: ****How to Show All Combinations of 5 Columns in Excel**

**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.