Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

Creating combinations among a list of data is a common phenomenon in our daily life. 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.

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

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, assemble IFERROR, the INDEX, the COUNTA, the ROW, and the 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. ### 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 Insert > 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. ## 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  