vba to generate all combinations, no repeat, from a range?

[excelNewbie22]

hi,
i googled quite a lot, found a lot but not what i'm looking for,
i need a simple macro to generate an x numbers out of a range,
so i can edit it each time per my need,
with no repeation

for example
range will be a2:h2 (or more or less)
numbers of combinations 5 (or more or less)
if possible with hyphens seperate between i.e 1-2-3-4-5
all results in one column

can you please help?

New Microsoft Excel Worksheet.xlsx
	A	B	C	D	E	F	G	H	I	J
1									of 6	of 5
2	1	2	3	4	5	6	7	8	1-2-3-4-5-6	1-2-3-4-5
3									1-2-3-4-5-7	1-2-3-4-6
4									1-2-3-4-5-8	1-2-3-4-7
5									1-2-3-4-6-7	1-2-3-4-8
6									1-2-3-4-6-8	1-2-3-5-6
7									1-2-3-4-7-8	1-2-3-5-7
8									1-2-3-5-6-7	1-2-3-5-8
9									1-2-3-5-6-8	1-2-3-6-7
10									1-2-3-5-7-8	1-2-3-6-8
11									1-2-3-6-7-8	1-2-3-7-8
12									1-2-4-5-6-7	1-2-4-5-6
13									1-2-4-5-6-8	1-2-4-5-7
14									1-2-4-5-7-8	1-2-4-5-8
15									1-2-4-6-7-8	1-2-4-6-7
16									1-2-5-6-7-8	1-2-4-6-8
17									1-3-4-5-6-7	1-2-4-7-8
18									1-3-4-5-6-8	1-2-5-6-7
19									1-3-4-5-7-8	1-2-5-6-8
20									1-3-4-6-7-8	1-2-5-7-8
21									1-3-5-6-7-8	1-2-6-7-8
22									1-4-5-6-7-8	1-3-4-5-6
23									2-3-4-5-6-7	1-3-4-5-7
24									2-3-4-5-6-8	1-3-4-5-8
25									2-3-4-5-7-8	1-3-4-6-7
26									2-3-4-6-7-8	1-3-4-6-8
27									2-3-5-6-7-8	1-3-4-7-8
28									2-4-5-6-7-8	1-3-5-6-7
29									3-4-5-6-7-8	1-3-5-6-8
30										1-3-5-7-8
31										1-3-6-7-8
32										1-4-5-6-7
33										1-4-5-6-8
34										1-4-5-7-8
35										1-4-6-7-8
36										1-5-6-7-8
37										2-3-4-5-6
38										2-3-4-5-7
39										2-3-4-5-8
40										2-3-4-6-7
41										2-3-4-6-8
42										2-3-4-7-8
43										2-3-5-6-7
44										2-3-5-6-8
45										2-3-5-7-8
46										2-3-6-7-8
47										2-4-5-6-7
48										2-4-5-6-8
49										2-4-5-7-8
50										2-4-6-7-8
51										2-5-6-7-8
52										3-4-5-6-7
53										3-4-5-6-8
54										3-4-5-7-8
55										3-4-6-7-8
56										3-5-6-7-8
57										4-5-6-7-8
test

 

[excelNewbie22]

any one?

also, forget to ask

all results in one column

of my choosing like in col k2

 

[excelNewbie22]

please?

 

[johnnyL]

I think you might get more responses if you were more precise with what you are asking.

Your use of 'more or less' probably should state the min & max values you anticipate.

Are the numbers that you use always going to start with '1'?
Are the numbers always going to be incremented by 1?
What are the minimum/maximum amount of numbers you anticipate? ex: minimum of 6 total numbers / maximum of 8 total numbers
Of these minimum/maximum amount of numbers to use, what is the minimum/maximum amount of 'draws' you anticipate? ex. 5 at a time?, 6 at a time?, etc.

 

[excelNewbie22]

hi johnny, thanks again for trying to help,
answers:

Are the numbers that you use always going to start with '1'?
Are the numbers always going to be incremented by 1?

no and no.
i want the macro to read the numbers (and the amount) from a range,
for example - a2:i2 which will include a2=2 b2=6 c2=11 d2=22 e2=23 f2=25 g2=29 h2=30 i2=36

What are the minimum/maximum amount of numbers you anticipate? ex: minimum of 6 total numbers / maximum of 8 total numbers

the amount: 9 to 35 - which will be determine by the range

Of these minimum/maximum amount of numbers to use, what is the minimum/maximum amount of 'draws' you anticipate? ex. 5 at a time?, 6 at a time?, etc.

draws of minimum of 3, maximum of 6

basically the range will set two things:
a- the amount of balls to pick from (example: a2:i2 = 9)
b- the numbers which will assemble all possible combinations (example: 2-6-11-22-23-25-29-30-36)

 

[johnnyL]

Some things to consider:

1) Instead of putting your pool of numbers across a row (ie. A2:H2) Why not consider putting them down a column (ie. A2:A9)? That seems like it would keep the sheet much neater. For example if you have 20 numbers in your pool of numbers ... you would have to scroll across the sheet to get to the column that actually displayed the results. On the other hand, if you put the pool of numbers to use down a column, you could start displaying the results in the next column over if desired.

2) You mentioned you want results to be displayed in one column. You also said:

the amount: 9 to 35 - which will be determine by the range

draws of minimum of 3, maximum of 6

The maximum display for one column would be 1048576 rows, if I recall correctly.

If you want a pool size of 35, that would limit the 'draws' to a maximum of 5
If you want the amount of draws to be the maximum of 6, that would limit the pool size to a maximum of 32.


Just some things to consider for your goal of what you are wanting to achieve.

Code could be added to 'check' if the amount of combinations would fit into one column & notify user if too many combinations would be generated to fit into one column.

 

[excelNewbie22]

1) Instead of putting your pool of numbers across a row (ie. A2:H2) Why not consider putting them down a column (ie. A2:A9)? That seems like it would keep the sheet much neater. For example if you have 20 numbers in your pool of numbers ... you would have to scroll across the sheet to get to the column that actually displayed the results. On the other hand, if you put the pool of numbers to use down a column, you could start displaying the results in the next column over if desired.

you are right, but the file i'm already using is built that way, so for now i'll keep it like that

i didn't take into account the limit of one column, so 32 will be enough

thank you

 

[johnnyL]

you are right, but the file i'm already using is built that way, so for now i'll keep it like that

What does that mean? You already have a file that works for this thread?

 

[johnnyL]

No response from OP, so here goes:

CombinationsOnly.xlsm
	A	B	C	D	E	F	G	H	I	J
1	1	2	3	4	5	6	7	8
2	Numbers to Draw	6
3
Sheet1

Code to use:

Option Explicit
'
' Inspired by some previous code from pgc01 https://www.mrexcel.com/board/threads/combination-help.277924/post-1379220
'
Sub AllCombinations()
'
    Dim ArrayRow                    As Long
    Dim DrawAmount                  As Long, TotalCombinations      As Long
    Dim NumbersPoolLastColumnNumber As Long
    Dim NumbersPoolRow              As Long
    Dim ResultSkipColumnAmount      As Long
    Dim NumbersPoolRange            As Range
    Dim NumbersPoolLastColumnLetter As String
    Dim NumbersDrawnArray           As Variant, NumbersPoolArray    As Variant, OutputArray     As Variant
'
    NumbersPoolRow = 1                                                                                              ' <--- set this to the row that contains the NumbersPool to use
    ResultSkipColumnAmount = 2                                                                                      ' <--- set this to the amount of columns to skip before displaying results
    DrawAmount = Range("B2").Value                                                                                  ' <--- set the amount of #s to pick in this cell
'
    NumbersPoolLastColumnNumber = Cells(NumbersPoolRow, Columns.Count).End(xlToLeft).Column                         ' Get last column number of PoolNumbers to use
    NumbersPoolLastColumnLetter = Split(Cells(NumbersPoolLastColumnNumber).Address, "$")(1)                         ' Get last column letter of PoolNumbers to use
'
    Set NumbersPoolRange = Range("A" & NumbersPoolRow & ":" & NumbersPoolLastColumnLetter & NumbersPoolRow)         ' Set the range of pool #s to use
'
    NumbersPoolArray = NumbersPoolRange                                                                             ' Load pool of numbers to use into 2D 1 based NumbersPoolArray
'
    TotalCombinations = Application.WorksheetFunction.Combin(NumbersPoolRange.Count, DrawAmount)                    ' Get total # of combinations that will be produced
    Range("A3").Value = "Total # of combinations"                                                                   ' Display header of 'Total # of combinations'
    Range("B3").Value = TotalCombinations                                                                           ' Display # of combinations that will be generated
'
    ReDim NumbersDrawnArray(1 To DrawAmount)                                                                        ' Establish size of 1D one based NumbersDrawnArray
    ReDim OutputArray(1 To TotalCombinations, 1 To DrawAmount)                                                      ' Establish size of 2D one based OutputArray
'
    Call GetAllCombinations(NumbersPoolArray, DrawAmount, NumbersDrawnArray, ArrayRow, OutputArray, 1, 1)           ' Calculate the Combinations
'
    Cells(NumbersPoolRow, NumbersPoolLastColumnNumber + _
            ResultSkipColumnAmount).Resize(TotalCombinations, 1).Value = OutputArray                                ' Display results to sheet
'
    ActiveSheet.UsedRange.EntireColumn.AutoFit                                                                      ' Autofit all column widths on the sheet
End Sub


Sub GetAllCombinations(ByVal NumbersPoolArray As Variant, ByVal DrawAmount As Long, ByVal NumbersDrawnArray As Variant, _
        ByRef ArrayRow As Long, ByRef OutputArray As Variant, ByVal NumbersPoolColumn As Long, ByVal DrawNumber As Long)
'
    Dim ArrayColumn         As Long
'
    For NumbersPoolColumn = NumbersPoolColumn To UBound(NumbersPoolArray, 2)                                        ' Loop through the NumbersPoolArray columns
        NumbersDrawnArray(DrawNumber) = NumbersPoolArray(1, NumbersPoolColumn)                                      '   Save NumbersPoolArray(1, NumbersPoolColumn) value to NumbersDrawnArray(DrawNumber)
'
        If DrawNumber = DrawAmount Then                                                                             '   If last DrawNumber of DrawAmount has been reached then ...
            ArrayRow = ArrayRow + 1                                                                                 '       Increment ArrayRow
'
            For ArrayColumn = 1 To DrawAmount                                                                       '       Loop through NumbersDrawnArray columns
                OutputArray(ArrayRow, 1) = OutputArray(ArrayRow, 1) & NumbersDrawnArray(ArrayColumn) & "-"          '           Save NumbersDrawnArray column value & delimiter to OutputArray
            Next                                                                                                    '       Loop back
'
            OutputArray(ArrayRow, 1) = Left$(OutputArray(ArrayRow, 1), Len(OutputArray(ArrayRow, 1)) - 1)           '       Remove the last delimiter '-' from the OutputArray string
        Else                                                                                                        '   Else ...
            Call GetAllCombinations(NumbersPoolArray, DrawAmount, NumbersDrawnArray, ArrayRow, _
                    OutputArray, NumbersPoolColumn + 1, DrawNumber + 1)                                             '       Recursive loop to get next value of NumbersDrawnArray
        End If
    Next                                                                                                            ' Loop back
End Sub

End Result =:

CombinationsOnly.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	1	2	3	4	5	6	7	8		1-2-3-4-5-6
2	Numbers to Draw	6								1-2-3-4-5-7
3	Total # of combinations	28								1-2-3-4-5-8
4										1-2-3-4-6-7
5										1-2-3-4-6-8
6										1-2-3-4-7-8
7										1-2-3-5-6-7
8										1-2-3-5-6-8
9										1-2-3-5-7-8
10										1-2-3-6-7-8
11										1-2-4-5-6-7
12										1-2-4-5-6-8
13										1-2-4-5-7-8
14										1-2-4-6-7-8
15										1-2-5-6-7-8
16										1-3-4-5-6-7
17										1-3-4-5-6-8
18										1-3-4-5-7-8
19										1-3-4-6-7-8
20										1-3-5-6-7-8
21										1-4-5-6-7-8
22										2-3-4-5-6-7
23										2-3-4-5-6-8
24										2-3-4-5-7-8
25										2-3-4-6-7-8
26										2-3-5-6-7-8
27										2-4-5-6-7-8
28										3-4-5-6-7-8
29
Sheet1

 

[johnnyL]

That code should spit out the results in a maximum of 10 seconds for up to 1,000,000 combinations.