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.

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.

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.

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

Hi,

At what stage are you getting stuck in implementing my solution? Please be specific. Upload your workbook to OneDrive and share the link of the workbook with me.

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 6x6 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

Hi,

Sorry for the delay in replying. I've been able to solve the first part by using the exact technique described in my Blog article. As a result of using Microsoft Query, I got 1200 rows of data. Where are you getting stuck at this stage?

Hey Ashish,

if x+y+z=12 then there ll be 220 combinations but how can I do this on excel?

Hi,

How will there be 220 combinations?

Thanks, Ashish. I have posted my question again as a response in Microsoft Community.

regards

Tim

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 Table1. 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 Table5. 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.

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

Hi,

You are welcome. Glad to help. That formula counts the unique entries in A3:F3.

Hope this clarifies.

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?

Hi,

You need to share more details. Please take an example.

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

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------

Hi,

I cannot visualize your dataset and expected solution. Please share exact dataset and expected result.

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

Hi,

It is working absolutely fine. I just tried it. I am not sure whether Excel on the Mac has Microsoft Query or not. At what step are you facing a problem?

why would you want to print 10,737,573 combinations, that's a lot of paper

[…] Dunno if this works but it looks intriguing. Create all possible combinations from different ranges without using VBA […]

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

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!

Hi,

Thank you very much for your kind comments.

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 !

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.

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.

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.

THANK YOU!!

You are welcome.

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

Hi,

For any six numbers that you choose, there will be 46,656 combinations i.e. 6^6. Is that what you want?

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.

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.

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

You are welcome.

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?

Hi,

That does not sound like a lot of data warranting 64 bit Excel. Ensure all applications are closed except MS Excel and then retry.

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.

Hi,

I do not understand your Excel requirement.

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)?

I still do not understand. Please explain in layman terms. What data do you have and how is it arranged. Also, show your expected result.

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

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.

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.

Hi,

What happens when you try out the method listed in my Blog? If you do not get the expected result, then please let me know where my method falls short of your expectations?

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

Hi,

I do not understand your question.

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?

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.

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!

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.

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

The formula should be

=SUMPRODUCT(1/COUNTIF(A2:C2,A2:C2))=3

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!

You are welcome. Thank you for your kind words.

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.

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.

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!

Hi,

I do not think that is possible. The result of Power Query or Power Pivot can be displayed only in an Excel worksheet.

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

You are most welcome. Thank you for the feedback.

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

Hi,

I do not understand your question. Please explain again.

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"

Hi,

Paste your SQL query here.

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)

Hi,

Please try the method suggested in my Blog. Post back with the exact issue you are facing, if at all my method does not solve your problem.

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

Hi,

Since you want to create all possible combinations of 4 digit numbers, create 4 copies of that single column and then try my method.

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

Hi,

I have solved the problem using Power Query. You may refer to my solution here.

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?

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.

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

Hi,

Can you show a sample dataset and your expected result on that sample dataset.

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

Hi,

Sorry for the delay in replying. You may refer to my solution in this workbook.

Hope this helps.

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?

Hi,

Do you mean that you would not like to see 344, 559, 595 etc.?

This is exactly what I was looking for and your post helped me. Thank you !

You are welcome.

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?

Hi,

Thank you. Try the steps outlined in the Blog article.

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

Hi,

This will require VBA coding. I do not provide solutions which involve VBA coding.

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!

Hi,

This would require usage of VBA. I do not provide VBA support.

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 ?

You are welcome. May be possible with the Query Editor. Share your data and the expected result.

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.

Hi,

I have solved this problem using Power Query a.k.a. Data > Get & Transform in Excel 2016. You may download my workbook from here.

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

Hi,

Share some data and show the expected result.

The data is 28 teams, 14 matches per day, and there are 3 possibilities that the LOCAL, EMPATHEN, OR WIN THE VISITOR wins.

and my question is, how many combinations are there of the 14 games where I only have to put 1 option, without it being repeated,

regards

Hi,

This may require VBA coding. I do not provide VBA support. Sorry I cannot help.

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?

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?

Yes and Yes

how do I do that in a single cell on excel?

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.

ok so how do i do that?

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

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.

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.

Hi,

I have solved the problem of generating combinations of 3 using Power Query. You may download my file from hereherehere.

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.

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.

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.

Hi,

Your version of MS Excel does not support Power Query i.e. Data > Get & Transform in Excel 2016 and higher versions.

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.

Hi,

This will be a memory intensive calculation. Not sure whether I can help here.

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)?

You are most welcome. I do not think I can solve that. Sorry.