Excel Macro

[Kenya649]

How do I write an Excel VBA code to pick a set of six numbers that are in the following lines and every line must have at least one number of my set of six numbers.

e.g the following are the lines

6 13 16 31 34 47
7 14 17 32 35 48
9 16 19 34 37 1
10 17 20 35 38 2
13 20 23 38 41 5
18 25 28 43 46 10
19 26 29 44 47 11
21 28 31 46 49 13
23 30 33 48 2 15
26 33 36 2 5 18
27 34 37 3 6 19
28 35 38 4 7 20
30 37 40 6 9 22
31 38 41 7 10 23
34 41 44 10 13 26
36 43 46 12 15 28
37 44 47 13 16 29
41 48 2 17 20 33
44 2 5 20 23 36
46 4 7 22 25 38
48 6 9 24 27 40
49 7 10 25 28 41
3 10 13 28 31 44

and I would want to identify a set of six numbers and each line must have at least one of the six identified numbers.

Please assist to identify the set(s) of six numbers using a VBA code


Thanks

 

[CMF]

Sunday morning and I need a break ...

Kenya649

The big question that springs to mind when I read posts requesting code is whether that person has done some work trying to solve the problem them self. To that end supplying some pseudo code which clearly and logically sets out in everyday language what you are trying to do and where you are stuck is normally a requirement for programming assistance (especially from me!). The point is that you need to indicate that you are not asking others to do work for you that you should be doing yourself. If you can't indicate some competence in coding in Excel then what can follow is a never ending stream of questions each of which may indicate a lack of understanding and an unwillingness to do much yourself. (Have I seen an example of this recently?)

The first question that I ask myself is why you have supplied a set of lines where the integers are not in numerical order as is normal. This is an indication that you don't know how to code such a simple routine and raises questions about whether you would understand code that is more complex.

The next question is whether there is any sense in the question you ask:

"How do I write an Excel VBA code to pick a set of six numbers that are in the following lines and every line must have at least one number of my set of six numbers. ... and I would want to identify a set of six numbers and each line must have at least one of the six identified numbers."

​

A suggestion is that instead of using the general term numbers use lines or blocks and integers.

So, do you want a set of six lines?

The set of 23 lines with six integers per line (Pick 6) you supplied uses 45 integers from a Pool of 49. A set of 6 lines with unique integers can be done easily 1 2 3 4 5 6 ... etc using 36 of the 45 integers you have used in your set. Unfortunately, (maybe) it is impossible to fit 36 integers into 1 line of 6 integers. I assume you know this so there must be some other way of setting out your objective that makes sense.

Now, GillesD is the resident Excel responder at this site and I don't want to steal his thunder but I doubt whether even he can make sense of your post.

Regards
Colin Fairbrother

 

[Kenya649]

The big question that springs to mind when I read posts requesting code is whether that person has done some work trying to solve the problem them self. To that end supplying some pseudo code which clearly and logically sets out in everyday language what you are trying to do and where you are stuck is normally a requirement for programming assistance (especially from me!). The point is that you need to indicate that you are not asking others to do work for you that you should be doing yourself. If you can't indicate some competence in coding in Excel then what can follow is a never ending stream of questions each of which may indicate a lack of understanding and an unwillingness to do much yourself. (Have I seen an example of this recently?)

CMF,

I'm really sorry for the confusion caused.
I'm not an expert in Excel Macro but I'm able to write codes that can sort, compare numbers and probably more.
When I asked for the assistance I had tried all I know and could not figure out how to do it.

6 13 16 31 34 47
7 14 17 32 35 48
9 16 19 34 37 1
10 17 20 35 38 2
13 20 23 38 41 5
18 25 28 43 46 10
19 26 29 44 47 11
21 28 31 46 49 13
23 30 33 48 2 15
26 33 36 2 5 18
27 34 37 3 6 19
28 35 38 4 7 20
30 37 40 6 9 22
31 38 41 7 10 23
34 41 44 10 13 26
36 43 46 12 15 28
37 44 47 13 16 29
41 48 2 17 20 33
44 2 5 20 23 36
46 4 7 22 25 38
48 6 9 24 27 40
49 7 10 25 28 41
3 10 13 28 31 44

If you take 2,7,13,19,40,43 (set of six numbers) you will find each line above has at least one number from the set of six numbers.
My question was assuming you had the 23 lines above, how can you get 2,7,13,19,40,43 using a program?

Thanks

 

[CMF]

Apology accepted and thank you ...

Kenya649

Thanks for apologizing.

If I understand you correctly you want to know the combinations of 6 integers that can be formed from the pool of integers used in a set of combinations of 6 where not necessarily all the integers for the Lotto game are being used - in your case 45 of a possible 49.

Well, not only can you get 2,7,13,19,40,43 but quite a few others as well. I think by me just stating logically what I interpret your objective as the solution is fairly obvious.

Since you are using 45 integers the number of possibilities is 45c6 or 8,145,060. You need to pass as arguments to your procedure the particular integers you are interested in and then enumerate the combinations. This raises the question as to whether you are really interested in such a high number of possibilities or whether you mean something else.

Don't be shy - post some code showing that you are having a go and have a few clues how to go about it and then I or others can show you where you're going wrong or how it can be improved on.

Regards
Colin Fairbrother

 

[time*treat]

Are you asking for copy-&-paste-&-run-ready code, or a p-code description of what you need?

CMF,

I'm really sorry for the confusion caused.
I'm not an expert in Excel Macro but I'm able to write codes that can sort, compare numbers and probably more.
When I asked for the assistance I had tried all I know and could not figure out how to do it.

6 13 16 31 34 47
7 14 17 32 35 48
9 16 19 34 37 1
10 17 20 35 38 2
13 20 23 38 41 5
18 25 28 43 46 10
19 26 29 44 47 11
21 28 31 46 49 13
23 30 33 48 2 15
26 33 36 2 5 18
27 34 37 3 6 19
28 35 38 4 7 20
30 37 40 6 9 22
31 38 41 7 10 23
34 41 44 10 13 26
36 43 46 12 15 28
37 44 47 13 16 29
41 48 2 17 20 33
44 2 5 20 23 36
46 4 7 22 25 38
48 6 9 24 27 40
49 7 10 25 28 41
3 10 13 28 31 44

If you take 2,7,13,19,40,43 (set of six numbers) you will find each line above has at least one number from the set of six numbers.
My question was assuming you had the 23 lines above, how can you get 2,7,13,19,40,43 using a program?

Thanks