Creating combinations among a list of data is a common phenomenon in our daily lives. Previously this task was accomplished by mathematicians as it requires sufficient knowledge of Mathematics. However, things have changed, and you can do the same thing with your Microsoft Excel application. Well, today we will discuss how to generate all possible combinations of a set of numbers in Excel.

## How to Generate All Possible Combinations of a Set of Numbers in Excel: 3 Methods

Let’s assume we have a dataset, namely “Unique House Number of USA”. You can use any dataset suitable for you.

Here, we have used the **Microsoft Excel 365 **version; you may use any other version according to your convenience.

### 1. Using Excel Formula

The first method we are going to describe is assembling the **IFERROR**, **INDEX**, **COUNTA**, **ROW**, and **MOD** functions together. Though the formula is too hard to grasp at a time, don’t worry we have added a formula breakdown below.

- To begin with this method, enter the following formula in cell
**E5**.

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

** Formula Breakdown:**

**COUNTA($D$5:$D$6))+1)**→ looks into cells**D5**and**D6**and counts the amount of information.- Output→ {3}

**INDEX($B$5:$B$6,INT((ROW(1:1)-1)/((COUNTA($C$5:$C$6)**→ returns a value or the reference to a value from cell**D5**to**D6**.- Output→{11, 12}

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

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

- Now see the output which is given below.

- To get the other value, drag the
**Fill Handle**tool from**E5**to**E12**.

### 2. Applying Power Query Feature

With the help of the **Power Query** feature, you can do the same task that has been described above. Though the procedure may seem tiresome, this will definitely do your job nicely.

- First of all, go to the
**Data tab**>**From Table/Range**command.

- Thus, a prompt will appear named
**Create Table**. Select your data area,**$B$4:$D6$**for instance. - Don’t forget to click on
**My table has header**. - After that, press
**OK**.

- Now go to
**Add Column**>**Custom Column**.

- Create a new column
**Temp,**for instance, then put**1**in the**Custom column formula**. Press**OK**afterward.

- Now, go to the
**Home**tab and click on**Merge Queries > Merge Queries as New**.

- Subsequently, a dialog box like below will appear. Select the
**Temp**column in both tables. - Select
**Full Outer (all rows from both)**from the**Join Kind**option. - Then press the
**OK**button.

- Now go to the
**Home**tab and select the**Close & Load option**.

- Now, see the output given below.

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

### 3. Incorporating VBA Code

Now, if you wish to accomplish your task by incorporating VBA code, then follow the method that we are going to describe below.

For your convenience, a sample dataset has been provided. We will assign our combined data to the selected boxes.

- First of all, press
**Alt + F11**to open your Microsoft Visual Basic. - Then press I
**nsert**>**Module**to open a**blank module**.

- Now, write the following VBA code in your
**Module1.**

```
Sub CombinationsForASetofNumbers()
Dim V1, V2, V3 As Range
Dim RG As Range
Dim xStr As String
Dim Temp1, Temp2, FN3 As Integer
Dim SV1, SV2, SV3 As String
Set V1 = Range("B5:B6")
Set V2 = Range("C5:C6")
Set V3 = Range("D5:D6")
xStr = "-"
Set RG = Range("E5")
For Temp1 = 1 To V1.Count
SV1 = V1.Item(Temp1).Text
For Temp2 = 1 To V2.Count
SV2 = V2.Item(Temp2).Text
For FN3 = 1 To V3.Count
SV3 = V3.Item(FN3).Text
RG.Value = SV1 & xStr & SV2 & xStr & SV3
Set RG = RG.Offset(1, 0)
Next
Next
Next
End Sub
```

** ⚡ Code Breakdown:**

Now, we will explain how the given VBA code works. The code is divided into 2 steps.

- In the first portion of our code, as our dataset includes only three columns, so we have assigned three variables named
**V1, V2**&**V3**. After that, we have specified their range which is indicated in**Box 1.** - In the second portion, a nested loop has been assigned to accomplish our task. Here
**RG**variable will contain merged data, each time the code run by assigning data in**SV1**,**SV2**, and**SV3**variables.

- Press
**F5**to run your VBA Code. - Close your Visual Basic Window.
- Now see the output, which has been given below.

## Practice Section

We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it yourself.

**Download Practice Workbook**

You can download and practice the dataset that we have used to prepare this article.

## Conclusion

Surely, now you can generate all possible combinations of a set of numbers in Excel. Anyway, if you have any queries or suggestions, please let us know in the comments section.

## Related Articles

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

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