Create all possible combinations from different ranges without using VBA

{ 124 Comments }

By Ashish Mathur · · · Leave a comment

Assume numbers from 1 to 8 in range B3:B10 and numbers from 9 to 16 in range C3:C10.  The objective is to create all possible combinations in column E from these two ranges without using VBA (macros).  This combined range of all possible combinations is called a Cartesian product.

You may refer to the following steps to create all possible combinations in column E

1. Type a heading in cell B2, say Data Set1.  Format cell B2 to Bold (Ctrl+B).
2. Type a heading in cell C2, say Data Set2.  Format cell C2 to Bold (Ctrl+B).
3. Select range B2:B10, press Ctrl+F3 > New and in the name box, type Data_set1
4. Select range C2:C10, press Ctrl+F3 > New and in the name box, type Data_set2
5. Select range B2:B10 and press Ctrl+T > OK
6. Select range C2:C10 and press Ctrl+T > OK
7. Save the file at your desired location, say on your desktop
8. Select cell E2 and go to Data > From Other Sources > From Microsoft Query > Excel Files > OK
9. In the Select Workbook dialog box, navigate to the folder and select the file which you just saved on the desktop (in step 7 above) and click on OK
10. With Data_set1 selected, click on the > symbol
11. Select Data_set2 and click on the > symbol
12. Click on Next > OK
13. Go to File > Return Data to Microsoft Excel
14. In the Import Data box, select cell E2 in the Existing Worksheet box and click on OK.  Just in case, you do not see the Import Data box, after step 12, press Alt+Tab.
15. You should now see your desired result in range E2:F66.

Furthermore, since you had converted the ranges in column B and column C to Tables individually, if you add additional rows of data to column B and/or column C, just refresh while you are clicked on any cell of the result range.  This will update the table in the result area.

Leave a Comment

Your email address will not be published.

CAPTCHA

*

  • Thanks, Ashish. You are great! What i was looking for are all possible combinations of 6 numbers if we select 2 numbers from Group A (1 to 8) and 4 numbers from Group B (9 to 16) for every possible combination instead of selecting 1 number from Group A and 1 number from Group B for every possible combination. examples: 1, 2, 9, 10, 11, 12; 1, 3, 9, 10, 11, 12; etc. Sorry for not having made myself well understood.

    Reply

    • Hi,

      If your question is related to this topic, please post your question there – please explain the question in detail. Also, for better understanding, upload your file to SkyDrive and share the link of the uploaded file here.

      Reply

      • Ashish,
        I have read and attempted the example to create list of combinations from a list multiple times and it’s not working. My example is this. I have a list of machines, 1 – 16 that I need to show all the possible combinations. Once I have the combination I need to multiply the equipment efficiency to show the probability. Do I need to do this in 2 steps?

        Thank you for your knowledge in this matter.
        Jason

        Reply

      • Hello! I have gone through your posts above to try and adopt it to what I am trying to do…no luck. I am trying to allocate costs based on financial dimensions which are assigned set percentages.

        Example:
        Dimension 1 include 1,2,3,4
        Dimension 2 includes 101,201,301,401,501,601
        Dimension 3 includes 10,20,30,40,50
        Dimension 4 includes 001,002,003,004,005,006,007,008,009,010

        So each “column” in your examples above have different number of options. Two things I want to be able to come up with:
        -all the possible combinations for a range like this (not an easy 6×6 grid).
        -then I also need the % calculation for allocating costs across these dimensions with each type of dimension having their own % breakdown.

        Dimension 1 could have % breakdown of:
        1 = 20%
        2 = 15%
        3 = 40%
        4 = 25%
        Dimension 2 could have % breakdown of:
        101 = 10%
        201 = 5%
        301 = 15%
        401 = 20%
        501 = 25%
        601 = 25%
        etc….

        But as my costs need to be allocated across ALL dimensions this gets exponentially large.

        So AMOUNT XXXX would need to broken down into Dimension 1 based on above percentage but then EACH of Dimension 1 would need to be broken down into Dimension 2 percentages and EACH of those would need to be broken down into Dimension 3 percentages….

        Any ideas on either of be queries above??

        Thanks

        Reply

    • Hi,

      You clarified your question at this link. This problem will have to be solved in two parts

      1. Creation of an intermediary table showing all possible combinations (even the invalid ones); and
      2. Creation of a Final Table from the one created in 1 above

      Steps for creating the Intermediary Table

      1. Create the data range as shown in D2:I10 of Data worksheet in this workbook
      2. Assign named ranges to individual columns of range D2:I10
      3. Follow the steps mentioned in 7 to 13 of the original post
      4. In the Import data box, select New Worksheet and cell A1. You will now see the table as shown in the Intermediary Table worksheet.

      Steps for creating the Final Table

      5. Copy the Table in A1:F40001 of this new worksheet and paste Special as Values in a new worksheet (in range A2:F40002 of Final Table worksheet)
      6. Mention Criteria as shown in I1:M2 of Final Table worksheet
      7. Click on cell O2 of Final Table worksheet and go to Data > Advanced > Copy to Another location
      8. In the List Range, select A2:F40002 of Final Table worksheet
      9. In the Criteria Range, select I1:M2 of Final Table worksheet
      10. In the Copy to box, select cell O2 of Final Table worksheet
      11. Click on OK

      Your desired result will appear in range O2:T1962 (1960 rows) of Final Table worksheet.

      Hope this helps.

      Reply

      • Apology for my late response. I was on leave. I have gone through the above procedure and get the desired result. Thanks a million! One more question: what does the formula of “=SUMPRODUCT(1/COUNTIF(A3:F3,A3:F3))=6” in cell I2 of final table worksheet mean? Thanks, Ashish.
        Tim

        Reply

  • Hello I have an assignment in statistics. I need an excel spreadsheet to list all possible combinations in groups of 6 using numbers 1-47. How can I set up excel to do this?

    Reply

  • Hi Ashish, Could help me create possible combinations for the below.
    8 columns each containing between 2-3 variables. no repetition, any order.

    a1 b1 c1 d1 e1 f1 g1 h1
    a2 b3 c2 d2 e2 f2 g2 h3
    a4 c4 d4 g6

    Reply

  • Sorry Ashish
    scrap the previous comment, it don’t think it made sense.

    Could you let me know how i can create as many 8 character combinations as possible based on the below 8 rows which contain 2-3 variables each. Letters can only be used once and ideally it would be ordered.
    For example first combo would be: a1, b1, c1, d1, e1, f1, g1, h1, second would be: a2, b1, c1, d1, e1, f1, g1, h1… etc

    a1 b1 c1 d1 e1 f1 g1 h1
    a2 b3 c2 d2 e2 f2 g2 h3
    a4———c4 d4————–g6——

    Reply

  • Hi ashish

    i have a query , the method told above is something i am not able to use on excel.

    I need to know the same
    the possible combinations of an input given.
    for example i have 2 number :1,2
    therefore the logic should show : 1,1;1,2;2,1;2,2
    is that possible on excel?
    i have 2008 version on mac

    Reply

  • code correction
    Excell programming is so criptic it easily confuses your logic:
    here is a simple basic code that solves the problem 2 numbers from a group of 8 (1,2,3,4,5,6,7,8, and 4 numbers from a group of 8( 9,10,11,12 13 14,15 16)
    for x1=1 to 7
    for x2= 2 to 8
    for x3= 9 to 13
    for x4=10 to 14
    for x5=11 to 15
    for x6 = 12 to 16
    print x1; x2;x3;x4;x5;x6
    next x6
    next x5
    next x4
    next x3
    next x2
    next x1

    Reply

  • You’re a gem and a genius!! Thank you sooooo much, not only did this work perfectly but your instructions were flawless. Thank you so much, again!

    Reply

  • Hello Ashish,

    I have arrived at your page looking for something else. Really Great work !

    Can you please assist me in finding the method for combination of a set of numbers whose sum is equal to a given value.

    My data consists 100-200 numbers some of them are negative and with decimals.

    I have tried using “Solver option” and popular “tushar-mehtas” code from
    google but it is consuming so much time and even sometimes not yeilding any result.

    Thanks in advance !

    Reply

    • Hi,

      Thank you for your kind words. Please go through my solution at this link (Scenario 2).

      If that solution does not help, then post your question in the Comments section of the link there. When posting your question, also share the link from where I can download your workbook. Please explain the question clearly enough.

      Reply

  • Hello Ashish,
    I have to create combinations on Excel and I was wondering if you could help me with that.
    I will give you an example of what I would need:
    I have $1050 to spend on lottery tickets, I have to pick combinations of six numbers from two separate pools of numbers- five different numbers from 1 to 75 and one numbers from 1 to 15. Therefore I need 70 different combinations of the first pool of numbers (1-75) for each number in the second pool of numbers (1-15). Can you help me create those combinations?

    I have tried this code =LARGE(ROW($1:$75)*NOT(COUNTIF($A$2:A2,ROW($1:$75))),RANDBETWEEN(1,(75+2-1)-ROW(A2)))
    but it gives me zeros and I can’t use those.

    Best Regards,

    Nando.

    Reply

    • Hi,

      I duly apologise for the delay in replying. I pasted your formula in cell A3 and copied down. I did not get any 0’s and that makes sense to me because the power limit number you have specified in RANDBETWEEN() is 1.

      Reply

  • I’ m trying to find a program that will give me a list of all combinations of a 6 number code . It needs to be simple . Prefer to just put the numbers in and get a list of all the combinations
    Thank you

    Reply

  • Dear Ashish,

    I have 12 numbers as follows: 0 0 0 0 0 0; 1 1 1 1 1 1. I want a combination of these numbers in all ways possible for example, 0 0 0 1 1 1 0 0 0 1 1 1. Is this possible for excel to do and how may I do it if possible? Thanks a million.

    Reply

    • Hi,

      I am not clear about your question. You have two columns with 0’s in one column and 1’s in the other. Should the result be a 2 column dataset with all possible combinations of 0’s and 1’s? If yes, then follow the steps mentioned in my Blog article.

      Reply

  • Thank you so much for this! It was extremely helpful as it saved hours and hours worth of work (not to mention frustration).

    Reply

  • Ashish – I have 10 columns of data with anywhere from 3 to 10 name in each column. Everytime I try to run the query, I get this error. “there isn’t enough memory to complete this action. try using less data or closing other applications. To increase memory availability, consider:
    -Using a 64 bit version of Excel
    -Adding memory to you device.”

    Help?

    Reply

  • I am trying to construct a data table loosely based on the concept of leaf area index. So the equation would be L=I-((d*A_1)+(e*A_2)) I want to find all combinations of the parenthetical expression, subtract those results from static I values, and sort the final number(L) into predetermined categories so I can easily reference what combinations result in a specific absorption level. However, I am not sure how exactly to get excel to generate all combinations with four variables. Should I generate independent tables of d*A_1 and e*A_2 and then use those tables to generate the value of the parenthetical expression?
    Thanks in advance.

    Reply

      • The first thing I want to do is find all possible results of the parenthetical expression. So, to simplify, if d=1;2;3 A_1=4;5;6 e=2;4;6 A_2=7;8;9 how do I get excel to output a table of all possible results of the expression (d*A_1)+(e*A_2)?

        Reply

          • I’m going to use the example numbers from above. I want to multiply all d values by all A_1 values. So 1*4; 1*5;1*6;2*4;2*5;2*6;3*4;3*5;3*6. Then multiply all e values by all A_2 values so, 2*7;2*8;2*9;4*7;4*8;4*9;6*7;6*8,6*9. And then add all d results to all e then if 1*4=4 and 2*7=14 then 4+14. But I have more values for each variable than i gave in the example and it would take forever to do them all by hand and the chance of error would be high.

          • Hi,

            My Blog post only help you with generating all possible values from multiple single column tables. It does not add any values. Please try to apply the technique outlined in the post to see if it helps.

  • Hi ashish
    Need one help
    Column 1 contains 8 different alphabets/products i.e a,b,c,d,e,f,g,h. I want the way i can get all possible combinations. There are 255 such combinations when we use the Combin function but i want to get the actual output. i.e. a, ab, abc etc

    Reply

    • Hi,

      Thank you for writing in. I am busy with other priorities and would not be able to devote time to solving this problem now. Please post your question in some other forum.

      Thank you and sorry about this.

      Reply

  • Hi Ashish,

    I am requesting a favor from you.

    I need find the combination of the set of data which has 5 columns.
    The expectation of out put is all the possible combination of the 5 columns.
    Can you please help on this.

    Reply

  • Hi Ashish,

    I need to make all combinations of 24 objects in groups of 4, but each object can be pair max. 1 time with another object. how do i do this?

    thanks in advance

    Reply

  • I have a team of 16 people and I want them to have a 2 minute conversation with each member of the team. Everyone will have 15 conversations. There are 8 pairs for each conversation. Can you show me how to generate that 8X15 array please?

    Reply

    • Hi,

      You’d like to create a Cartesian product (cross join). There should be n*(n-1)/r pairs i.e. 120 pairs. I have solved this using Power Query. You may download my solution from this link.

      Hope this helps.

      Reply

  • Hey Ashish

    I have a problem where I’m given the data set 1,2,4,7,9,10 and need to make all the possible number of combinations of 3 from the data set, without any repeats in each combination.

    such as (1,2,3) (2,1,3) (3,1,2) but there cannot be any repeats from the data set such as (3,3,2) or (3,3,3).

    Is there a way to do this in excel without the use of VBAs?

    Thanks!

    Reply

    • Hi,

      Please follow the Microsoft Query steps outlined in the Blog. Once you get the data in Excel, you may write a formula in a spare column to check the count of unique numbers in every row. If that count is 10, then you have all unique numbers in that row. The formula to count unique numbers in B2:K2 is

      =SUMPRODUCT(1/COUNTIF(B2:K2,B2:K2))

      You may copy this formula down and filter the entire dataset with a criteria on this column of 10.

      Hope this helps.

      Reply

      • Hey Ashish,

        When I try using the advanced filter with the criteria, I get columns that are duplicated in the manner of (1,1,1) (2,2,2) (3,3,3). How can I fix this?
        My criteria is set as =SUMPRODUCT(1/COUNTIF(A2:B2:C2,A2:B2:C2))=3

        Reply

  • Four years later and still handy! Thanks so much, I needed to create all the possible row combos of several columns and this was perfect!

    Reply

  • Hi Ashish,

    This worked great for most of my project! However, I ran into an issue of not having enough rows in excel to create all possible combinations of a string. Do you have a workaround for running out of excel rows? I need to generate about 1,800,000 different numbers.

    Reply

    • Hi,

      No, I do not. You cannot increase the number of rows. The BI tools of Excel can accommodate more than 10,00,000 rows but you will not be able to see that output on any sheet because of the row limit.

      Reply

      • Is there a way to have the final set of data populate in a text document or other type of document rather than excel? Thanks!

        Reply

  • Hi Ashish.
    Just a big thank you for your article on,” Create all possible combinations from
    different ranges without using VBA” works perfect for me just some small modification for my needs,just acknowledgement for your good work,thanks again.
    steve

    Reply

  • Hello,
    I want to have a table combinations that displays from at least 4 different groups with each group having a number range from 1 to 4
    Combinations 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
    Group 1 1 1 1 1 2 1 2 2 2 1 2 2 2 1 1 1 3
    Group 2 1 1 1 2 1 1 1 2 1 2 1 2 2 1 1 3 1
    Group 3 1 1 2 1 1 2 1 1 2 2 2 1 2 1 3 1 1
    Group 1 1 2 1 1 1 2 2 1 1 2 2 2 2 3 1 1 1 etc

    I’m not to sure is it a easy task to do.
    Thank you. Joseph

    Reply

  • Hello. Do you know why I would generate the following error when I follow the steps in your blog post?

    “Syntax Error in From Clause”

    Reply

  • Hi,
    i am trying to get different combination of numbers from “n” numbers. below is the one example what i am trying with n=4. Is it possible to get all the possible combinations by using the method shown above. please let help me out in doing that.

    by using numbers (1 2 2 2) we can write (4p1)= 4 combinations (1222,2122,2212,2221)
    similarly for numbers (1 2 3 4) we can write (4p4)= 24 combinations (1234, 2134, 1314, 2341 …etc)

    Reply

  • i have tried the method it is not giving me exactly what i wanted. problem i am having here is , in this case we have only one data set. using the numbers in one data set we need write the all possible combinations.

    Thanks
    Vinod

    Reply

  • Hi Ashish,
    I tried by creating 4 Copies and it has given me 256 combinations instead of 24 combinations. I came to know this is because of repetition of numbers. But I need combination of numbers with out repetition. to remove repetitions I followed steps posted in blog and I am stuck at this formula “=SUMPRODUCT(1/COUNTIF(A3:F3,A3:F3))=6”. it looks like this logic will not work in my case.
    I am using your excel posted in blog “possible_combination_of_numbers.xlsx”
    please let me know how this can be fixed.

    Thanks
    Vinod

    Reply

  • I tried to generate possible combinations of different values in 5 different columns using your suggested method. I could do with four columns only when I try to do with 5 columns I am get an error “Microsoft Query has stopped working” and the SQL automatically get closed. Is there any limitation on selecting the number of columns and/or values in them?

    Reply

    • Hi,

      No, there is no such limitation. Please retry. You should also try to use Power Query to solve this problem. Download my workbook from the reply comment on September 27, 2017.

      Reply

  • Hello Ashish,
    If you have a moment could you please take a look at my problem?
    I have about 4000 articles with between 2 to 5 authors on each. I want to list all the possible pairings of author collaborations on each article. For example on an article with 3 authors a1, a2, a3 then the possible pairings are a1,a2; a1,a3; a2,a3; whereas a paper with 4 authors will have 6 pairings b1,b2; b1,b3; b1,b4; b2,b3; b2,b4; b3,b4; Is there a way to automatically list all of these possible pairings for the 4000 records?
    Lila

    Reply

      • Hi Ashish
        Dataset:
        ArticleRefNumber Author 1 Author 2 Author 3 Author 4 Author 5
        2345 SDF EDR DCF RTY DCG
        3456 EDR DFT
        6789 SDF MJK CFG

        Expected Result:
        2345 SDF EDR
        SDF DCF
        SDF RTY
        SDF DCG
        EDR RTY
        EDR DCF
        EDR DCG
        DCF RTY
        DCF DCG
        RTY DCG
        3456 EDR DFT
        6789 SDF MJK
        SDF CFG
        MJK CFG
        Or I can email this demo worksheet if easier?
        Many thanks for your time, Lila

        Reply

  • hi,
    i have a problem on my hands. I have 9 different numbers. 3, 4, 5, 9, 11, 20, 47, 81, 83. what I am trying to accomplish on Excel is to generate every possible three digit combination using these set numbers. Never using the same number twice. How would you suggest I do this?

    Reply

  • Hi Ashish,
    This is really something good.

    I want something similar if you can help.

    I’m trying to generate all possible load combinations from the set of given load set.

    Let’s say i have following.

    LC1: 1 0 0 0
    LC2: 0 1 0 0
    LC3: 0 0 1 0
    LC4: 0 0 0 1

    Then I need all possible combinations as below:
    LC1+LC2 : 1 1 0 0
    LC1+ LC3: 1 0 1 0
    LC1+ LC4: 1 0 0 1
    LC1+ LC2+ LC3: 1 1 1 0 and so on…..

    Can you please assist?

    Reply

  • I would like 7 different numbers and would like to changed the desired combination from the 7 numbers , example 2 combination or , 1 combination or 3 combination or what the desired combination of numbers from the 7 different numbers selected in the cells without any duplication . Can you assist

    Reply

  • I am trying to setup a playing list for tennis.
    We are 10 people and every week we play 2 matches of doubles; so 8 people play and 2 people are free.
    We want to randomly create teams and play with and against each other in an evenly number of occasions.
    How to create this overview?

    I hope it is clear.
    Thanks for your support!

    Reply

  • hi,

    Thanks for the solution to create the maximum combinations for the given lists. Can we set some preferences like must include, not required combinations in the final answer ?

    Reply

  • Below is the sample data.

    Type –> Primary, Secondary,Null
    Coverage –> Plan A, Plan B,Plan C
    Duration –> 1 Year, 2 Years, > 2 years

    Not required Combinations –>
    1.Type Null
    2. Primary Plan B

    I need to daily test multiple combinations. My intention is to eliminate the least preferred items and test an optimal set within my time frame.

    Appreciate your support.

    Reply

  • i’M SORRY

    Hello, good day, my question is the following if you could help me, the fact is that in a pool there are 28 teams and therefore are 14 games for a day, and it is obvious that there is a possibility that the teams in the 14 games one lose, the other win, and the other draw. Now; How many combinations are there in total, without repeating the results?
    I would like you to answer me by email carlos_2000guapo@hotmail.com
    greetings and thanks

    Reply

  • I am trying to create a list of combinations of 5 numbers in a set using numbers 1-69. my total is 11,238,513 combinations but its not giving me a list of the different combination. How do i go about getting that in my excel?

    Reply

    • Hi,

      So are you saying that if we were to create 5 figure numbers from numbers ranging from 1 to 69, then there will be 11,238,513 combinations? You want to create this 11,238,513 combinations in a single column in your Excel file?

      Reply

        • Hi,

          What do you mean by “how do I do that in a single cell on excel?” If at all, we can get that result, it will be in a column. Since MS Excel has only 1 million rows, the only way to get all rows (11.23 million) would be to transfer to the result to the PowerPivot.

          Reply

          • Hi,

            Sorry for the delayed reply. I do not think I can solve this. A five digit number can get formed from multiple combinations. It can be formed by combining a 2 digit number, another 2 digit number and a 1 digit number. It can also get formed by combining 5 single digit numbers. Sorry, but I will not be able to help here.

  • Hi Ashish, Your blog is amazingly useful. I tried and got combinations of 3 and 5 alphabets from a range of A-N,however I am unable to remove duplicate combinations wherever any alphabet is repeated for e.g. bbc, cbc etc. Seems I am not using the below formula correctly. Can you help. i referred to your revert on March 30, 2017 at 8:50 am.
    =SUMPRODUCT(1/COUNTIF(B2:K2,B2:K2))

    Reply

    • Hi,

      Thank you for your kind words. I am not clear with your question. The SUMPRODUCT() formula will count the unique instances. So do you want to count and get a single cell answer or do you want to remove duplicates and get all other combinations. Share a dataset and show the expected result.

      Reply

  • Hi Ashish, Thanks for your revert. I wanted all possible combinations of 3 and 5 from a dataset of letters a till n. For combinations of 3,i created 3 columns (data set 1 with letters A-L, data set 2 with letters B-M and data set 3 with letters C-N. Then I applied your steps to get all combinations of 3 and 5 which I got. I then concatenated the cells and saw the combinations as abc, bbc, cbc, dbc etc. In these combinations, i only want combinations where a letter is not repeated for e.g i dont want cbc, bbc. I only want combinations such as abc, dbc where no letter is getting repeated. Same i also want for combination of 5.

    Reply

      • Thank you so much Ashish. Request you to also tell me how to run this power query so that i can also generate further combinations of 4,5, 6 etc.

        Reply

        • Hi,
          In that workbook, go to Data > Queries and Connections. In the right hand side pane, right click on the first query and go to Edit. In the Applied Steps section, click/double click through each step to understand the process.

          Reply

  • Hi Ashish, I can’t find queries and connections in data tab, neither in browser while opening your excel or after downloading it. I can see 3 options only i.e. refresh selected connections, refresh all connections and calculate workbook Post download I can only find connections but not queries. I am using office 2007. Pls help.

    Reply

  • Hi Ashish,

    I need to create all possible combinations of 15 tables with data (all containing numbers 1-15). I am trying to replicate your suggested solution for listing all combinations, and it works well for two tables, but then with more than 3 tables my excel blocked. Is there any efficient way on how i could do this?

    thank a lot for your help.

    Reply

  • Hi Ashish thanks a million for the answer. One more think please. Is it possible to generate only unique combinations, lets say general 1,2,3 and not the 3,2,1 etc. since i need only unique combinations and this would significantly reduce the number of data (which might be a solution doable from excel query)?

    Reply

  • Hello Ashish
    I am trying to create 7 number combinations of number 1-50 with 50 filter pairs.
    Would be able to help.

    Thanks

    Reply

  • Hello i have a similar problem of trying to list all combinations of twelve numbers from 48 numbers .. How can i create that list

    Reply

  • Hi Ashish,

    I would like to get assistance in getting all possible combinations which has 6 numbers wherein it has 3 odd numbers and 3 even numbers as the result.

    Data Set1: 1, 11, 16, 18, 22, 44
    Data Set2: 33, 42, 15, 36, 9, 7

    Thank you very much for the assistance.

    Reply