How to Generate All Possible Combinations of a Set of Numbers in Excel (3 Methods)

Dataset Overview

Let’s assume we have a dataset, namely Unique House Number of USA.

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

Method 1 – Using Excel Formula

This method involves combining several functions (IFERROR, INDEX, COUNTA, ROW, and MOD) to achieve the desired result. Although the formula may appear complex at first glance, let’s break it down step by step:

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)→ counts the amount of information in cells D5 and D6.
  - Output→ {3}

- INDEX($B$5:$B$6,INT((ROW(1:1)-1)/((COUNTA($C$5:$C$6)→ returns values from cell B5 to B6.
  - Output→{11, 12}

- INT((ROW(1:1)-1)/((COUNTA($C$5:$C$6)*(COUNTA($D$5:$D$6)))))+1)→calculates an integer value based on row number and information count.
  - 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.

Drag the Fill Handle tool from E5 to E12 to get other values.

Method 2 – Applying Power Query Feature

Go to the Data tab > From Table/Range.

Select your data area (e.g., $B$4:$D6) and ensure My table has header is checked.
Press OK.

Go to Add Column > Custom Column.

Create a new column (e.g., Temp) with the formula 1.

Go to the Home tab and click on Merge Queries > Merge Queries as New.

Select the Temp column in both tables and choose Full Outer (all rows from both).
Press OK.

Go to the Home tab and select the Close & Load option.

Herewith the output:

Method 3 – Incorporating VBA Code

A sample dataset has been provided and we will assign our combined data to the selected boxes.

Press Alt + F11 to open Microsoft Visual Basic.
Insert > Module to open a blank module.

Enter the following VBA code in 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:

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 the Visual Basic Window.

Now you have the output with all possible combinations:

Practice Section

We have provided a Practice section on the right side of each sheet so you can practice yourself.