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]

You need a range of cells, 30 columns x 1 row

This range can be anywhere you want, on any sheet (with any sheet name)

The range of cells must be named "numbers". To do this, either
1) select your 30 cells, and type the word "numbers" into the "Name Box", located at the top of Excel, just above Cell A1
2) In XL03, I seem to recall you use [Insert > Name > Define] menu to create the name. In XL07 and later this is on the [formulas > name manager] menu

This named range is already referred to within the code, using the line

Range("numbers")

The actual array that I mentioned is created within the code as well, by loading all values from the named range, into the object I've named "arrValues()". This holds your 30 values in 30 array locations (1-x), which can then be pulled out when needed. This object only exists for the duration of the code running

The code must be run with the sheet active, on which you want your combinations to be displayed. For simplicity, I would only use the 1 sheet myself, dumping my 2 named ranges ("numbers", and "results") over on the right somewhere, i.e. not in columns 1-6!

There are two entirely separate sections of code. The first creates your combinations. The second analyses your combinations to see which have been selected in your results. Part 2 works independently of Part 1, and hence yes, it will work ok

 

[kenny9002]

Hi Baitmaster,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
The difficulty I am having with getting this to work (albeit very elementary for the initiated ones like you), makes me feel like a dumb - despite the fact that you've done a very brilliant work to help me. I followed your instructions, but couldn't get it to work. I believe that I am definitely doing something WRONG. Please bear with me, I'll briefly take you through what I did so you can, if possible, point me to what I am doing wrong.<o></o>
<o></o>
Meanwhile, in order not to be faced with problems of space, I discarded my Excel 2003, upgraded to Office 2010, so I can use Excel 10.<o></o>
<o></o>
1. I opened a new Excel workbook and selected Sheet No. 1.<o></o>
2. Entered my 30 numbers in Row No. 1 (i.e. Cells A1 to AD1).<o></o>
3. Highlighted/selected the said Cells A1 to AD1 and typed the word 'number' in the 'Name Box' - as you directed.<o></o>
4. Pressed Alt + 11 (to get to the VB editor), Insert, Module.<o></o>
5. I copied and pasted your most-recent Code. (I simply copied and pasted your Code without changing or additing anything to the code).<o></o>
6. I closed and went back to Excel.<o></o>
7. With my cursor on Cell A3, I pressed F5 to run the Macro, and I got a dialogue box named 'Go To'.<o></o>
8. Inside the dialogue box was the name 'numbers'. I clicked on 'numbers' and clicked OK.<o></o>
9. The only thing that happened after that is that the Cells A1 to AD1 (Row 1) which contain the 30 numbers, were highlighted, and nothing else happened.<o></o>
<o></o>
I tried this a few times with the same result. Please may I know if I am supposed to make any changes or add anything to the Code before or after pasting it onto the Editor?<o></o>
<o></o>
I know that your Code works very well. It's just that I have not done the correct thing. Please kindly advise. <o></o>
<o></o>
Thanks very much.
<o></o>
Kenny

 

[baitmaster]

F5 runs the macro from the VBE, but not from Excel, sorry I wasn't clear on that - by closing the VBE, F5 no longer works as you've found. Either hit F5 when your cursor is within the actual code (stick the window to one side so you can see progress in XL), or find another way to run the macro, e.g. from Macros menu within the View Tab, or by attaching a form button to the page, and linking it to the macro

The macro shouldn't need any other changes, you set the named range up correctly. Hopefully this clarifies things, but again let me know if there's any more probs

 

[kenny9002]

Hi Baitmaster,

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
You are really a GENIUS and a STAR! The first part of the code worked out beautifully well. I was able to generate the 593,775 combinations. I am really very grateful to you from the bottom of my heart.

<o></o>
Now I want to go on to the 2<SUP>nd</SUP> part, and wish to clarify a few things:

<o></o>
1. I have the generated 6-number combinations on Sheet 1 – columns A to F (i.e. A1:F593,776). The Sheet has, on the 1<SUP>st</SUP> row (A1 to AD1) the 30 nos typed in initially. Now that the combinations have been generated, I’d delete those 30 numbers from the row, and use the row as a ‘header’ row with the following headers: n1,n2,n3,n4,n5,n6.

2. For the Part 2, I am planning to place the drawn results (very many rows also of 6 numbers) onto Sheet 2. Is this OK? I don’t know if this is what paragraph 2 of your Post #4 was referring to.

3. I was thinking that to compare the generated combinations with the drawn results, your Part 2 Code will pick (one row at a time) from the generated combinations on Sheet 1 and match it with all the rows of the drawn results on Sheet 2 – in order to determine which row of the 6-number combinations has come up in the list of drawn numbers.

4. However, in your Post #4, paragraph 2, you indicated, for the 2<SUP>nd</SUP> step of the task, that “I inserted a header row, applied Autofilter on that header row, and created a range of 6 cells into which I placed my results (in ascending order, or it won’t work). I named that range ‘results’”.

a) When you referred to ‘inserting the header row’, does that apply to Sheet 1 i.e. the generated combinations as I indicated in (1) above? Or does it apply to the list of the already drawn results in my own Sheet 2?



b) Re – Creating a range of 6 cells into which the results should be placed: Please are you suggesting that the said 6 cells for the results should be created on the same sheet 1 that houses the generated combinations? If so, may I know how I can achieve that, please.

<o></o>
c) Re – ascending order, or it won’t work: Were you suggesting that the already drawn results should be in ascending order? I have uploaded an Excel copy sheet of the results the way it is usually published (Lottery Results.xls), for your information, please. I don’t know if you are registered with www.box.net. The URL for the uploaded file is as follows:
<o></o>
<o></o>
http://www.box.net/shared/223860shknryot73dcme

<o></o>
d) If you take a look at the file, I believe it might not be possible to arrange the results in ascending order. If there is a way, I’d like to know so I can do it – assuming that is what you were referring to.

<o></o>
5. My last request on this subject: - please forgive me for asking too much of you: Is it possible to tweak the Part 2 Code. Something has just occurred to me. In my initial post, my idea was that, for the Part 2, I’d like the Code to highlight in colour only the rows in which all the 6 numbers drawn MATCH the 6 numbers generated. I just realised that, in addition to highlighting the 6-number matched rows, I’d like the Code also to highlight any rows/combinations where 5 numbers of the 6-number combinations match the drawn results (i.e. both 6-number and 5-number matches), if it is possible to pull this off.
<o></o>
I am sorry for bothering you this much. This will be it for all that I wish to request.


<o></o>
Thank you so very much.

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

 

[baitmaster]

OK, glad you got the first bit working. The second needs a bit of tweaking because we are now looking for multiple sets of results in one hit, thats not difficult but the current method I’ve got may be slow at doing it. The test for 5 results from 6 also changes things a bit, and opens up more questions, such as if 6 and 5, why not 4? I assume that also wins a prize? And 5 plus the bonus ball, well that’s an extension of 5, i.e. crack 5 and add a predefined number into the mix...

A problem with 5, and even more 4, is that the number of possible matches goes up significantly. Using Autofilter as a quick hit probably won’t do too well in this case, although it will if you are happy to leave the computer running for potentially quite a long time – hours maybe, but not sure yet. Also, I think the actual algorithms are gonna cause more issue on this one, because you're not trying to find an exact result, so you have to look at various combinations

I’m gonna look at 6 only, using autofilter, then think about 5 and 4. This way you will at least see how I’m building from testing one set of results, to testing multiple sets of results, because the part2 code wont change much

Sub identifyResults()
Dim AFrange As Range: Set AFrange = Sheet1.Range("$A$1:$F$593776")
Dim i As Integer, x As Integer
x = Range("results").Rows.Count
With AFrange
    For i = 1 To x
        Application.StatusBar = "looking at result " & i & " of " & x
        .AutoFilter Field:=1, Criteria1:=Range("results").Cells(i, 1)
        .AutoFilter Field:=2, Criteria1:=Range("results").Cells(i, 2)
        .AutoFilter Field:=3, Criteria1:=Range("results").Cells(i, 3)
        .AutoFilter Field:=4, Criteria1:=Range("results").Cells(i, 4)
        .AutoFilter Field:=5, Criteria1:=Range("results").Cells(i, 5)
        .AutoFilter Field:=6, Criteria1:=Range("results").Cells(i, 6)
        .SpecialCells(xlCellTypeVisible).Interior.Color = 65535
        Sheet1.ShowAllData
    Next i
End With
Application.StatusBar = False
End Sub

To make this work, I created a named range again, of 680 rows and 6 columns, and called it "results". I stuck this range on a different sheet, but that doesn't affect the code. The only thing that does, is that the autofilter is in a specific set of cells (see code for details), on the sheet named in VBA as "sheet1". NOTE this is not necessarily the name of it in Excel, but in VBA - look at the project explorer window in VBA to check. Rename it using the properties window if needed

This code works, but is slow, as I thought. I'll think about it some more and try to find a quicker method of matching results - it should be possible via a formula, but I don't want to dump 600,000 lookup formulas into the model, cos that will kill it too - especially if theres some "fuzzy" logic in there, i.e. matching only part of results, say 4 from 6

 

[baitmaster]

OK, a different approach, which is much faster than the previous approach, and more adaptable

We have our range of combinations, 600k rows x 6 columns
In cell G7, I created the formula "=A2&"."&B2&"."&C2&"."&D2&"."&E2&"."&F2" and copied it down to all rows (double click the fill handle, the dot in the bottom right of the cell, its the quickest way)

I then copied these formulas, and pasted them as values only (onto themselves), to avoid calculation speed problems

I named the entire column G "combinations". Column H I named "Match6"

Without changing the configuration of the results sheet that I described in my previous post, I used the following code:

Sub identifyResults()
Dim i As Integer, x As Integer: x = Range("results").Rows.Count
Dim strResult As String
Dim resultRow As Double
With Range("results")
    For i = 1 To x
        Application.StatusBar = "looking at result " & i & " of " & x
        
        strResult = .Cells(i, 1) & "." & .Cells(i, 2) & "." & .Cells(i, 3) & "." & .Cells(i, 4) & "." & .Cells(i, 5) & "." & .Cells(i, 6)
        
        resultRow = 0
        
        On Error Resume Next
        resultRow = Application.Match(strResult, Range("combinations").Cells, 0)
        On Error GoTo 0
        
        If resultRow > 0 Then ' match found
            .Cells(i, 7) = resultRow
            Range("Match6").Cells(resultRow, 1).Value = Range("Match6").Cells(resultRow, 1).Value + 1
            
        Else
            .Cells(i, 7) = "not found"
        End If
        
    Next i
End With
Application.StatusBar = False
End Sub

 

[kenny9002]

Hi Baitmaster,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I can’t thank you enough for your rare patience with me.
Could you please check below to see what I have done regarding Part 2. I don’t believe that I am on the right track, given the outcome that I got.

<o></o>
1. On my first Sheet which I had named ‘Numbers’, I have the 593,775 rows of the 6-number combinations that I generated using your Part 1 code. It starts from A1 to F593,775.

2. As per your most recent post #16, I inserted on Cell G1 of the same ‘Numbers’ sheet the formula =A2&"."&B2&"."&C2&"."&D2&"."&E2&"."&F2, copied it down to all the rows, and then copied and pasted the it as ‘values’ onto themselves. I gave this column G the name ‘Combinations’ and then gave column H the name ‘Match6’.

3. I don’t know whether what I did here re columns G & H, is what you wanted me to do on the ‘Numbers’ sheet. Or did you want me to do that on the ‘Results’ sheet?

4. Next, I copied and pasted some of the lotto published results (a copy of which I uploaded earlier for your information) onto a second sheet which I named ‘Results’.

a) The published lotto results had other columns such as data, bonus numbers, etc., but I deleted those other columns, leaving only the columns containing the 6-number results (i.e. columns A to F) the same number of columns also as in the case of the generated combinations in the ‘Numbers’ sheet.

b) I highlighted all the cells containing the results (i.e. A1 to F687) and then created a named range called ‘Results’.

c) That is all that I did with the ‘Results’ sheet.

<o></o>
5. With Alt+F11, I went to the VB Editor, copied and pasted your most-recent Code (the one you indicated would be faster and more adaptable – Post #16).

6. I went back to Excel, Macros, selected ‘identifyResults’ as the one to run, and clicked Run.

7. Nothing happened on the ‘Numbers’ sheet. However, I discovered that on the next sheet – the ‘Results’ sheet, column G was filled with ‘not found’ all through down.
May I know, please, where I went wrong.
<o></o>
Also, in your Post #15, you touched on the prospect of testing for ‘5 plus the bonus ball’ and also ‘4 results from 6’. Although in my previous post, re Part 2, I requested for ‘6 from 6’ and ‘5 from 6’, I am also interested in the ‘5 plus the bonus ball’. I did not want to push my luck by requesting for that also as I felt guilty that I was beginning to be too demanding of you.
<o></o>
However, I would NOT need the ‘4 from 6’. If it can be achieved, I will be OK with –
a) 6 from 6
b) 5 from 6 Plus BONUS ball; and
c) 5 from 6.

That will serve my purpose and do me OK.
<o></o>
Thanks again for all your help.
<o></o>
Kenny

 

[kenny9002]

Hi Baitmaster,

I hope you are well. I was wondering if you've gone through my last post.

Thanks.

Kenny

 

[baitmaster]

Hi Kenny, I briefly saw your post over the weekend, but didn't have time to look into it

Looking through, you are totally on the right track, although there's a few small points to check

WRT your numbered list:
1) OK
2) Mostly correct, except we both possibly described the formula incorrectly. The purpose is to merge every 6-number combination into a single value in a single cell, so that we can use a MATCH formula to find it - we can use this formula on a single column of unique values, but not on 6 separate columns of values. Obviously, the cell with this formula should refer to the 6 values on the same row, but when I described it I said I wrote in row 7, when you described it you said Row 1. In both cases we referred to row 2, so of course we should write that formula in cell G2! The purpose of copy/paste values, is to help simply create the text string we are going to use. This text string is only the 6 numbers, separated by dots. Check these are all as you would expect for each row
3) Aside from checking point 2, you did exactly what I intended
4), a) and b) also fine
5) correct
6) correct
7) please check this. I also had many, many combinations return "not found", but this is because in most cases, they weren't. In my test I am still using the combination numbers 1-30 only. The results include values up to 49, so ANY result with any number >30, is indeed not found. This is the majority of results; my first exception was row 14 [1,3,5,13,21,24], which matched combination row 24,654. To test this, find a row that you know should exist, or at least check "results" column G are definitely ALL "not found"

Aside from the above, I've also been a bit stumped by the second requirement. I believe we need to generate, for every set of results, every possible combination of numbers that matches 5 of these - this will allow us to search for that exact set of numbers. We MUST ensure no duplications at this point, and we must ensure each set of numbers is still in ascending numerical order. I think I have a method to do this, but it needs time to get it right, which I don't currently have.

I think we need this approach because I'm not sure how to use "fuzzy" logic to match on only 5 of the numbers, certainly using the MATCH formula approach. Theoretically any combination MIGHT match 5 balls with more than 1 set of results, hence ensuring no duplication, because we will simply have to have a counter for each combination to say how may times it won

An alternative approach MIGHT be to look again at the autofilter approach, and use "<>[your 6 numbers]" as the filter criteria, and play around with this a bit, but still not sure at the moment

Please check what I wrote above, and report back on point 7. When I get the chance, I'll give more thought to 5 numbers

 

[baitmaster]

The following code seems to work for 5 numbers. It takes some time to run, maybe an hour or more, but progress is reported on the statusbar

I'm also going to leave it to you to work out what to do with this code, and it's up to you to verify if you are happy with the results

To look at the bonus ball, you can delete the 1 to 43 loop, and replace just the single value with the bonus ball value. Hopefully you've learnt something quite complex by looking at this, so do try and do this last piece of coding by yourself - I'll help if needed, the code update is simple enough now, although you also have to create the location (a new data column?) for the bonus ball data

Sub identifyResults5()
 
Dim intResultLoop As Integer, i As Integer, j As Integer, k As Integer
Dim maxNum As Integer, resultQty As Integer: maxNum = Range("maxNum").Value: resultQty = Range("results").Rows.Count
Dim intBallReplaced As Integer, intBallNum As Integer
 
Dim strResult As String
Dim resultRow As Double
 
Dim arrResOther(1 To 43)
Dim arrOriginalResults(1 To 6)
Dim arrRevisedResults(1 To 6)
Dim arrOrderedResults(1 To 6)
 
' reset results columns
Range("[COLOR=blue]Match5[/COLOR]").ClearContents
Range("Match5").Cells(1, 1) = "Match 5"
Range("results").Columns(7).ClearContents
 
With Range("results")
    For intResultLoop = 1 To resultQty
 
        ' assign current set of results to 6-cell array
        For i = 1 To 6
            arrOriginalResults(i) = .Cells(intResultLoop, i)
        Next i
 
        ' create array of other possible numbers, to create every combination for match 5
        k = 0
        For j = 1 To maxNum
            If Not (j = arrOriginalResults(1) Or j = arrOriginalResults(2) Or j = arrOriginalResults(3) Or j = arrOriginalResults(4) Or j = arrOriginalResults(5) Or j = arrOriginalResults(6)) Then
                k = k + 1
                arrResOther(k) = j
            End If
        Next j
 
        ' replace each of the 6 numbers with each from the array of others to create new array of 6 results
        For intBallNum = 1 To 6
 
            For intBallReplaced = 1 To 43
 
                ' duplicate array with original numbers
                For i = 1 To 6
                    arrRevisedResults(i) = arrOriginalResults(i)
                Next i
 
                ' replace one ball value at a time
                arrRevisedResults(intBallNum) = arrResOther(intBallReplaced)
 
                Application.StatusBar = "looking at result " & intResultLoop & " of " & resultQty & ": ball " & intBallNum & " = " & Format(arrResOther(intBallReplaced), "00")
 
                ' create new array using existing array in ascending numerical order
                For i = 1 To 6
                    arrOrderedResults(i) = WorksheetFunction.Large(arrRevisedResults, 7 - i)
                Next i
 
 
                strResult = arrOrderedResults(1) & "." & arrOrderedResults(2) & "." & arrOrderedResults(3) & "." & arrOrderedResults(4) & "." & arrOrderedResults(5) & "." & arrOrderedResults(6)
 
                resultRow = 0
 
                ' use existing code
                On Error Resume Next
                resultRow = Application.Match(strResult, Range("combinations").Cells, 0)
                On Error GoTo 0
 
                If resultRow > 0 Then ' match found
                    .Cells(intResultLoop, 7) = .Cells(intResultLoop, 7) & resultRow & ", "
                    Range("Match5").Cells(resultRow, 1).Value = Range("Match5").Cells(resultRow, 1).Value + 1
                End If
 
            Next intBallReplaced
        Next intBallNum
    Next intResultLoop
End With
 
Application.StatusBar = False
End Sub