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

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

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.

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

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

Method 2 – Applying Power Query Feature

Go to the Data tab and select From Table/Range.

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

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

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

Go to Add Column and choose Custom Column.

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

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

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

Go to the Home tab, click on Merge Queries and select 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.

Incorporating VBA Code

Press Alt + F11 to open Microsoft Visual Basic.
Select Insert and choose Module to open a blank module.

Incorporating VBA Code

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.