This post will guide you how to generate all possible combinations with a formula in excel. How do I create list with all possible combinations of two lists in Excel. How to list all combinations from two separate list or range in Excel.
Table of Contents
1. Generate All Possible Combinations of Two Lists using Formula
Assuming that you have two list of data in different column, A and B, and you want to get a list of all possible combinations from those two list in Column A and B. How to achieve it.
Type this formula into Cell F1, and then drag the AutoFill Handle down column F until you get cells that look empty.
Note: if you want to use this formula into other cell, you need to change the absolute cell reference to that cell. Such as: form F1 to E1.
2. Creating All Possible Combinations Using VBA Code
You can also create all possible combinations of two list using VBA code in Excel. Just do the following steps:
Step1: press Alt + F11 to open the VBA Editor.
Step2: In the VBA Editor, go to the “Insert” menu and select “Module.” This will create a space to write your VBA code.
Step3: Copy and paste the following VBA code for combining two lists:
Sub GenerateCombinations() Dim ws As Worksheet Dim list1Range As Range Dim list2Range As Range Dim outputRange As Range Dim i As Long Dim j As Long Dim rowCount As Long ' Set your worksheet Set ws = ThisWorkbook.Worksheets("Sheet1") ' Change "Sheet1" to your sheet name ' Set your ranges for the two lists Set list1Range = ws.Range("A1:A3") ' Change to your range Set list2Range = ws.Range("B1:B3") ' Change to your range ' Set your output range Set outputRange = ws.Range("C1") ' Change to your desired starting cell rowCount = 1 ' Loop through both lists For i = 1 To list1Range.Rows.Count For j = 1 To list2Range.Rows.Count outputRange.Offset(rowCount, 0).Value = list1Range.Cells(i, 1).Value & " | " & list2Range.Cells(j, 1).Value rowCount = rowCount + 1 Next j Next i End Sub
Step4: Close the VBA Editor and press Alt + F8 to open the “Macro” dialog. Select the macro you created and click “Run“.
3. Video: Creating All Possible Combinations
In this video, we’re going to demonstrate not one, but two methods to create all possible combinations from two lists. The first method harnesses the magic of formulas, employing the IF, ROW, COUNTA, INDEX, and MOD functions. The second method takes advantage of VBA code.
4. Related Functions
- Excel INDEX function
The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
- Excel ROW function
The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
- Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
- Excel MOD function
he Excel MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Excel. The syntax of the MOD function is as below:=MOD (number, divisor)….
- Excel INT function
The Excel INT function returns the integer portion of a given number. And it will rounds a given number down to the nearest integer.The syntax of the INT function is as below:= INT (number)…
- Excel COUNTA function
The Excel COUNTA function counts the number of cells that are not empty in a range. The syntax of the COUNTA function is as below:= COUNTA(value1, [value2],…)…