Possible to generate combinations of specific numbers?

**excelamer** · 12-30-2019, 12:27 AM

Is it possible to generate combinations from a specific set of variable numbers, rather than randomly from a range?
My question isn't about Lotto, but to use a simple analogy, there are Lotto number generators that calculate all possible 6-ball combinations from a set of say, 49 numbers.
But what I want to do is generate all possible 6-number combinations from a specific set of 10 numbers, such as 2,4,6,8,10,12,14,16,18,20.
Or, at another time, I might want to generate all 5-number combinations from a set of 15 numbers, such as 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15.
Could this be done in Excel?

**Glenn Kennedy** · 12-30-2019, 03:58 AM

Yes, but you'll need VBA. Here's a sheet (source unknown) that does that. Enable macros on opening. Asssuming that it's combinations (not permutations) that you're interested in, select C in A1... and take it from there.

**hrlngrv** · 12-30-2019, 04:42 AM

Excel really isn't the ideal tool to generate combinations. It can be done, but other tools, e.g., a few stats packages, would be better.

Here's a link to a spreadsheet approach implemented in Google Sheets. The formulas should work in Excel too.

**excelamer** · 12-30-2019, 06:13 PM

Glenn Kennedy and hrlngrv: Thank you, gentlemen, much appreciated

I may have to come back with more questions, but I'll see how far I can get solo.
I hope the New Year is kind to you both!

**excelamer** · 12-31-2019, 06:22 PM

Glen Kennedy, hi again. That spreadsheet worked well. Only thing is, I need the results to be in separate columns, rather than a list of numbers separated by commas. And the sheet is locked, I guess it needs a password.
Any ideas?

hrlngrv, hi also. That sheet is also locked, and the output is restricted to 462 results, instead of the possible 5005 that 15 numbers will return. Plus, there's no ability to change the input number selection.
There's an inbuilt way of contacting the author, which I've done. I guess they'll want paying.

**hrlngrv** · 12-31-2019, 06:53 PM

Originally Posted by excelamer

...
hrlngrv, hi also. That sheet is also locked, and the output is restricted to 462 results, instead of the possible 5005 that 15 numbers will return. Plus, there's no ability to change the input number selection.
There's an inbuilt way of contacting the author, which I've done. I guess they'll want paying.

The Google Sheets example is mine, and it's free to use. If you have a Google account, you should be able to make an editable copy to use in Google Sheets. Otherwise, you could download it, and the download process converts it to .XLSX.

If you had an editable copy, you could add more numbers in Sheet1!1:1 in the cells immediately to the right of the numbers already there.

I only meant this as an abbreviated example. Note that 20 choose 6 (i.e, the number of combinations of 6 numbers from a population of 20) is 38,760, which I mention because the number of combinations increases rapidly as the population size increases. You definitely don't want to use any on-line spreadsheet for that level of calculations.

Anyway, the idea is generating the indices for given combinations on a separate worksheet, then using those indices to generate the combinations. If you download the Google Sheets workbooks as an Excel workbook, you should fill 'combination indices'!A1000:F1000 down many thousands more rows to handle populations larger than 10. Then you'd need to fill Sheet1!A1004:F1004 down as well for additional combinations for larger populations. Formulas will return #N/A when all combinations have been exhausted.

To repeat from my previous reply: Excel can do this, but it can't do it well. There are better tools for this sort of thing.

**jeffreybrown** · 12-31-2019, 07:18 PM

Hi excelamer,

Here's another idea for your consideration.

**excelamer** · 12-31-2019, 08:26 PM

hrlngrv: Hi and thank you very much

I downloaded the file and, as you say, it becomes editable in its Excel form.
AFAIK, 15 source numbers should yield 5005 combos, but I'll be applying other formulas to weed out the bulk of those, so the final result should be more compact. Which is why I wanted to use Excel.
Much appreciated!

Jeff: Thanks for the link, there's much interesting stuff there.

LATER: I've been experimenting with the workbook and I seem to be doing something (or several things!) wrong.
I entered 15 random numbers in the 'population' line, which caused the changes shown on the pic.
But I'm not sure how to get the results to fill more than 1,000 rows.