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>

 

[kenny9002]

Hi Baitmaster,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I hope you are doing OK. I’m awfully sorry for bothering you and for reporting back that I’m still struggling with the ‘Matching’ part of my project. With all the help you have given, I decided to do all I can to get the second part – the matching part – work for me. But I’ve continued to have problems with it. With the knowledge that I have since gained, I have tried to figure out the problem. Unfortunately I’ve been unsuccessful, hence I have to ask for your further help, please.
<o></o>
I believe I did all that you instructed me to do, as follows:
<o></o>
1) With respect to the ‘numbers’ sheet:

a) Merge every 6-number combination into a single value in a single cell in column G and name that column ‘combinations’.
<o></o>
b) Copied the formula and pasted them as values onto themselves to create the text-string of the 6-numbers, separated by dots.
<o></o>
c) The column G came out quite OK.
<o></o>
d) As you instructed, I created another column H and named it ‘Match 6’
<o></o>
2) For the ‘Results’ Sheet:
<o></o>
a)The results occupy columns A to F, and they were sorted in ascending order (smallest to largest) as you suggested.

b)Apart from that, nothing else was done to the sheet.
<o></o>
3. How I proceeded from there:
<o></o>
a) In the VBA editor, insert new Module

b) In the blank procedure window, I pasted the macro you wrote. On the procedure window, there are two drop-down combos side-by-side, namely:

- General; and
- Identifyresults (This is the name of the macro you wrote)

c) Closed and returned to Excel, clicked View, Macro.

d) When I clicked RUN, I got an error message as follows:

Run-time error ‘1004’<o></o>
Method ‘Range’ of object ‘_Global’ failed<o></o>
<o></o>
End, Debug, Help<o></o>
<o></o>
e) On clicking ‘debug’, I was taken back to the VBA editor and I could see that the part of the second line of the code which I have underlined as shown below, was highlighted in yellow colour:
<o></o>
Dim i As Integer, x As Integer: x = Range("results").Rows.Count
<o></o>
Infact, when I tried the ‘Matching’ code the day after you posted it, the ‘not found’ result which it showed for all the rows appeared in column 7 of the Results Sheet. I thought it would have been in the ‘Match6’ column of the ‘numbers’ sheet. Even now, I can’t even get that. All I get is the error described in 3 (d) and (e) above.
<o></o>
I have tried everything I could and have failed to get the matching part to work for me. I need your help. I know you’ve put in a good deal of your time to help. I apologise for bothering you further.
<o></o>
Meanwhile, I recall that in an earlier post, you indicated you tested the code but with 1 to 30 consecutive numbers, and that if any set contained any number > 30, it would not be picked up. I am providing below the 30 non-consecutive numbers that I used for my combinations:
<o></o>
9, 17, 44, 35, 11, 30, 12, 48, 38, 6, 20, 42, 24, 27, 32, 36, 15, 41, 45, 3, 2, 21, 39, 33, 14, 23, 46, 18, 28, 5<o></o>
<o></o>
I had earlier uploaded some part of the published lottery results (see my post #14) for the www.box.net link in case you need to use the results to test the macro.
<o></o>
Infact, in order to try and get it work for me, I copied two 6-number actual results from the ‘Results’ sheet and incorporated them into my combinations to see if the matching will pick them up. I didn’t succeed. Nothing happened this time around.
<o></o>
Please could you kindly look into this for me to determine where it is all going wrong for me. If I can get this matching of 6 numbers to work, I believe I will be able to tackle the 5+ and 5, following the instructions you gave.
<o></o>
The ‘Matching’ code you wrote is in your Post #16 above – in case you wish to look at it again, please.
<o></o>
Thank you for everything.
<o></o>
Kenny

 

[kenny9002]

I refer to my Post #21 which you might not have read. Please disregard only paragraph 3 (d) and (e) of that post. A few minutes ago, the error that I reported stopped showing. I was able to get it, again, to show 'not found' throughout all the rows, just as it did many days ago.

Also, it still shows the 'not found' in the 'Results' sheet column G (column No. 7) just as I reported earlier.

And even when I incorporated a few 6-number actual results into the combinations in the 'Numbers' sheet, it still showed every row of the 'Results' sheet as 'not found'.

Infact, as a test, I switched and copied a few 6-number combinations and placed them in appropriate rows in the 'Results' sheet - re-sorted the sheet, and ran the Macro. I still got 'not found' in all the rows in the 'Results' sheet. I don't know what else to do to get it working. There must be something that I am doing very wrong!

Thanks.

Kenny

 

[kenny9002]

Hi Baitmaster,

I suppose you are very busy and might not have read my most recent posts addressed for your kind attention. Please I'd appreciate it if you can find time to have a look at the issue. You've already done a great deal - with just the final tweaks to get it all done.

Thanks.

Kenny

 

[kenny9002]

Hi Baitmaster,

I hope you've not given up on my project. I am still stuck with trying to match the 6 results with your 6-results Matching Code. I don't know if you have seen my two last posts. I am sorry if I'm having to bother you too much.

I ask that you give me a few more minutes. I am providing you here (as I had done in one of last two posts) the actual 30 numbers that I use for the combination.

9, 17, 44, 35, 11, 30, 12, 48, 38, 6, 20, 42, 24, 27, 32, 36, 15, 41, 45, 3, 2, 21, 39, 33, 14, 23, 46, 18, 28, 5

I suppose you already have the Lottery Results. In your earlier post, you indicated that you were still using 1 to 30 for the combination. I don't know if this is a factor, hence I am asking that you please use my own 30 numbers above to generate the combinations and see if the matching code will work with the outcome.

As I indicated earlier, if I can get this 'match 6' code to work, I will make do with that and continue working on my project.

Thanks for your continued help.

Kenny