Lexicographic Index & Sums

[Alexafjb]

Hello Everyone!!! Newbie here!

I've been look around the board the last few weeks, and I'm trying to put together a macro in excel 2007 that will give me all the "Lexicographic Index Sum's" that add up to say ie."160" I'd also like to maybe, be able to reduce those numbers down to, Lex combinations that contain only 1 to 4 Prime Numbers, that once again add up to ie."160". I've read many threads on macros to get Lex, but nothing close to what i'm looking for. The 2 Games I play, that is... when I play are 6/49 & Lotto Max Canada. Has anyone come across any codeing similar to what I'm looking for? If so, please point me in the right direction.

Oh and Happy, Happy, Holidays!

 

[PAB]

Hi Alexafjb,

Welcome to the Forum .

I've been looking around the board the last few weeks, and I'm trying to put together a macro in excel 2007 that will give me all the "Lexicographic Index Sum's" that add up to say i.e."160".

I am not sure how well you know Macro's but the Macro below will produce ALL 158,923 Combinations that sum to 160 in a 649 Lotto.

Option Explicit
Option Base 1

Const MinA As Integer = 1
Const MaxF As Integer = 49
Const SumTotal As Integer = 160

Sub SumTotal_PAB()
Dim A As Integer, B As Integer, C As Integer, D As Integer, E As Integer, F As Integer
Dim Count As Long
Dim Sum As Integer
Dim n As Long
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
End With
Range("A1").Select
Count = 0
For A = MinA To MaxF - 5
For B = A + 1 To MaxF - 4
For C = B + 1 To MaxF - 3
For D = C + 1 To MaxF - 2
For E = D + 1 To MaxF - 1
For F = E + 1 To MaxF
Sum = A + B + C + D + E + F
If Sum = SumTotal Then
Count = Count + 1
ActiveCell.Offset(0, 0).Value = A
ActiveCell.Offset(0, 1).Value = B
ActiveCell.Offset(0, 2).Value = C
ActiveCell.Offset(0, 3).Value = D
ActiveCell.Offset(0, 4).Value = E
ActiveCell.Offset(0, 5).Value = F
ActiveCell.Offset(1, 0).Select
End If
Next F
Next E
Next D
Next C
Next B
Next A
Columns("A:F").ColumnWidth = 3
With Application
.DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub

If you want to list the combinations of a different Sum then just change the value 160 to whatever in the line:-

Const SumTotal As Integer = 160

I hope this helps!

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[PAB]

Hi Alexafjb,

Just as a follow up, if you wanted the SumTotal next to each combination then use the amended code below.
I have also added ClearContents to the code in case you want to run several different SumTotals one after the other.
This way there will be no overlap and will give you clean data results.

I'd also like to maybe, be able to reduce those numbers down to those that contain only 1 to 4 Prime Numbers, that once again add up to i.e."160".

As far as this is concerned, there are two ways of doing this.

(1) Put the formula...

=SUM(COUNTIF(A1:F1,{2,3,5,7,11,13,17,19,23,29,31,37,41,43,47}))

...into cell J1 and copy down as far as needed.

(2) Add an extra line of code to do this, although this will increase the run time quite considerably.

BTW, there are...

0 Prime + 6 Non Prime = 16,998 Combinations
1 Prime + 5 Non Prime = 49,665 Combinations
2 Prime + 4 Non Prime = 54,684 Combinations
3 Prime + 3 Non Prime = 28,512 Combinations
4 Prime + 2 Non Prime = 8,248 Combinations
5 Prime + 1 Non Prime = 749 Combinations
6 Prime + 0 Non Prime = 67 Combinations
Total = 158,923 Combinations

...if that helps.

Here is the amended code.

Option Explicit
Option Base 1

Const MinA As Integer = 1
Const MaxF As Integer = 49
Const SumTotal As Integer = 160

Sub SumTotal_PAB()
Dim A As Integer, B As Integer, C As Integer, D As Integer, E As Integer, F As Integer
Dim Count As Long
Dim Sum As Integer
Dim n As Long
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
End With
Columns("A:H").ClearContents
Range("A1").Select
Count = 0
For A = MinA To MaxF - 5
For B = A + 1 To MaxF - 4
For C = B + 1 To MaxF - 3
For D = C + 1 To MaxF - 2
For E = D + 1 To MaxF - 1
For F = E + 1 To MaxF
Sum = A + B + C + D + E + F
If Sum = SumTotal Then
Count = Count + 1
ActiveCell.Offset(0, 0).Value = A
ActiveCell.Offset(0, 1).Value = B
ActiveCell.Offset(0, 2).Value = C
ActiveCell.Offset(0, 3).Value = D
ActiveCell.Offset(0, 4).Value = E
ActiveCell.Offset(0, 5).Value = F
ActiveCell.Offset(0, 7).Value = SumTotal
ActiveCell.Offset(1, 0).Select
End If
Next F
Next E
Next D
Next C
Next B
Next A
Columns("A:H").ColumnWidth = 3
With Application
.DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[PAB]

Actually Alexafjb, it takes about four times longer to run with the formula built into the code, not as bad as I initially thought.

Here is the amended code:-

Option Explicit
Option Base 1

Const MinA As Integer = 1
Const MaxF As Integer = 49
Const SumTotal As Integer = 160

Sub SumTotal_PAB()
Dim A As Integer, B As Integer, C As Integer, D As Integer, E As Integer, F As Integer
Dim Count As Long
Dim Sum As Integer
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
End With
Columns("A:J").ClearContents
Range("A1").Select
Count = 0
For A = MinA To MaxF - 5
For B = A + 1 To MaxF - 4
For C = B + 1 To MaxF - 3
For D = C + 1 To MaxF - 2
For E = D + 1 To MaxF - 1
For F = E + 1 To MaxF
Sum = A + B + C + D + E + F
If Sum = SumTotal Then
Count = Count + 1
ActiveCell.Offset(0, 0).Value = A
ActiveCell.Offset(0, 1).Value = B
ActiveCell.Offset(0, 2).Value = C
ActiveCell.Offset(0, 3).Value = D
ActiveCell.Offset(0, 4).Value = E
ActiveCell.Offset(0, 5).Value = F
ActiveCell.Offset(0, 7).Value = SumTotal
ActiveCell.Offset(1, 0).Select
End If
Next F
Next E
Next D
Next C
Next B
Next A
Range("J1:J" & Range("A" & Rows.Count).End(xlUp).Row).Formula = _
"=SUM(COUNTIF(A1:F1,{2,3,5,7,11,13,17,19,23,29,31,37,41,43,47}))"
Range("J1:J" & Range("A" & Rows.Count).End(xlUp).Row).Value = _
Range("J1:J" & Range("A" & Rows.Count).End(xlUp).Row).Value
Columns("A:J").ColumnWidth = 3
With Application
.DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub

Now you can just sort the data as required.

I hope this helps!

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[Alexafjb]

Smiles PAB's Take-out Window!

Hello PAB

Thank you!!! I'm impressed! 3 maybe 4 hours since I opened the thread and on a Sunday before Christmas to boot!

I thought at the most, someone would point me in the direction of another thread on the subject. WoW!

I'm still in the process of learning VBA, Not that hard really, just alot to remember for a 53 year old, like taking out the garbage on the right day and the wife's birthday! Anyways I hope you don't mind me asking questions that these days would make a 1st grader giggle.

Cheers

Alex.

 

[PAB]

Hi Alexafjb,

Anyways I hope you don't mind me asking questions that these days would make a 1st grader giggle.

Not at all, we all have to learn the things we know from somewhere.

Did you particularly want a CSN ( Combination Sequence Number )???
If so, just use the Count variable to put the CSN in column A and adjust the rest of the code accordingly.
This will probably make the sorting part of the exercise easier if nothing else.
I don't know how well you know Excel or Macro's but if you are unable to do this yourself I will update the code and post it for you.

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[Alexafjb]

Hey There PAB

Thought I seen you you hovering in the "who's on line list"!

Through my research, I guess I missed the memo some where.
The diffrence between CSN and Lex is? I thought it was only the way in which they where calculated?
Memo to self: Must read more.

This brings to mind another question I was postulating? Should I start another thread when inquering on proccedures outside the original question, but still with in "Excel & Macros"?

Cheers.
Alex.

 

[Alexafjb]

Hello PAB

I think, I did not fully understand your what you where asking.

Did you particularly want a CSN ( Combination Sequence Number )???
If so, just use the Count variable to put the CSN in column A and adjust the rest of the code accordingly.
This will probably make the sorting part of the exercise easier if nothing else.

After much coffee and a shower!! I should have said represented, ordered, sorted works too!

Cheers
Alex.

 

[PAB]

Hi Alex,

The diffrence between CSN and Lex is?

They are the same, it is just an index of the order they are produced in.
I think the term Lexicographic or Lexicographical is mainly used in the mathematical environment, whereas us in the Lotto environment use CSN.

This brings to mind another question I was postulating? Should I start another thread when inquering on proccedures outside the original question, but still with in "Excel & Macros"?

This depends, is the question totally off topic of the original question asked in this particular thread, if so, then yes, but if it is an adaption then post away.

BTW,

Did you particularly want a CSN ( Combination Sequence Number )???
If so, just use the Count variable to put the CSN in column A and adjust the rest of the code accordingly.
This will probably make the sorting part of the exercise easier if nothing else.
I don't know how well you know Excel or Macro's but if you are unable to do this yourself I will update the code and post it for you.

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[PAB]

Hi Alex,

Sorry, we posted at exactly the same time.

After much coffee and a shower!! I should have said represented, ordered, sorted works too!

I don't quite understand what you mean?

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[Alexafjb]

Shipps Passing in the night!

Hi PAB

The confusion started on my end when you ask:

"Did you particularly want a CSN ( Combination Sequence Number )???"

I had a brain FART and I thought that they where not the same thing. LOL does not matter. I got it.

Alex.

 

[PAB]

Hi Alex,

The confusion started on my end when you asked:

"Did you particularly want a CSN ( Combination Sequence Number )???"

I had a brain FART and I thought that they where not the same thing.

No worries, we have all been there.
Anyway, did you manage to adapt the code to produce the CSN number yourself?
If not, I will amend the code for you if you do want it included.
Also, would you like the code to include the sort function on firstly the number of Prime numbers in Descending order and then the CSN number in Ascending order?

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[Alexafjb]

Hi PAB
I've not run your work as of yet. But, sure if you feel inclinde to put it together. I know I will use it for sure! I've been a pen and paper guy for a long time. My skills with Excel are there but, can't hold a candle to what I seen you and a few others produce. I'm in the process right now of writting up an new thread that will explain more of what I'm trying to accomplish and were I stand on the learning curve when it comes to lotto and VBA. So I hope you will stick with me and point the right direction.

THX
Alex.

 

[PAB]

Hi Alex,

I went ahead and adjusted the code according to my previous post, which includes the addition of the CSN number and the SORT function with respect to the Prime numbers and the CSN number.
Anyway, here it is, give it a go and please let me know what you think.

Option Explicit
Option Base 1

Const MinA As Integer = 1
Const MaxF As Integer = 49
Const SumTotal As Integer = 160

Sub SumTotal_And_Prime_PAB_v2()
Dim A As Integer, B As Integer, C As Integer, D As Integer, E As Integer, F As Integer
Dim Count As Long
Dim Sum As Integer
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
End With
Columns("A:K").ClearContents
Range("A1").Select
Count = 0
For A = MinA To MaxF - 5
For B = A + 1 To MaxF - 4
For C = B + 1 To MaxF - 3
For D = C + 1 To MaxF - 2
For E = D + 1 To MaxF - 1
For F = E + 1 To MaxF
Sum = A + B + C + D + E + F
If Sum = SumTotal Then
Count = Count + 1
ActiveCell.Offset(0, 0).Value = Count
ActiveCell.Offset(0, 1).Value = A
ActiveCell.Offset(0, 2).Value = B
ActiveCell.Offset(0, 3).Value = C
ActiveCell.Offset(0, 4).Value = D
ActiveCell.Offset(0, 5).Value = E
ActiveCell.Offset(0, 6).Value = F
ActiveCell.Offset(0, 8).Value = SumTotal
ActiveCell.Offset(1, 0).Select
End If
Next F
Next E
Next D
Next C
Next B
Next A
Range("K1:K" & Range("A" & Rows.Count).End(xlUp).Row).Formula = _
"=SUM(COUNTIF(B1:G1,{2,3,5,7,11,13,17,19,23,29,31,37,41,43,47}))"
Range("K1:K" & Range("A" & Rows.Count).End(xlUp).Row).Value = _
Range("K1:K" & Range("A" & Rows.Count).End(xlUp).Row).Value
Range("A1", Range("K" & Rows.Count).End(xlDown)).Sort _
Key1:=Range("K1"), Order1:=xlDescending, Header:=xlNo, _
Key2:=Range("A1"), Order2:=xlAscending, Header:=xlNo
Columns("B:K").ColumnWidth = 3
With Application
.DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[Alexafjb]

Lovely Work!

Good Morning PAB

Hope your Holidays are going well!

I’ve plugged in the last Module you created ( CSN & Prime) it ran nice!
I tell ya though, I’d hate to run it on an old processor, slow bus speed & low RAM! I think I would still be waiting for it to finish.

I’ve a Duel Core and 2 Gig’s of Ram and only had Excel & VBE loaded and it still took 5 minutes to complete!

What would your thoughts be on that Module having the ability to do the same thing, “CSN, SORT, PRIME Number” and
include ability of the user to be able to pick 1, 2, or 3 numbers to use as “seed’s” for the generation of the CSN, ect. ?

My thoughts on this;
-It may give the ability to reduce the amount of combination (Note: I said MAY! :-0)!
-In studying Lotto histories most of us can get a warm fuzzy feeling, for at least one number!
-In the other thread I started, I mentioned the DB being 1-49 to give basically a graphical representation, sometime in the future,
I’d like to plug in the outcome from this module ahead of the last winning draw ( one row at a time, to view the possible future draw pattern. )
To me, speed of computation is not an issue ( I’d wait all day, if I felt I was going to get closer if not right on! ).
I don’t wheel often, but if this works as I feel it will, I’d give it a go!

Now, I’m off to see what you and Icewynd have created!

Cheers.
Alex.

 

[PAB]

You're welcome Alex,

I tell ya though, I’d hate to run it on an old processor, slow bus speed & low RAM! I think I would still be waiting for it to finish.

I’ve a Duel Core and 2 Gig’s of Ram and only had Excel & VBE loaded and it still took 5 minutes to complete!

I can't understand that, I have a Duel Core running 1GB of Ram and it runs in 2:59 seconds, whereas yours takes 66% longer processing time?
This may of course be due to what you have running in the background in your msconfig: Startup, but that's another issue.
Anyway, I am glad it runs OK and produces the required results.

What would your thoughts be on that Module having the ability to do the same thing, “CSN, SORT, PRIME Number” and
include ability of the user to be able to pick 1, 2, or 3 numbers to use as “seed’s” for the generation of the CSN, ect. ?

My thoughts on this;
-It may give the ability to reduce the amount of combination (Note: I said MAY!)!
-In studying Lotto histories most of us can get a warm fuzzy feeling, for at least one number!
-In the other thread I started, I mentioned the DB being 1-49 to give basically a graphical representation, sometime in the future,
I’d like to plug in the outcome from this module ahead of the last winning draw ( one row at a time, to view the possible future draw pattern. )
To me, speed of computation is not an issue ( I’d wait all day, if I felt I was going to get closer if not right on! ).
I don’t wheel often, but if this works as I feel it will, I’d give it a go!

I assume that you are still talking about using Sum Total as the basis but with the added criteria of using 1, 2, or 3 numbers set in each combination?

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[Alexafjb]

Hi PAB

Yes, sums of the particulate number or numbers chosen.
So say if I want 160 as sum, and I choose numbers 4, 45 as an example.

Cheers.
Alex.

 

[PAB]

Hi Alex,

Please take a quick look at the other thread, it is available for one hour.
Please let me know in the other thread when you have downloaded it!

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[Alexafjb]

Hi PAB

can't understand that, I have a Duel Core running 1GB of Ram and it runs in 2:59 seconds, whereas yours takes 66% longer processing time?
This may of course be due to what you have running in the background in your msconfig: Startup, but that's another issue.
Anyway, I am glad it runs OK and produces the required results

Forgot to mention, I’m anal about security!

Forgot there was, antivirus, firewall, and the culprit was most likely the “Sand box” I play in!

It’s a force of habit. Too many Script Kiddies lose on the net, not to mention corrupt companies that love to worm in, and see if you can be exploited for cash!

Did I mention I’m Anal about security?

Cheers.
Alex.

 

[Alexafjb]

Hi PAB

OK I got it!

Cheers.
Alex.