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