Help with combination – a 2-part problem

[kenny9002]

PART 1:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>

Please I need help with a 2-part issue relating to ‘Combinations’. Firstly, I have 30 different numbers, and wish to generate all possible 6/30 combinations (without repetition). I believe that I would require a good VB macro to achieve this, and should be very grateful if expert forum members would, please, help me with a VB macro. Also when each 6-number group is generated, I would like to have the result in six columns – i.e. each of the six numbers will occupy a cell per column, rather than have the numbers in one cell separated by comas. I use Excel 2003, and I am using this for a lottery analysis.
<o></o>
<o></o>
If this had come up in an earlier thread, could I please be directed to the appropriate link. <o></o>
<o></o><o></o>


PART 2:<o></o>
<o></o>

After generating all the possible 6/30 combinations, I wish to match each group of six numbers with existing results that have been published. In other words, I have, on a separate sheet (e.g. SHEET 1), a copy of all the six number results already drawn. I wish to be able to MATCH each row (six numbers) of the published results with the rows of the generated 6-number combinations. The aim here is to identify my six-number generated combinations that have already been drawn. And when a Match is TRUE, then the relevant generated six-number combination (or the relevant rows) would have to be highlighted with any colour – so I can easily identify all such matched combinations in my generated list of combinations.<o></o>
<o></o>
Please I need help with these two issues. I suppose I might require two different VB macros to achieve these – I don’t know, but I need your kind help. I have thought through, and have in my head what I wish to achieve – as explained above. I believe that I can achieve this using Excel, but I am not yet very good with Excel. I have no doubt that that some members on this forum have the expertise on these sort of issues, and I’m therefore asking for their kind help.<o></o>

Thanks everyone for your anticipated help.

Kenny <o></o>
<o></o>

 

[baitmaster]

well my stats is a bit rusty, but I'm not sure that you will be able to fit this many combinations onto a spreadsheet, even in XL07 or later with a million rows...

And any calculations you want done on such a large set of results is going to cause you definite time issues...

That said, I think you probably want the following set of loops, as basis for your code:

Option Explicit
Sub writeNums()
Dim i As Integer, j As Integer, k As Integer, l As Integer, m As Integer, n As Integer, counter As Long
For i = 1 To 25
    For j = i + 1 To 26
        For k = j + 1 To 27
            For l = k + 1 To 28
                For m = l + 1 To 29
                    For n = m + 1 To 30
                        counter = counter + 1
                        Cells(counter, 1) = i
                        Cells(counter, 2) = j
                        Cells(counter, 3) = k
                        Cells(counter, 4) = l
                        Cells(counter, 5) = m
                        Cells(counter, 6) = n
                    Next n
                Next m
            Next l
        Next k
    Next j
Next i
End Sub

I won't look at part 2 yet, because I don't think you'll get past the space issues on part 1. Let me know if you get that far...

 

[baitmaster]

OK, I stand corrected. I ran the code and generated 593,775 lines of data, and it looks correct. I guess I should think about what to do next...

 

[baitmaster]

You will definitely need XL07 or later to do this, although some changes could make it work in XL03, but quite clumsy as will need multiple data sets (9 at least). Either way, its not particularly user friendly as there are so many sets of results

Once I created my data set of 593,775 results, I inserted a header row, applied Autofilter on that header row, and created a range of 6 cells into which I place my results (in ascending order, or it won't work). I named that range "results"

The following code will highlight the relevant set of results in yellow:

Sub identifyResults()
Dim AFrange As Range: Set AFrange = ActiveSheet.Range("$A$1:$F$593776")
With AFrange
    .AutoFilter Field:=1, Criteria1:=Range("results").Cells(1, 1)
    .AutoFilter Field:=2, Criteria1:=Range("results").Cells(1, 2)
    .AutoFilter Field:=3, Criteria1:=Range("results").Cells(1, 3)
    .AutoFilter Field:=4, Criteria1:=Range("results").Cells(1, 4)
    .AutoFilter Field:=5, Criteria1:=Range("results").Cells(1, 5)
    .AutoFilter Field:=6, Criteria1:=Range("results").Cells(1, 6)
End With
AFrange.SpecialCells(xlCellTypeVisible).Interior.Color = 65535
ActiveSheet.ShowAllData
End Sub

Whatever you are trying to do, I would suggest you try to handle this using mathematical theory only, rather than on a spreadsheet - especially if you only have XL03 available to you

 

[kenny9002]

OK, I stand corrected. I ran the code and generated 593,775 lines of data, and it looks correct. I guess I should think about what to do next...

Hi,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Thanks for your kind response. Please help me to understand this: after posting my request, I continued searching through to see if I could find a solution to the problem. I had come across a Post – see the link below:
<o> </o>
http://www.mrexcel.com/forum/showthread.php?t=459437&highlight=combination+permutation
<o> </o>
The title of the Post is ‘Macro Error’. Under this title, I looked through the posts # 1 to #10. In post #10, It appears that the originator Carbob was asking for almost the same sort of assistance as in my own part 1 – although relating to 6/36 combinations. I observed that Xenou, in post #10 advised Carbob as follows:
<o> </o>
a) Enter 6 in Cell B1
b) In Cell B2 = True (Combination)
c) In Cell B3 – False (without repetition – numbers cannot repeat), and
d) In Cell B5 down enter Nos 1 to 36.
<o> </o>
He also indicated that when he ran it, it provided 376,992 combinations. When I looked through, I did not find the suggested macro.
<o> </o>
I know that there are usually different ways of achieving a given result. Please could you look at the above link and kindly ascertain the exact macro in question and whether it would be easier for me to use that. Is it possible to also help look into the difference in outcomes? You suggested that your macro for my 6/30 combinations would produce 593,775 combinations but from the above, Xenou’s suggestion for 6/36 would produce 376,992 combinations.
<o> </o>
I would appreciate your kind help regarding whichever approach would be easier for me to use given my limited knowledge in VB and indeed in Excel. I hope that you would also still find time to look into the part 2 of my post.
<o> </o>
Again, thanks for everything.
<o> </o>
Kenny

 

[baitmaster]

I think the macro he's talking about is the macro being used in that thread, at post #1. I've had a quick look at it, and to me it looks overcomplicated. I think I've handled it a different way by setting up loops that simply won't return repeated answers, rather than loop through everything and then test to see if duplication has occurred on each line

The results discussed in that other thread are for 5/36, not 6/36. The maths for the # of permutations are
x!/(y! * (x-y)!)
where x = 30 and y = 6 in your case. The qty values of 376,992 and 593,775 are both correct for the input values stated on each

In order to run this code, as stated you will need a different version of Excel, purely for the number of rows that you need - XL03 limits you to 65,536 rows. You could adjust the criteria to select only 4 from 30 for test purposes, as this will require only 27,405 rows

Whichever version you use, you would run the code as per the following steps, noting that it covers both parts of your requirement:

1) use the code shown in post 2. Adjusted for only 4/30, it would be as follows:

Option Explicit
Sub writeNums()
Dim i As Integer, j As Integer, k As Integer, l As Integer, counter As Long
For i = 1 To 27
    For j = i + 1 To 28
        For k = j + 1 To 29
            For l = k + 1 To 30
                counter = counter + 1
                Cells(counter, 1) = i
                Cells(counter, 2) = j
                Cells(counter, 3) = k
                Cells(counter, 4) = l
            Next l
        Next k
    Next j
Next i
End Sub

To run the code, from Excel, hit ALT + F11 to open the VB Editor, create a new code module, and paste the code I've written. Hit F5 to run it, making sure you have the right sheet open in Excel. You may want to be able to see Excel when you run the code, so you can see something happening. This code will take time to complete

2 - once this is complete, insert a new header row 1, apply autofilter (Data > Filter > Autofilter)
3 - create a named range of six cells, 1 row x 6 columns, apply the name "results" (Insert > Name > Define)
4 - type your results in ascending order

Go back to the VB Editor, and paste the code provided in Post 4. For 4/30 permutations this would be

Sub identifyResults()
Dim AFrange As Range: Set AFrange = ActiveSheet.Range("$A$1:$F$27406")
With AFrange
    .AutoFilter Field:=1, Criteria1:=Range("results").Cells(1, 1)
    .AutoFilter Field:=2, Criteria1:=Range("results").Cells(1, 2)
    .AutoFilter Field:=3, Criteria1:=Range("results").Cells(1, 3)
    .AutoFilter Field:=4, Criteria1:=Range("results").Cells(1, 4)
End With
AFrange.SpecialCells(xlCellTypeVisible).Interior.Color = 65535
ActiveSheet.ShowAllData
End Sub

Run this code in the same way

 

[kenny9002]

Hi Baitmaster,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Thanks again for your prompt response and for all the time that you have put into this. I appreciate all that very much.<o></o>
<o></o>
I have noted the points you made and I am willing to adopt your approach. There is one point though that I need further clarification on: I suspect that my initial post could have been a bit misleading. I indicated that I have '30 numbers' for which I need all the possible 6-number combinations. The said numbers are NOT 30 consecutive numbers starting from 1. They are actually 30 different/random numbers selected between 1 and 50. I am therefore wondering where I would need to type in the chosen 30 different numbers. They are not consecutive numbers from 1 to 30.<o></o>
<o></o>
In the 2nd paragraph of my post #5 above, it is this thinking that I had in mind - wondering where I would need to specify or type in the chosen 30 numbers and also the 6 to show that I wish to generate all possible 6-number combination (without repetition) from the CHOSEN 30 DIFFERENT NUMBERS. I don't know whether my post #5 was confusing and made you think that the 30 numbers run consecutively from 1 to 30. This is not the case, please.<o></o>
<o></o>
The other thing that I note is that in your last post - post #6 - you used the term 'permutations'. Is this correct, and is it possible that this thinking influenced the code that you produced. I thought that the principle/technique we are looking at for dealing with my problems is 'COMBINATION' and not 'permutations'.<o></o>
<o></o>
If I am able to clear these issues, I am happy to use your suggested approach.<o></o>
<o></o>
Please, help, and thanks for everything.

PS/ As I searched the threads, I got information about what is referred to as 'Myrna Larson's Code'. I wonder if you've come across this and whether it can be used to achieve the part 1 of my problem.
<o></o>
Kenny <o></o>

 

[baitmaster]

It doesn't matter too much what the 30 numbers are, so long as it is the same 30 numbers used throughout all results. You will need to use Ctrl + H (find and replace) to replace the results Ive programmed, with the corresponding value syou want to used - so the 1-30 effectively become place holders, which you replace with values required. Any other approach will just cause problems. You can programme a short macro to do this element as well as you want, not really worth the effort for a one-off

Combinations, permutations... ahh yes, terminology, never my strong point... what I've provided is every unique combination of [potentially any] 30 numbers without repetition... call 'em what you want

Had a look at Myrna Larson's code, but don't see the need to try and adapt this process for that code, because I believe this works ok

 

[baitmaster]

Here is an alternative to the first piece of code, which writes any 30 numbers you want. You need to create a named range called "numbers" and put your 30 values in it, before running this code as before. It pulls the 30 values into an array, then writes the contents of the x'th value of the array, instead of the x'th number

It also shows your current progress in the statusbar

Sub writeNums()
Dim i As Integer, j As Integer, k As Integer, l As Integer, m As Integer, n As Integer, counter As Long
' create array to hold numbers required in 30 unique placeholders
Dim arrVals()
ReDim arrVals(1 To 30)
Dim x As Integer
For x = 1 To 30
    arrVals(x) = Range("numbers").Cells(1, x).Value ' swap (1,x) to (x,1) if using column instead of row
Next x
counter = 1 ' avoids header row
' write all unique combinations of numbers
For i = 1 To 25
    For j = i + 1 To 26
        For k = j + 1 To 27
            For l = k + 1 To 28
                For m = l + 1 To 29
                    For n = m + 1 To 30
                        counter = counter + 1
                        Cells(counter, 1) = arrVals(i)
                        Cells(counter, 2) = arrVals(j)
                        Cells(counter, 3) = arrVals(k)
                        Cells(counter, 4) = arrVals(l)
                        Cells(counter, 5) = arrVals(m)
                        Cells(counter, 6) = arrVals(n)
                        Application.StatusBar = Format(counter, "0,000,000")
                    Next n
                Next m
            Next l
        Next k
    Next j
Next i
Application.StatusBar = False
End Sub

 

[kenny9002]

Hi Baitmaster,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Thanks for your responses and for the alternative code which you have very kindly provided me. I will use it and trust that it will help me to achieve the result that I desire.<o></o>
<o></o>
Please, forgive my knowledge of VB macros and Excel which is very elementary. You have advised that I create an array of any chosen 30 values, and name the array 'numbers'. Could you please advise me - where do I place the 30 numbers. Is it on the same Excel sheet that I will run the macro on, eg. Sheet 1? Or do you mean that I should open an Excel Sheet 1, give it the name 'numbers', and then type in my different 30 numbers from, say cell A1 down to A30, and then open Sheet 2 and run the macro from Sheet 2? Or have I got it wrong here?<o></o>
<o></o>
I also hope that the Part 2 code you posted earlier should also work with this alternative code.<o></o>
<o></o>
Thanks so much. I remain very grateful to you.<o></o>
<o></o>
Kenny<o></o>