While dealing with an Excel worksheet, you may need to generate all the combinations of a certain number of columns. Finding these combinations manually would be painstaking and time-consuming. You can use the VBA macros or the Power Query Editor to do the operation. It will save time and make your work easier. Also, you can use a formula to do so. We tried every method possible to gain your understanding. You can go through the article to get a proper visualization for finding all combinations of 6 columns in Excel. So, let’s get started.

## How to Generate All Combinations of 6 Columns in Excel: 2 Methods

We have taken a dataset of numbers in 6 columns. Now, we will try all the combinations of these 6 columns. You can also follow the same methods for 3/4/5 or any number of columns.

Not to mention, we have used the ** Microsoft 365** version. You may use any other version at your convenience.

### 1. Applying VBA Macros

You can use VBA macros to find all the combinations of 6 columns. This code will help you to combine any number of columns. You just need to change the variable number and the function number. Please follow the below steps to use the code.

**📌**** Steps:**

- Firstly, navigate to the
**Developer**tab >> choose**Visual Basic**.

- Consequently, a window appears. Go to the
**Insert**tab >> pick**Module**>>**Module1**.

- In the
**Module 1**section write up the following code.

```
Sub CombinationsFor6Columns()
Dim X1, X2, X3, X4, X5, X6 As Range
Dim RG As Range
Dim xStr As String
Dim FN1, FN2, FN3, FN4, FN5, FN6 As Integer
Dim SV1, SV2, SV3, SV4, SV5, SV6 As String
Set X1 = Range("B5:B7")
Set X2 = Range("C5:C7")
Set X3 = Range("D5:D7")
Set X4 = Range("E5:E7")
Set X5 = Range("F5:F7")
Set X6 = Range("G5:G7")
xStr = "-"
Set RG = Range("H5")
For FN1 = 1 To X1.Count
SV1 = X1.Item(FN1).Text
For FN2 = 1 To X2.Count
SV2 = X2.Item(FN2).Text
For FN3 = 1 To X3.Count
SV3 = X3.Item(FN3).Text
For FN4 = 1 To X4.Count
SV4 = X4.Item(FN4).Text
For FN5 = 1 To X5.Count
SV5 = X5.Item(FN5).Text
For FN6 = 1 To X6.Count
SV6 = X6.Item(FN6).Text
RG.Value = SV1 & xStr & SV2 & xStr & SV3 & xStr & SV4 & xStr & SV5 & xStr & SV6
Set RG = RG.Offset(1, 0)
Next
Next
Next
Next
Next
Next
End Sub
```

In the above code, the **X1** to **X6 **is the range. We have to declare the range column numbers individually. **FN1** to **FN6** is the integer and **SV1** to **SV6** is the string. **RG** is the range of the output cell which is **H5**.

Eventually, run the code with the **F5** key, and you’ll get the result like the image below.

**Read More: **How to Generate All Possible Combinations of a Set of Numbers in Excel

### 2. Employing Power Query

The **Power Query** is a powerful tool in Excel that can be used to solve multiple table-related problems. It can also show all the combinations of the columns you need. Though the procedure is a bit lengthy, it is handy too. See the below steps.

**📌**** Steps:**

- Firstly, select the entire range of the dataset.
- Secondly, hover over the
**Data**tab >> choose the**From Table/Range**.

- Consequently, the
**Create Table**dialog wizard appears. Check the**My table has headers**.

- At this moment, the
**Power Query Editor**appears. Select**Close & Load**>>**Close & Load To**.

- Eventually, check the
**Only Create Connection**from the**Import Data**dialog box.

- Again, navigate to the
**Data**tab >> choose**Get Data**>> pick**Launch Power Query Editor**.

- Subsequently, move to the
**Add Column**tab >> choose**Custom Column**.

- Eventually, the
**Custom Column**dialog wizard appears. Write**Temp**to the**New column****name**. Put**1**to the**Custom column formula**. Hit**OK**.

- Now, from the
**Home**tab of the**Power Query Editor**. Choose**Merge Queries**>>**Merge Queries as New**.

- In addition, the
**Merge**dialog box appears. Select the**Temp**(whatever you named) column in both portions. Make the**Join Kind**as**Full Outer (all rows from both)**.

Finally, you get all the combinations for 6 columns like the image below.

## How to Get All Combinations of 3 Columns Using Formula in Excel

You can also use a formula we use here for 3 column combinations. This is a large formula, and as a result, it may not be feasible for large numbers of columns. It is possible to use formulas for any number of columns, but we are discouraging you from doing so. Because it will take lots of time to write up the code. But in case you want to use it, you can follow the below steps: We have used the **IFERROR**, the **INDEX**, the **COUNTA**, the **ROW**, and the **MOD** functions together here.

**📌**** Steps:**

- Initially, go to the
**E5**cell and insert the formula.

**=IFERROR(INDEX($B$5:$B$7,INT((ROW(1:1)-1)/((COUNTA($C$5:$C$7)*(COUNTA($D$5:$D$7)))))+1)&”-“&INDEX($C$5:$C$7,MOD(INT((ROW(1:1)-1)/COUNTA($D$5:$D$7)),COUNTA($C$5:$C$7))+1)&”-“&INDEX($D$5:$D$7,MOD((ROW(1:1)-1),COUNTA($D$5:$D$7))+1),””)**

Here,

**B5:B7**= Range of Column 1.

**C5:C7**= Range of Column 2.

**D5:D7**= Range of Column 3.

**Formula Breakdown:**

**COUNTA($D$5:$D$7))+1)→** looks into cells **D5 **and **D7** and counts the amount of information.

**INDEX($B$5:$B$7,INT((ROW(1:1)-1)/((COUNTA($C$5:$C$7)→** returns a value or the reference to a value from cell **D5** to **D7**.

**INT((ROW(1:1)-1)/((COUNTA($C$5:$C$7)*(COUNTA($D$5:$D$7)))))+1)→**returns an integer value after dividing the number of “rows-1” by the product of the amount of information in cell **C5** to **C7** and **D5** to **D7**.

**IFERROR(INDEX($B$5:$B$7,INT((ROW(1:1)-1)/((COUNTA($C$5:$C$7)*(COUNTA($D$5:$D$7)))))+1)→** evaluates the formula and returns all possible combinations.

- Press
**ENTER**and drag it down for other cells.

Finally, you get all the combinations for 3 columns with the formula (see the image).

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

## Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.

**Download Practice Workbook**

Download the following practice workbook. It will help you to realize the topic more clearly.

## Conclusion

That’s all about today’s session. These are some easy methods to create combinations of 6 columns in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding please download the practice sheet. Thanks for your patience in reading this article.

Hi Fahim,

This article is pretty helpful to me but I want to use the macros for generating combinations of 4 columns.What modification should be done for that? Can you please explain? Thanks though.

Hello Fred,

Thanks for your appreciation. You can also do it for 4 columns also. All you need to change is the variable, integer and string. Without these changes, all the other code will be the same as before. We also provide the VBA code for the 4 columns below.

`Sub CombinationsFor4Columns()`

`Dim X1, X2, X3, X4 As Range`

`Dim RG As Range`

`Dim xStr As String`

`Dim FN1, FN2, FN3, FN4 As Integer`

`Dim SV1, SV2, SV3, SV4 As String`

`Set X1 = Range("B5:B7")`

`Set X2 = Range("C5:C7")`

`Set X3 = Range("D5:D7")`

`Set X4 = Range("E5:E7")`

`xStr = "-"`

`Set RG = Range("H5")`

`For FN1 = 1 To X1.Count`

`SV1 = X1.Item(FN1).Text`

`For FN2 = 1 To X2.Count`

`SV2 = X2.Item(FN2).Text`

`For FN3 = 1 To X3.Count`

`SV3 = X3.Item(FN3).Text`

`For FN4 = 1 To X4.Count`

`SV4 = X4.Item(FN4).Text`

`RG.Value = SV1 & xStr & SV2 & xStr & SV3 & xStr & SV4`

`Set RG = RG.Offset(1, 0)`

`Next`

`Next`

`Next`

`Next`

`End Sub`

Hope the above code solves your problem. Keep supporting us.

Regards

Fahim Shahriyar Dipto

Excel & VBA Content Developer

Is it possible to generate combination for 3 columns where we have 5 numbers of rows?

Hello Maurice,

Thanks for your feedback. In your query, as you wanted to create the combination for 3 columns and five rows, you have to change the VBA code variables to X1, X2, and X3. Also, you need to change the row range. Don’t worry, We have added the code for your betterment. Follow the code and your work will be done.

`Sub CombinationsFor3Columns()`

`Dim X1, X2, X3 As Range`

`Dim RG As Range`

`Dim xStr As String`

`Dim FN1, FN2, FN3, FN4 As Integer`

`Dim SV1, SV2, SV3, SV4 As String`

`Set X1 = Range("B5:B9")`

`Set X2 = Range("C5:C9")`

`Set X3 = Range("D5:D9")`

`xStr = "-"`

`Set RG = Range("D5")`

`For FN1 = 1 To X1.Count`

`SV1 = X1.Item(FN1).Text`

`For FN2 = 1 To X2.Count`

`SV2 = X2.Item(FN2).Text`

`For FN3 = 1 To X3.Count`

`SV3 = X3.Item(FN3).Text`

`RG.Value = SV1 & xStr & SV2 & xStr & SV3`

`Set RG = RG.Offset(1, 0)`

`Next`

`Next`

`Next`

`End Sub`

Hope the above code solves your problem. Keep supporting us.

Regards

Fahim Shahriyar Dipto

Excel & VBA Content Developer

I tried the first option to get all the possible combinations of 6 digits from a list of 15 numbers, and I got an error message saying that there was an error on: “Set RG = RG.Offset(1, 0)”. Could you check what I did wrong? This is the code that I used:

Sub CombinationsFor6Columns()

Dim X1, X2, X3, X4, X5, X6 As Range

Dim RG As Range

Dim xStr As String

Dim FN1, FN2, FN3, FN4, FN5, FN6 As Integer

Dim SV1, SV2, SV3, SV4, SV5, SV6 As String

Set X1 = Range(“B5:B19”)

Set X2 = Range(“C5:C19”)

Set X3 = Range(“D5:D19”)

Set X4 = Range(“E5:E19”)

Set X5 = Range(“F5:F19”)

Set X6 = Range(“G5:G19”)

xStr = “-”

Set RG = Range(“H5”)

For FN1 = 1 To X1.Count

SV1 = X1.Item(FN1).Text

For FN2 = 1 To X2.Count

SV2 = X2.Item(FN2).Text

For FN3 = 1 To X3.Count

SV3 = X3.Item(FN3).Text

For FN4 = 1 To X4.Count

SV4 = X4.Item(FN4).Text

For FN5 = 1 To X5.Count

SV5 = X5.Item(FN5).Text

For FN6 = 1 To X6.Count

SV6 = X6.Item(FN6).Text

RG.Value = SV1 & xStr & SV2 & xStr & SV3 & xStr & SV4 & xStr & SV5 & xStr & SV6

Set RG = RG.Offset(1, 0)

Next

Next

Next

Next

Next

Next

End Sub

Hello

LUIS CARRERAThanks for sharing your problem. The error you are facing is like the following:

On the line below:The error arises from setting the

range offsetoutside theworksheet’s boundaries. The rangeRGstarts at cellH5, and when you try to set theoffset to (1, 0), it might go beyond the last row of the worksheet.However, I am presenting an

Excel VBAcode to handle that error.Excel VBA Code:Hopefully, the code will work perfectly for you. Good luck.

Regards

Lutfor Rahman Shimanto