How to list or generate all possible combinations in Excel? (2023)

How to list or generate all possible combinations in Excel? (1)

Let’s say, I have the following two columns of data, and now, I want to generate a list of all possible combinations based on the two lists of values as left screenshot shown. Maybe, you can list all the combinations one by one if there are few values, but, if there are several columns with multiple values needed to be listed the possible combinations, here are some quick tricks may help you to deal with this problem in Excel.

List or generate all possible combinations from two lists with formula

List or generate all possible combinations from three or more lists with VBA code

List or generate all possible combinations from multiple lists with a powerful feature

List or generate all possible combinations from two lists with formula

The following long formula can help you to list all possible combinations of two lists values quickly, please do as follows:

(Video) Create all possible combinations of two lists of values in Excel

1. Enter or copy the below formula into a blank cell, in this case, I will enter it to cell D2, and then press Enter key to get the result, see screenshot:

=IF(ROW()-ROW($D$2)+1>COUNTA($A$2:$A$5)*COUNTA($B$2:$B$4),"",INDEX($A$2:$A$5,INT((ROW()-ROW($D$2))/COUNTA($B$2:$B$4)+1))&"-"&INDEX($B$2:$B$4,MOD(ROW()-ROW($D$2),COUNTA($B$2:$B$4))+1))

How to list or generate all possible combinations in Excel? (2)

Note: In the above formula, $A$2:$A$5 isthe range of the first column values, and $B$2:$B$4 is the range of the second list values which you want to list all their possible combinations, the $D$2 is the cell that you put the formula, you can change the cell references to your need.

2. Then select cell D2 and drag the fill handle down to the cells until get the blank cells, and all the possible combinations have been listed based on the two lists values. See screenshot:

How to list or generate all possible combinations in Excel? (3)

List or generate all possible combinations from three or more lists with VBA code

Maybe the above formula is somewhat difficult for you to apply, if there are multiple columns data, it will be troublesome for modifying. Here, I will introduce a VBA code to deal with it quickly.

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Generate all combinations of 3 or multiple columns

(Video) List Or Generate All Possible Combinations In Excel

Sub ListAllCombinations()'Updateby ExtendofficeDim xDRg1, xDRg2, xDRg3 As RangeDim xRg As RangeDim xStr As StringDim xFN1, xFN2, xFN3 As IntegerDim xSV1, xSV2, xSV3 As StringSet xDRg1 = Range("A2:A5") 'First column dataSet xDRg2 = Range("B2:B4") 'Second column dataSet xDRg3 = Range("C2:C4") 'Third column dataxStr = "-" 'SeparatorSet xRg = Range("E2") 'Output cellFor xFN1 = 1 To xDRg1.Count xSV1 = xDRg1.Item(xFN1).Text For xFN2 = 1 To xDRg2.Count xSV2 = xDRg2.Item(xFN2).Text For xFN3 = 1 To xDRg3.Count xSV3 = xDRg3.Item(xFN3).Text xRg.Value = xSV1 & xStr & xSV2 & xStr & xSV3 Set xRg = xRg.Offset(1, 0) Next NextNextEnd Sub

Note: In the above code, A2:A5, B2:B4, C2:C4 are the data range that you want to use, E2 is the output cell that you want to locate the results. If you want to get all combinations of more columns, please change and add other parameters to the code as your need.

3. Then, press F5 key to run this code, and all combinations of the 3 columns will be generated at once, see screenshot:

How to list or generate all possible combinations in Excel? (4)

List or generate all possible combinations from multiple lists with a powerful feature

If there are multiple lists values need to be listed the possible combinations, maybe it is difficult for you to modify the code. Here, I can recommend a powerful tool -- Kutools for Excel, it contains a handy feature List All Combinations which can quickly list all the possible combinations based on given data lists.

Tips:To apply this List All Combinationsfeature, firstly, you should download the Kutools for Excel, and then apply the feature quickly and easily.

After installing Kutools for Excel, please do as this:

1. Click Kutools > Insert > List All Combinations, see screenshot:

How to list or generate all possible combinations in Excel? (5)

2. In the List All Combinations dialog box, do the operations as below demo shown:

How to list or generate all possible combinations in Excel? (6)

(Video) Get all possible combinations from multiple lists in Excel using Power Query | Excel Off The Grid

3. Then all the specified values and separators have been listed into the dialog box, see screenshot:

How to list or generate all possible combinations in Excel? (7)

4.And then click Ok button, and a prompt box will pop out to remind you select a cell to output the result, see screenshot:

How to list or generate all possible combinations in Excel? (8)

5. Click OK, all of the possible combinations based on the given lists have been generated into the worksheet as following screenshot shown:

How to list or generate all possible combinations in Excel? (9)

Click to Download Kutools for Excel Now !

More relative articles:

  • Generate All Combinations Of 3 Or Multiple Columns
  • Supposing, I have 3 columns of data, now, I want to generate or list all combinations of the data in these 3 columns as below screenshot shown. Do you have any good methods for solving this task in Excel?
  • Find All Combinations That Equal A Given Sum
  • For example, I have the following list of numbers, and now, I want to know which combination of numbers in the list sum up to 480, in the following screenshot shown, you can see there are five groups of possible combinations that add up equal to 480, such as 300+60+120, 300+60+40+80, etc. This article, I will talk about some methods to find which cells sum up to a specific value in Excel.
  • Generate Or List All Possible Permutations
  • For example, I have three characters XYZ, now, I want to list all possible permutations based on these three characters to get six different results as this: XYZ, XZY, YXZ, YZX, ZXY and ZYX. In Excel, how could you quickly generate or list all permutations based on different number of characters?
(Video) Create All Possible Combinations from Two or More Lists in Power Query
  • Generate A List Of All Possible 4 Digits Combinations
  • In some cases, we may need to generate a list of all possible 4 digits combinations of number 0 to 9, which means to generate a list of 0000, 0001, 0002…9999. To quickly solve the list task in Excel, I introduce some tricks for you.

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, chartsand anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

Read More... Free Download... Purchase...

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

Read More... Free Download... Purchase...

(Video) Create a Table that Shows All Combinations of Different List Items

FAQs

How do you get all possible combinations? ›

How to calculate combinations? - combination formula
  1. C(n,r) is the number of combinations;
  2. n is the total number of elements in the set; and.
  3. r is the number of elements you choose from this set.
6 Apr 2022

How do you generate random combinations in Excel? ›

Go on to select the Column G, and type this formula =INDEX(A$1:A$4,D1) into the formula bar, and press Ctrl + Enter keys to get the result. See screenshot: Tip: A1 is the first cell of the first list, and D1 is the first cell of the column contains your first formula.

Can Excel find a combinations that equal given sum? ›

Find cells combination that equal a given sum with Solver Add-in. If you are confused with above method, Excel contains a Solver Add-in feature, by using this add-in, you can also identify the numbers which total amount equals a given value.

How do I get all the combinations of two columns in Excel? ›

Joining queries to find all combinations of two lists

Once the queries from the tables are ready, go to Data > Get Data > Combine Queries > Merge to open the Merge dialog of Power Query. Select each table in the drop downs. Click on the column for each table to select them.

How many possible combinations are there with 4 answers? ›

If I've remembered this correctly, this is a problem that can be solved using a factorial function? I.e. there are 4 objects, so the total number of possible combinations that they can be arranged in is 4! = 4 x 3 x 2 x 1 = 24.

Can Excel Solver find multiple solutions? ›

If you're working on a problem and want to see the multiple possible solutions, then using the Solver tool may be a good way for you to calculate the answers. Learning how to use this Excel tool can prepare you to work through real-world scenarios that require mathematical analysis to understand.

How do you find all possible combinations of two numbers? ›

To find all the combinations of two numbers, we multiply the number of possible outcomes for the first number by the number of possible outcomes for the second number. Let us look at this with an example. If the numbers we can use are 0 through 9, then there will always be 10 possible outcomes for the first number.

How do you know how many variations possible? ›

The number of variations can be easily calculated using the combinatorial rule of product. For example, if we have the set n = 5 numbers 1,2,3,4,5 and we have to make third-class variations, their V3 (5) = 5 * 4 * 3 = 60. Vk(n)=n(n−1)(n−2)...

How many combinations are possible with 5 items? ›

So we say that there are 5 factorial = 5! = 5x4x3x2x1 = 120 ways to arrange five objects.

How do you get all the combinations of characters in a string? ›

charAt(0); String st=s. substring(1); Set<String> qq=find(st); for(String str:qq) { for(int i=0;i<=str. length();i++) { ss. add(comb(str,c,i)); } } } return ss; } public static String comb(String s,char c,int i) { String start=s.

How do you calculate combinations without repetitions in Excel? ›

The COMBIN Function[1] is an Excel Math and Trigonometry function. The function will calculate the number of combinations without repetitions for a given number of items.

What are all the possible combinations of 1234? ›

1234, 1243, 1423, 4123, 1324, 1342, 1432, 4132, 3124, 3142, 3412, 4312, 2134, 2143, 2413, 4213, 2314, 2341, 2431, 4231, 3214, 3241, 3421, 4321.

How long does it take to try all combinations of 4 numbers? ›

Trialing this in a combination-lock by hand, assuming that you can turn a dial at a rough rate of one position per second, would take you 10 000 seconds (2hours 46mins 40secs) and you are guaranteed to get there eventually - you'd look rather suspicious though, and very few people would try every single combination ...

How many combinations can be made with 6 numbers? ›

One way you could think about it is that each combination of six numbers occurs from 0 (000000) to 999,999. That means that there are 1,000,000 combinations (since you're counting zero as well as every number from 1 to 999,999) .

How many combinations of 6 options are there? ›

So for 6 items the equation is as follows 6*5*4*3*2= 720 possible combinations of 6 items. If you can choose to use less of the items in a sequence that changes things. 1 item used = 6 possibilities .

What are all the 6 digit combination possibilities? ›

There are 900000 possible ways to get 6 digit numbers using 0 - 9.

How many combinations are possible with ABCD? ›

Total possible arrangement of letters a b c d is 24.

How many combinations of 12 numbers are there? ›

In your case, with 12 numbers, the number is 12x11x10x... x2x1=479001600. This number is called "twelve factorial" and written 12!, so, for example 4!=

How many combinations can you make with 5 numbers? ›

In total you will find 5 × 24 = 120 possibilities.

How do you generate all permutations of an array? ›

You take first element of an array (k=0) and exchange it with any element (i) of the array. Then you recursively apply permutation on array starting with second element. This way you get all permutations starting with i-th element.

How do you find the number of unique combinations? ›

The number of combinations of n distinct objects, taken r at a time is: Cr = n! / r! (n - r)!

How many combinations of 3 can you make with 5 items? ›

So 5 choose 3 = 10 possible combinations.

How many combinations are possible with 10 items? ›

With replacement means the same item can be chosen more than once. Without replacement means the same item cannot be selected more than once. 10 X 10 X 10 X 10 = 10,000 combinations are possible.

How many combinations of 7 things are there? ›

Answer and Explanation: The number of combinations that are possible with 7 numbers is 127. In general, the formula we use to determine the number of combinations possible with n elements is as follows: Number of combinations possible with n elements = 2n - 1.

How many possible combinations of 3 items from a group of 4 are possible? ›

There are 4 * 3 * 2 = 24 combinations if repetition is not allowed. If repetition is allowed, there are 4 * 4 * 4 = 64 combinations.

Videos

1. Excel List All Lottery Combinations - 2441
(MrExcel.com)
2. How To Generate All Possible Combination Of Items From Two Lists
(How To Excel)
3. COMBIN Function in Excel 2013 - Calculate Combinations and Permutations
(Excel Destination)
4. Combinations Generator - Free Excel Template
(Indzara Excel Templates)
5. Combinations Generator Excel Template Multiple Lists
(Indzara Excel Templates)
6. How to make Excel list all possible combinations
(Lotto Combination)
Top Articles
Latest Posts
Article information

Author: Manual Maggio

Last Updated: 02/06/2023

Views: 6608

Rating: 4.9 / 5 (49 voted)

Reviews: 88% of readers found this page helpful

Author information

Name: Manual Maggio

Birthday: 1998-01-20

Address: 359 Kelvin Stream, Lake Eldonview, MT 33517-1242

Phone: +577037762465

Job: Product Hospitality Supervisor

Hobby: Gardening, Web surfing, Video gaming, Amateur radio, Flag Football, Reading, Table tennis

Introduction: My name is Manual Maggio, I am a thankful, tender, adventurous, delightful, fantastic, proud, graceful person who loves writing and wants to share my knowledge and understanding with you.