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

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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.


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

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


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


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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mohammad Shah Miran
Mohammad Shah Miran

Mohammad Shah Miran has a professional background spanning over a year at Softeko. Initially starting as an Excel and VBA writer, he authored more than 50 articles for the ExcelDemy project. Currently, Miran is engaged in the LinuxSimply project as a Linux content developer, completed over 40 articles. His analytical approach extends across various domains, including Excel, VBA, Bash scripting, Linux, data analysis, and Python programming. In his leisure time, Miran enjoys watching movies and series or listening... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo