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.


Download Practice Workbook

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


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.

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

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

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

 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.

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

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

Read More: How to Sum All Possible Combinations in Excel


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.

Applying Power Query Feature How to Generate All Possible Combinations of a Set of Numbers in Excel

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

Applying Power Query Feature How to Generate All Possible Combinations of a Set of Numbers in Excel

  • Now go to Add Column > Custom Column.

Applying Power Query Feature How to Generate All Possible Combinations of a Set of Numbers in Excel

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

Applying Power Query Feature How to Generate All Possible Combinations of a Set of Numbers in Excel

  • 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 Apply All Combinations of 3 Columns in Excel


Similar Readings


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.

Incorporating VBA Code

  • First of all, press Alt + F11 to open your Microsoft Visual Basic.
  • Then press Insert > Module to open a blank module.

Incorporating VBA Code

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

Incorporating VBA Code

Read More: How to Find Combinations Without Repetition in Excel


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.

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

Mohammad Shah Miran

Mohammad Shah Miran

Miran is a highly motivated individual with a strong educational background in engineering. He is interested in technology and passionate about creating engaging and informative content. After graduation, Miran decided to pursue a career in content development and has been working in the field for some time. Miran is dedicated to staying current with the latest trends and technologies and is eager to continue learning and growing as a professional.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo