|
Calculating Gaps Between Numbers
Hi everyone,
I am trying to create a "Gaps" program that cycles through ALL 13,983,816 combinations in a 6 from 49 Lotto and produces the following data. I would like it to work out the Gaps between each of the balls for EACH combination in a 6 from 49 Lotto with no replacement. For example, the combination 02 08 18 28 36 45 will have the Gaps of ... Ball 1 numbered 02 & Ball 2 numbered 08 = 05 Gaps ( 03 04 05 06 07 ) Ball 2 numbered 08 & Ball 3 numbered 18 = 09 Gaps ( 19 10 11 12 13 14 15 16 17 ) Ball 3 numbered 18 & Ball 4 numbered 28 = 08 Gaps ( 20 21 22 23 24 25 26 27 ) Ball 4 numbered 28 & Ball 5 numbered 36 = 07 Gaps ( 29 30 31 32 33 34 35 ) Ball 5 numbered 36 & Ball 6 numbered 45 = 08 Gaps ( 37 38 39 40 41 42 43 44 ) .... so the Gaps description for this particular combination will be 05 09 08 07 08. The Gaps difference between ball 1 and ball 2 is really ball 2 minus ball 1 - 1 and so on. I would like to have a Gaps description for each category and the Total combinations for each category associated with that list. So extracting three combinations from the Loop as an example ... Combination - 02 09 16 25 38 45 Combination - 04 15 19 36 37 49 Combination - 09 15 28 39 46 47 .... will give the answer ... Gaps 06 06 08 12 06 = x combinations ( x could be 925,365 combinations with the exact same Gaps between the balls 1-2, 2-3 ,3-4 ,4-5 & 5-6 ) Gaps 10 03 16 00 11 = x combinations ( x could be 236,258 combinations with the exact same Gaps between the balls 1-2, 2-3, 3-4, 4-5 & 5-6 ) Gaps 05 12 10 06 00 = x combinations ( x could be 756,258 combinations with the exact same Gaps between the balls 1-2, 2-3, 3-4, 4-5 & 5-6 ) .... etc. It would also be nice to have a Total at the bottom of the combinations list, which of course, should equal 13,983,816 combinations. I have written the following which has nothing to do with what I am trying to achieve above, but for some reason it does not work. It is supposed to give me a list of Gaps from 00 to 43 from ALL 13,983,816 combinations. The list of Gaps should be in cells B3:B45 and the total combinatios for each gap should be in cells C3:C45. Could somebody please have a glance at it. Option Explicit Option Base 1 Private Sub Gaps() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim GapsTotal(43) As Long Application.ScreenUpdating = False For i = 1 To 43 GapsTotal(i) = 0 Next i For A = 1 To 44 For B = A + 1 To 45 For C = B + 1 To 46 For D = C + 1 To 47 For E = D + 1 To 48 For F = E + 1 To 49 If B - A - 1 = 0 Then GapsTotal(0) = GapsTotal(0) + 1 If C - B - 1 = 0 Then GapsTotal(0) = GapsTotal(0) + 1 If D - C - 1 = 0 Then GapsTotal(0) = GapsTotal(0) + 1 If E - D - 1 = 0 Then GapsTotal(0) = GapsTotal(0) + 1 If F - E - 1 = 0 Then GapsTotal(0) = GapsTotal(0) + 1 If B - A - 1 = 1 Then GapsTotal(1) = GapsTotal(1) + 1 If C - B - 1 = 1 Then GapsTotal(1) = GapsTotal(1) + 1 If D - C - 1 = 1 Then GapsTotal(1) = GapsTotal(1) + 1 If E - D - 1 = 1 Then GapsTotal(1) = GapsTotal(1) + 1 If F - E - 1 = 1 Then GapsTotal(1) = GapsTotal(1) + 1 .... continued all the way down to ... If B - A - 1 = 42 Then GapsTotal(42) = GapsTotal(42) + 1 If C - B - 1 = 42 Then GapsTotal(42) = GapsTotal(42) + 1 If D - C - 1 = 42 Then GapsTotal(42) = GapsTotal(42) + 1 If E - D - 1 = 42 Then GapsTotal(42) = GapsTotal(42) + 1 If F - E - 1 = 42 Then GapsTotal(42) = GapsTotal(42) + 1 If B - A - 1 = 43 Then GapsTotal(43) = GapsTotal(43) + 1 If C - B - 1 = 43 Then GapsTotal(43) = GapsTotal(43) + 1 If D - C - 1 = 43 Then GapsTotal(43) = GapsTotal(43) + 1 If E - D - 1 = 43 Then GapsTotal(43) = GapsTotal(43) + 1 If F - E - 1 = 43 Then GapsTotal(43) = GapsTotal(43) + 1 Next F Next E Next D Next C Next B Next A Sheets("Gaps Data").Select Range("B2").Value = "Gaps" Range("B3").Value = "Gaps of 00" Range("B4").Value = "Gaps of 01" .... continued all the way down to ... Range("B44").Value = "Gaps of 42" Range("B45").Value = "Gaps of 43" Range("C2").Value = "Total" Range("C3").Select For i = 1 To 43 ActiveCell.Offset(i, 0).Value = GapsTotal(i) Next i Application.ScreenUpdating = True End Sub Any help will be greatly appreciated. Thanks in Advance. All the Best. Paul |
Calculating Gaps Between Numbers
I'm confused, and don't see how you get this:
Gaps 06 06 08 12 06 = x combinations ( x could be 925,365 combinations with the exact same Gaps between the balls 1-2, 2-3 ,3-4 ,4-5 & 5-6 ) Those 'gaps' can only be produced by 6 different combinations: 01 08 15 24 37 44 02 09 16 25 38 45 03 10 17 26 39 46 04 11 18 27 40 47 05 12 19 28 41 48 06 13 20 29 42 49 In general, the number of combinations for any gap combinations would be 49 - SUM(Gaps) - 5 For the example above, 49 - 38 - 5 = 6 combinations There will be lots of combinations of gaps that can only be produced by one combination. For example: Gaps 01 01 01 01 01 39 would be uniquely 01 03 05 07 09 49 HTH, Bernie MS Excel MVP "Paul Black" wrote in message oups.com... Hi everyone, I am trying to create a "Gaps" program that cycles through ALL 13,983,816 combinations in a 6 from 49 Lotto and produces the following data. I would like it to work out the Gaps between each of the balls for EACH combination in a 6 from 49 Lotto with no replacement. For example, the combination 02 08 18 28 36 45 will have the Gaps of ... Ball 1 numbered 02 & Ball 2 numbered 08 = 05 Gaps ( 03 04 05 06 07 ) Ball 2 numbered 08 & Ball 3 numbered 18 = 09 Gaps ( 19 10 11 12 13 14 15 16 17 ) Ball 3 numbered 18 & Ball 4 numbered 28 = 08 Gaps ( 20 21 22 23 24 25 26 27 ) Ball 4 numbered 28 & Ball 5 numbered 36 = 07 Gaps ( 29 30 31 32 33 34 35 ) Ball 5 numbered 36 & Ball 6 numbered 45 = 08 Gaps ( 37 38 39 40 41 42 43 44 ) ... so the Gaps description for this particular combination will be 05 09 08 07 08. The Gaps difference between ball 1 and ball 2 is really ball 2 minus ball 1 - 1 and so on. I would like to have a Gaps description for each category and the Total combinations for each category associated with that list. So extracting three combinations from the Loop as an example ... Combination - 02 09 16 25 38 45 Combination - 04 15 19 36 37 49 Combination - 09 15 28 39 46 47 ... will give the answer ... Gaps 06 06 08 12 06 = x combinations ( x could be 925,365 combinations with the exact same Gaps between the balls 1-2, 2-3 ,3-4 ,4-5 & 5-6 ) Gaps 10 03 16 00 11 = x combinations ( x could be 236,258 combinations with the exact same Gaps between the balls 1-2, 2-3, 3-4, 4-5 & 5-6 ) Gaps 05 12 10 06 00 = x combinations ( x could be 756,258 combinations with the exact same Gaps between the balls 1-2, 2-3, 3-4, 4-5 & 5-6 ) ... etc. It would also be nice to have a Total at the bottom of the combinations list, which of course, should equal 13,983,816 combinations. I have written the following which has nothing to do with what I am trying to achieve above, but for some reason it does not work. It is supposed to give me a list of Gaps from 00 to 43 from ALL 13,983,816 combinations. The list of Gaps should be in cells B3:B45 and the total combinatios for each gap should be in cells C3:C45. Could somebody please have a glance at it. Option Explicit Option Base 1 Private Sub Gaps() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim GapsTotal(43) As Long Application.ScreenUpdating = False For i = 1 To 43 GapsTotal(i) = 0 Next i For A = 1 To 44 For B = A + 1 To 45 For C = B + 1 To 46 For D = C + 1 To 47 For E = D + 1 To 48 For F = E + 1 To 49 If B - A - 1 = 0 Then GapsTotal(0) = GapsTotal(0) + 1 If C - B - 1 = 0 Then GapsTotal(0) = GapsTotal(0) + 1 If D - C - 1 = 0 Then GapsTotal(0) = GapsTotal(0) + 1 If E - D - 1 = 0 Then GapsTotal(0) = GapsTotal(0) + 1 If F - E - 1 = 0 Then GapsTotal(0) = GapsTotal(0) + 1 If B - A - 1 = 1 Then GapsTotal(1) = GapsTotal(1) + 1 If C - B - 1 = 1 Then GapsTotal(1) = GapsTotal(1) + 1 If D - C - 1 = 1 Then GapsTotal(1) = GapsTotal(1) + 1 If E - D - 1 = 1 Then GapsTotal(1) = GapsTotal(1) + 1 If F - E - 1 = 1 Then GapsTotal(1) = GapsTotal(1) + 1 ... continued all the way down to ... If B - A - 1 = 42 Then GapsTotal(42) = GapsTotal(42) + 1 If C - B - 1 = 42 Then GapsTotal(42) = GapsTotal(42) + 1 If D - C - 1 = 42 Then GapsTotal(42) = GapsTotal(42) + 1 If E - D - 1 = 42 Then GapsTotal(42) = GapsTotal(42) + 1 If F - E - 1 = 42 Then GapsTotal(42) = GapsTotal(42) + 1 If B - A - 1 = 43 Then GapsTotal(43) = GapsTotal(43) + 1 If C - B - 1 = 43 Then GapsTotal(43) = GapsTotal(43) + 1 If D - C - 1 = 43 Then GapsTotal(43) = GapsTotal(43) + 1 If E - D - 1 = 43 Then GapsTotal(43) = GapsTotal(43) + 1 If F - E - 1 = 43 Then GapsTotal(43) = GapsTotal(43) + 1 Next F Next E Next D Next C Next B Next A Sheets("Gaps Data").Select Range("B2").Value = "Gaps" Range("B3").Value = "Gaps of 00" Range("B4").Value = "Gaps of 01" ... continued all the way down to ... Range("B44").Value = "Gaps of 42" Range("B45").Value = "Gaps of 43" Range("C2").Value = "Total" Range("C3").Select For i = 1 To 43 ActiveCell.Offset(i, 0).Value = GapsTotal(i) Next i Application.ScreenUpdating = True End Sub Any help will be greatly appreciated. Thanks in Advance. All the Best. Paul |
Calculating Gaps Between Numbers
Bernie:
01 08 15 24 37 44 the numbers with X's represent the GAPS the number of numbers in the GAPS make up the pattern 06 06 08 12 06. You could repeat this pattern a total of 6 times until you ran out of numbers. 1 2x 3x 4x 6 5x 6x 7x 8 9x 10x 11x 12x 6 13x 14x 15 16x 17x 18x 19x 20x 8 21x 22x 23x 24 25x 26x 27x 28x 29x 30x 12 31x 32x 33x 34x 35x 36x 37 38x 39x 40x 6 41x 42x 43x 44 45 46 47 48 49 -- Regards, Tom Ogilvy "Bernie Deitrick" wrote: I'm confused, and don't see how you get this: Gaps 06 06 08 12 06 = x combinations ( x could be 925,365 combinations with the exact same Gaps between the balls 1-2, 2-3 ,3-4 ,4-5 & 5-6 ) Those 'gaps' can only be produced by 6 different combinations: 01 08 15 24 37 44 02 09 16 25 38 45 03 10 17 26 39 46 04 11 18 27 40 47 05 12 19 28 41 48 06 13 20 29 42 49 In general, the number of combinations for any gap combinations would be 49 - SUM(Gaps) - 5 For the example above, 49 - 38 - 5 = 6 combinations There will be lots of combinations of gaps that can only be produced by one combination. For example: Gaps 01 01 01 01 01 39 would be uniquely 01 03 05 07 09 49 HTH, Bernie MS Excel MVP "Paul Black" wrote in message oups.com... Hi everyone, I am trying to create a "Gaps" program that cycles through ALL 13,983,816 combinations in a 6 from 49 Lotto and produces the following data. I would like it to work out the Gaps between each of the balls for EACH combination in a 6 from 49 Lotto with no replacement. For example, the combination 02 08 18 28 36 45 will have the Gaps of ... Ball 1 numbered 02 & Ball 2 numbered 08 = 05 Gaps ( 03 04 05 06 07 ) Ball 2 numbered 08 & Ball 3 numbered 18 = 09 Gaps ( 19 10 11 12 13 14 15 16 17 ) Ball 3 numbered 18 & Ball 4 numbered 28 = 08 Gaps ( 20 21 22 23 24 25 26 27 ) Ball 4 numbered 28 & Ball 5 numbered 36 = 07 Gaps ( 29 30 31 32 33 34 35 ) Ball 5 numbered 36 & Ball 6 numbered 45 = 08 Gaps ( 37 38 39 40 41 42 43 44 ) ... so the Gaps description for this particular combination will be 05 09 08 07 08. The Gaps difference between ball 1 and ball 2 is really ball 2 minus ball 1 - 1 and so on. I would like to have a Gaps description for each category and the Total combinations for each category associated with that list. So extracting three combinations from the Loop as an example ... Combination - 02 09 16 25 38 45 Combination - 04 15 19 36 37 49 Combination - 09 15 28 39 46 47 ... will give the answer ... Gaps 06 06 08 12 06 = x combinations ( x could be 925,365 combinations with the exact same Gaps between the balls 1-2, 2-3 ,3-4 ,4-5 & 5-6 ) Gaps 10 03 16 00 11 = x combinations ( x could be 236,258 combinations with the exact same Gaps between the balls 1-2, 2-3, 3-4, 4-5 & 5-6 ) Gaps 05 12 10 06 00 = x combinations ( x could be 756,258 combinations with the exact same Gaps between the balls 1-2, 2-3, 3-4, 4-5 & 5-6 ) ... etc. It would also be nice to have a Total at the bottom of the combinations list, which of course, should equal 13,983,816 combinations. I have written the following which has nothing to do with what I am trying to achieve above, but for some reason it does not work. It is supposed to give me a list of Gaps from 00 to 43 from ALL 13,983,816 combinations. The list of Gaps should be in cells B3:B45 and the total combinatios for each gap should be in cells C3:C45. Could somebody please have a glance at it. Option Explicit Option Base 1 Private Sub Gaps() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim GapsTotal(43) As Long Application.ScreenUpdating = False For i = 1 To 43 GapsTotal(i) = 0 Next i For A = 1 To 44 For B = A + 1 To 45 For C = B + 1 To 46 For D = C + 1 To 47 For E = D + 1 To 48 For F = E + 1 To 49 If B - A - 1 = 0 Then GapsTotal(0) = GapsTotal(0) + 1 If C - B - 1 = 0 Then GapsTotal(0) = GapsTotal(0) + 1 If D - C - 1 = 0 Then GapsTotal(0) = GapsTotal(0) + 1 If E - D - 1 = 0 Then GapsTotal(0) = GapsTotal(0) + 1 If F - E - 1 = 0 Then GapsTotal(0) = GapsTotal(0) + 1 If B - A - 1 = 1 Then GapsTotal(1) = GapsTotal(1) + 1 If C - B - 1 = 1 Then GapsTotal(1) = GapsTotal(1) + 1 If D - C - 1 = 1 Then GapsTotal(1) = GapsTotal(1) + 1 If E - D - 1 = 1 Then GapsTotal(1) = GapsTotal(1) + 1 If F - E - 1 = 1 Then GapsTotal(1) = GapsTotal(1) + 1 ... continued all the way down to ... If B - A - 1 = 42 Then GapsTotal(42) = GapsTotal(42) + 1 If C - B - 1 = 42 Then GapsTotal(42) = GapsTotal(42) + 1 If D - C - 1 = 42 Then GapsTotal(42) = GapsTotal(42) + 1 If E - D - 1 = 42 Then GapsTotal(42) = GapsTotal(42) + 1 If F - E - 1 = 42 Then GapsTotal(42) = GapsTotal(42) + 1 If B - A - 1 = 43 Then GapsTotal(43) = GapsTotal(43) + 1 If C - B - 1 = 43 Then GapsTotal(43) = GapsTotal(43) + 1 If D - C - 1 = 43 Then GapsTotal(43) = GapsTotal(43) + 1 If E - D - 1 = 43 Then GapsTotal(43) = GapsTotal(43) + 1 If F - E - 1 = 43 Then GapsTotal(43) = GapsTotal(43) + 1 Next F Next E Next D Next C Next B Next A Sheets("Gaps Data").Select Range("B2").Value = "Gaps" Range("B3").Value = "Gaps of 00" Range("B4").Value = "Gaps of 01" ... continued all the way down to ... Range("B44").Value = "Gaps of 42" Range("B45").Value = "Gaps of 43" Range("C2").Value = "Total" Range("C3").Select For i = 1 To 43 ActiveCell.Offset(i, 0).Value = GapsTotal(i) Next i Application.ScreenUpdating = True End Sub Any help will be greatly appreciated. Thanks in Advance. All the Best. Paul |
Calculating Gaps Between Numbers
Tom, You could repeat this pattern a total of 6 times until you ran out of numbers. I think we're in agreement: Those 'gaps' can only be produced by 6 different combinations: 01 08 15 24 37 44 02 09 16 25 38 45 03 10 17 26 39 46 04 11 18 27 40 47 05 12 19 28 41 48 06 13 20 29 42 49 But I have no idea how the OP got 925,365 combinations: Gaps 06 06 08 12 06 = x combinations ( x could be 925,365 combinations with the exact same Gaps between the balls 1-2, 2-3 ,3-4 ,4-5 & 5-6 ) Bernie |
Calculating Gaps Between Numbers
Thanks for the reply Bernie,
The Gaps 06 06 08 12 06 = x combinations ( x could be 925,365 combinations with the exact same Gaps between the balls 1 & 2, and 2 & 3, and 3 & 4, and 4 & 5, and 5 & 6 ) is only an example figure, I have no idea what the actual figure is, or come to it, how to code it. There can only be 5 criteria for each combination. These are the gaps between balls ... 1 and 2 2 and 3 3 and 4 4 and 5 5 and 6 .... obviously with ALL 13,983,816 combinations there will be quite a few categories. The Gaps are produced for each category using all the 6 numbers within each combination. Begin Quote :- Gaps 01 01 01 01 01 39 ( how did you get the 6 sets of numbers? ) ... .... would be uniquely 01 03 05 07 09 49 End Quote. I don't quite understand it when you say ... Begin Quote :- Gaps 06 06 08 12 06 = x combinations ( x could be 925,365 combinations with the exact same Gaps between the balls 1-2, 2-3 ,3-4 ,4-5 & 5-6 ) Those 'Gaps' can only be produced by 6 different combinations :- 01 08 15 24 37 44 02 09 16 25 38 45 03 10 17 26 39 46 04 11 18 27 40 47 05 12 19 28 41 48 06 13 20 29 42 49 In general, the number of combinations for any gap combinations would be 49 - SUM(Gaps) - 5 For the example above, 49 - 38 - 5 = 6 combinations. There will be lots of combinations of Gaps that can only be produced by one combination. End Quote. Thanks again. All the Best. Paul |
Calculating Gaps Between Numbers
Paul,
Try the macro below HTH, Bernie MS Excel MVP Private Sub Gaps2() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim GapsTotal(0 To 43) As Long Application.ScreenUpdating = False For i = 0 To 43 GapsTotal(i) = 0 Next i For A = 1 To 44 For B = A + 1 To 45 For C = B + 1 To 46 For D = C + 1 To 47 For E = D + 1 To 48 For F = E + 1 To 49 GapsTotal(B - A - 1) = GapsTotal(B - A - 1) + 1 GapsTotal(C - B - 1) = GapsTotal(C - B - 1) + 1 GapsTotal(D - C - 1) = GapsTotal(D - C - 1) + 1 GapsTotal(E - D - 1) = GapsTotal(E - D - 1) + 1 GapsTotal(F - E - 1) = GapsTotal(F - E - 1) + 1 Next F Next E Next D Next C Next B Next A Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" For i = 0 To 43 Sheets("Gaps Data").Cells(i + 3, 2).Value = "Gaps of " & Format(i, "00") Sheets("Gaps Data").Cells(i + 3, 3).Value = GapsTotal(i) Next i Application.ScreenUpdating = True End Sub "Paul Black" wrote in message oups.com... Thanks for the reply Bernie, The Gaps 06 06 08 12 06 = x combinations ( x could be 925,365 combinations with the exact same Gaps between the balls 1 & 2, and 2 & 3, and 3 & 4, and 4 & 5, and 5 & 6 ) is only an example figure, I have no idea what the actual figure is, or come to it, how to code it. There can only be 5 criteria for each combination. These are the gaps between balls ... 1 and 2 2 and 3 3 and 4 4 and 5 5 and 6 ... obviously with ALL 13,983,816 combinations there will be quite a few categories. The Gaps are produced for each category using all the 6 numbers within each combination. Begin Quote :- Gaps 01 01 01 01 01 39 ( how did you get the 6 sets of numbers? ) ... ... would be uniquely 01 03 05 07 09 49 End Quote. I don't quite understand it when you say ... Begin Quote :- Gaps 06 06 08 12 06 = x combinations ( x could be 925,365 combinations with the exact same Gaps between the balls 1-2, 2-3 ,3-4 ,4-5 & 5-6 ) Those 'Gaps' can only be produced by 6 different combinations :- 01 08 15 24 37 44 02 09 16 25 38 45 03 10 17 26 39 46 04 11 18 27 40 47 05 12 19 28 41 48 06 13 20 29 42 49 In general, the number of combinations for any gap combinations would be 49 - SUM(Gaps) - 5 For the example above, 49 - 38 - 5 = 6 combinations. There will be lots of combinations of Gaps that can only be produced by one combination. End Quote. Thanks again. All the Best. Paul |
Calculating Gaps Between Numbers
Whoops!
mistook your explantion for part of your question <face red. Have to confess I didn't read the original post so thought it was all a quote. -- Regards, Tom Ogilvy "Bernie Deitrick" wrote: Tom, You could repeat this pattern a total of 6 times until you ran out of numbers. I think we're in agreement: Those 'gaps' can only be produced by 6 different combinations: 01 08 15 24 37 44 02 09 16 25 38 45 03 10 17 26 39 46 04 11 18 27 40 47 05 12 19 28 41 48 06 13 20 29 42 49 But I have no idea how the OP got 925,365 combinations: Gaps 06 06 08 12 06 = x combinations ( x could be 925,365 combinations with the exact same Gaps between the balls 1-2, 2-3 ,3-4 ,4-5 & 5-6 ) Bernie |
Calculating Gaps Between Numbers
Thanks for the code Bernie.
I don't have access to Excel at the moment so am unable to test the code. Does the code apply to my original request or to my Private Sub Gaps() effort please. I assume it applies to my effort. It is my original request that is the most important one. Thanks in Advance. All the Best. Paul On Jul 10, 5:04 pm, Tom Ogilvy wrote: Whoops! mistook your explantion for part of your question <face red. Have to confess I didn't read the original post so thought it was all a quote. -- Regards, Tom Ogilvy "Bernie Deitrick" wrote: Tom, You could repeat this pattern a total of 6 times until you ran out of numbers. I think we're in agreement: Those 'gaps' can only be produced by 6 different combinations: 01 08 15 24 37 44 02 09 16 25 38 45 03 10 17 26 39 46 04 11 18 27 40 47 05 12 19 28 41 48 06 13 20 29 42 49 But I have no idea how the OP got 925,365 combinations: Gaps 06 06 08 12 06 = x combinations ( x could be 925,365 combinations with the exact same Gaps between the balls 1-2, 2-3 ,3-4 ,4-5 & 5-6 ) Bernie- Hide quoted text - - Show quoted text - |
Calculating Gaps Between Numbers
Paul,
The macro code is a working version of the macro that you requested, greatly simplified by using variable indices. HTH, Bernie MS Excel MVP "Paul Black" wrote in message ups.com... Thanks for the code Bernie. I don't have access to Excel at the moment so am unable to test the code. Does the code apply to my original request or to my Private Sub Gaps() effort please. I assume it applies to my effort. It is my original request that is the most important one. Thanks in Advance. All the Best. Paul On Jul 10, 5:04 pm, Tom Ogilvy wrote: Whoops! mistook your explantion for part of your question <face red. Have to confess I didn't read the original post so thought it was all a quote. -- Regards, Tom Ogilvy "Bernie Deitrick" wrote: Tom, You could repeat this pattern a total of 6 times until you ran out of numbers. I think we're in agreement: Those 'gaps' can only be produced by 6 different combinations: 01 08 15 24 37 44 02 09 16 25 38 45 03 10 17 26 39 46 04 11 18 27 40 47 05 12 19 28 41 48 06 13 20 29 42 49 But I have no idea how the OP got 925,365 combinations: Gaps 06 06 08 12 06 = x combinations ( x could be 925,365 combinations with the exact same Gaps between the balls 1-2, 2-3 ,3-4 ,4-5 & 5-6 ) Bernie- Hide quoted text - - Show quoted text - |
Calculating Gaps Between Numbers
I ran it, here are the results:
Gaps Gaps of 00 8,561,520 Gaps of 01 7,669,695 Gaps of 02 6,853,770 Gaps of 03 6,108,795 Gaps of 04 5,430,040 Gaps of 05 4,812,990 Gaps of 06 4,253,340 Gaps of 07 3,746,990 Gaps of 08 3,290,040 Gaps of 09 2,878,785 Gaps of 10 2,509,710 Gaps of 11 2,179,485 Gaps of 12 1,884,960 Gaps of 13 1,623,160 Gaps of 14 1,391,280 Gaps of 15 1,186,680 Gaps of 16 1,006,880 Gaps of 17 849,555 Gaps of 18 712,530 Gaps of 19 593,775 Gaps of 20 491,400 Gaps of 21 403,650 Gaps of 22 328,900 Gaps of 23 265,650 Gaps of 24 212,520 Gaps of 25 168,245 Gaps of 26 131,670 Gaps of 27 101,745 Gaps of 28 77,520 Gaps of 29 58,140 Gaps of 30 42,840 Gaps of 31 30,940 Gaps of 32 21,840 Gaps of 33 15,015 Gaps of 34 10,010 Gaps of 35 6,435 Gaps of 36 3,960 Gaps of 37 2,310 Gaps of 38 1,260 Gaps of 39 630 Gaps of 40 280 Gaps of 41 105 Gaps of 42 30 Gaps of 43 5 69,919,080 -- Regards, Tom Ogilvy "Paul Black" wrote: Thanks for the code Bernie. I don't have access to Excel at the moment so am unable to test the code. Does the code apply to my original request or to my Private Sub Gaps() effort please. I assume it applies to my effort. It is my original request that is the most important one. Thanks in Advance. All the Best. Paul On Jul 10, 5:04 pm, Tom Ogilvy wrote: Whoops! mistook your explantion for part of your question <face red. Have to confess I didn't read the original post so thought it was all a quote. -- Regards, Tom Ogilvy "Bernie Deitrick" wrote: Tom, You could repeat this pattern a total of 6 times until you ran out of numbers. I think we're in agreement: Those 'gaps' can only be produced by 6 different combinations: 01 08 15 24 37 44 02 09 16 25 38 45 03 10 17 26 39 46 04 11 18 27 40 47 05 12 19 28 41 48 06 13 20 29 42 49 But I have no idea how the OP got 925,365 combinations: Gaps 06 06 08 12 06 = x combinations ( x could be 925,365 combinations with the exact same Gaps between the balls 1-2, 2-3 ,3-4 ,4-5 & 5-6 ) Bernie- Hide quoted text - - Show quoted text - |
Calculating Gaps Between Numbers
Thanks Bernie for producing the code & thanks Tom for running it and
listing the information for me, it is greatly appreciated. Have either of you had any thoughts on achieving my main request at all please, that is the part at the beginning of my initial post and goes down to and includes the paragraph ... It would also be nice to have a Total at the bottom of the combinations list, which of course, should equal 13,983,816 combinations. Thanks in Advance. All the Best. Paul On Jul 10, 6:24 pm, Tom Ogilvy wrote: I ran it, here are the results: Gaps Gaps of 00 8,561,520 Gaps of 01 7,669,695 Gaps of 02 6,853,770 Gaps of 03 6,108,795 Gaps of 04 5,430,040 Gaps of 05 4,812,990 Gaps of 06 4,253,340 Gaps of 07 3,746,990 Gaps of 08 3,290,040 Gaps of 09 2,878,785 Gaps of 10 2,509,710 Gaps of 11 2,179,485 Gaps of 12 1,884,960 Gaps of 13 1,623,160 Gaps of 14 1,391,280 Gaps of 15 1,186,680 Gaps of 16 1,006,880 Gaps of 17 849,555 Gaps of 18 712,530 Gaps of 19 593,775 Gaps of 20 491,400 Gaps of 21 403,650 Gaps of 22 328,900 Gaps of 23 265,650 Gaps of 24 212,520 Gaps of 25 168,245 Gaps of 26 131,670 Gaps of 27 101,745 Gaps of 28 77,520 Gaps of 29 58,140 Gaps of 30 42,840 Gaps of 31 30,940 Gaps of 32 21,840 Gaps of 33 15,015 Gaps of 34 10,010 Gaps of 35 6,435 Gaps of 36 3,960 Gaps of 37 2,310 Gaps of 38 1,260 Gaps of 39 630 Gaps of 40 280 Gaps of 41 105 Gaps of 42 30 Gaps of 43 5 69,919,080 -- Regards, Tom Ogilvy "Paul Black" wrote: Thanks for the code Bernie. I don't have access to Excel at the moment so am unable to test the code. Does the code apply to my original request or to my Private Sub Gaps() effort please. I assume it applies to my effort. It is my original request that is the most important one. Thanks in Advance. All the Best. Paul On Jul 10, 5:04 pm, Tom Ogilvy wrote: Whoops! mistook your explantion for part of your question <face red. Have to confess I didn't read the original post so thought it was all a quote. -- Regards, Tom Ogilvy "Bernie Deitrick" wrote: Tom, You could repeat this pattern a total of 6 times until you ran out of numbers. I think we're in agreement: Those 'gaps' can only be produced by 6 different combinations: 01 08 15 24 37 44 02 09 16 25 38 45 03 10 17 26 39 46 04 11 18 27 40 47 05 12 19 28 41 48 06 13 20 29 42 49 But I have no idea how the OP got 925,365 combinations: Gaps 06 06 08 12 06 = x combinations ( x could be 925,365 combinations with the exact same Gaps between the balls 1-2, 2-3 ,3-4 ,4-5 & 5-6 ) Bernie- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Calculating Gaps Between Numbers
Paul,
Gaps of 43 5 69,919,080 69,919,080 / 5 =13,983,816 Bernie "Paul Black" wrote in message ps.com... Thanks Bernie for producing the code & thanks Tom for running it and listing the information for me, it is greatly appreciated. Have either of you had any thoughts on achieving my main request at all please, that is the part at the beginning of my initial post and goes down to and includes the paragraph ... It would also be nice to have a Total at the bottom of the combinations list, which of course, should equal 13,983,816 combinations. Thanks in Advance. All the Best. Paul On Jul 10, 6:24 pm, Tom Ogilvy wrote: I ran it, here are the results: Gaps Gaps of 00 8,561,520 Gaps of 01 7,669,695 Gaps of 02 6,853,770 Gaps of 03 6,108,795 Gaps of 04 5,430,040 Gaps of 05 4,812,990 Gaps of 06 4,253,340 Gaps of 07 3,746,990 Gaps of 08 3,290,040 Gaps of 09 2,878,785 Gaps of 10 2,509,710 Gaps of 11 2,179,485 Gaps of 12 1,884,960 Gaps of 13 1,623,160 Gaps of 14 1,391,280 Gaps of 15 1,186,680 Gaps of 16 1,006,880 Gaps of 17 849,555 Gaps of 18 712,530 Gaps of 19 593,775 Gaps of 20 491,400 Gaps of 21 403,650 Gaps of 22 328,900 Gaps of 23 265,650 Gaps of 24 212,520 Gaps of 25 168,245 Gaps of 26 131,670 Gaps of 27 101,745 Gaps of 28 77,520 Gaps of 29 58,140 Gaps of 30 42,840 Gaps of 31 30,940 Gaps of 32 21,840 Gaps of 33 15,015 Gaps of 34 10,010 Gaps of 35 6,435 Gaps of 36 3,960 Gaps of 37 2,310 Gaps of 38 1,260 Gaps of 39 630 Gaps of 40 280 Gaps of 41 105 Gaps of 42 30 Gaps of 43 5 69,919,080 -- Regards, Tom Ogilvy "Paul Black" wrote: Thanks for the code Bernie. I don't have access to Excel at the moment so am unable to test the code. Does the code apply to my original request or to my Private Sub Gaps() effort please. I assume it applies to my effort. It is my original request that is the most important one. Thanks in Advance. All the Best. Paul On Jul 10, 5:04 pm, Tom Ogilvy wrote: Whoops! mistook your explantion for part of your question <face red. Have to confess I didn't read the original post so thought it was all a quote. -- Regards, Tom Ogilvy "Bernie Deitrick" wrote: Tom, You could repeat this pattern a total of 6 times until you ran out of numbers. I think we're in agreement: Those 'gaps' can only be produced by 6 different combinations: 01 08 15 24 37 44 02 09 16 25 38 45 03 10 17 26 39 46 04 11 18 27 40 47 05 12 19 28 41 48 06 13 20 29 42 49 But I have no idea how the OP got 925,365 combinations: Gaps 06 06 08 12 06 = x combinations ( x could be 925,365 combinations with the exact same Gaps between the balls 1-2, 2-3 ,3-4 ,4-5 & 5-6 ) Bernie- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Calculating Gaps Between Numbers
Hi Bernie,
I think the confusion is me not being able to explain myself properly. I would like a list of the total combinations for each category of Gaps calculated for each combination. For example, taking the combinations below in a 6 from 49 Lotto as ... Combination 01 02 03 04 05 06 = Gap category 00 00 00 00 00 Combination 01 02 03 04 05 07 = Gap category 00 00 00 00 01 Combination 01 02 03 04 05 08 = Gap category 00 00 00 00 02 Combination 01 02 03 04 05 09 = Gap category 00 00 00 00 03 Combination 01 02 03 04 05 10 = Gap category 00 00 00 00 04 Combination 01 02 03 04 05 11 = Gap category 00 00 00 00 05 Combination 01 02 03 04 05 12 = Gap category 00 00 00 00 06 Combination 01 11 12 14 18 21 = Gap category 09 00 01 03 02 Combination 11 21 22 24 28 31 = Gap category 09 00 01 03 02 Combination 21 22 23 24 25 31 = Gap category 00 00 00 00 05 .... the category list and total combinations for the above will be ... Category 00 00 00 00 00 = total combinations of 1 ( One ) Category 00 00 00 00 01 = total combinations of 1 ( One ) Category 00 00 00 00 02 = total combinations of 3 ( Three ) Category 00 00 00 00 03 = total combinations of 1 ( One ) Category 00 00 00 00 04 = total combinations of 1 ( One ) Category 00 00 00 00 05 = total combinations of 2 ( Two ) Category 00 00 00 00 06 = total combinations of 1 ( One ) I would also like the total combinations at the end please, which of course should be 13,983,816 combinations. I hope this makes it a bit clearer. Thanks in Advance. All the Best. Paul On Jul 10, 9:50 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, Gaps of 43 5 69,919,080 69,919,080 / 5 =13,983,816 Bernie "Paul Black" wrote in message ps.com... Thanks Bernie for producing the code & thanks Tom for running it and listing the information for me, it is greatly appreciated. Have either of you had any thoughts on achieving my main request at all please, that is the part at the beginning of my initial post and goes down to and includes the paragraph ... It would also be nice to have a Total at the bottom of the combinations list, which of course, should equal 13,983,816 combinations. Thanks in Advance. All the Best. Paul On Jul 10, 6:24 pm, Tom Ogilvy wrote: I ran it, here are the results: Gaps Gaps of 00 8,561,520 Gaps of 01 7,669,695 Gaps of 02 6,853,770 Gaps of 03 6,108,795 Gaps of 04 5,430,040 Gaps of 05 4,812,990 Gaps of 06 4,253,340 Gaps of 07 3,746,990 Gaps of 08 3,290,040 Gaps of 09 2,878,785 Gaps of 10 2,509,710 Gaps of 11 2,179,485 Gaps of 12 1,884,960 Gaps of 13 1,623,160 Gaps of 14 1,391,280 Gaps of 15 1,186,680 Gaps of 16 1,006,880 Gaps of 17 849,555 Gaps of 18 712,530 Gaps of 19 593,775 Gaps of 20 491,400 Gaps of 21 403,650 Gaps of 22 328,900 Gaps of 23 265,650 Gaps of 24 212,520 Gaps of 25 168,245 Gaps of 26 131,670 Gaps of 27 101,745 Gaps of 28 77,520 Gaps of 29 58,140 Gaps of 30 42,840 Gaps of 31 30,940 Gaps of 32 21,840 Gaps of 33 15,015 Gaps of 34 10,010 Gaps of 35 6,435 Gaps of 36 3,960 Gaps of 37 2,310 Gaps of 38 1,260 Gaps of 39 630 Gaps of 40 280 Gaps of 41 105 Gaps of 42 30 Gaps of 43 5 69,919,080 -- Regards, Tom Ogilvy "Paul Black" wrote: Thanks for the code Bernie. I don't have access to Excel at the moment so am unable to test the code. Does the code apply to my original request or to my Private Sub Gaps() effort please. I assume it applies to my effort. It is my original request that is the most important one. Thanks in Advance. All the Best. Paul On Jul 10, 5:04 pm, Tom Ogilvy wrote: Whoops! mistook your explantion for part of your question <face red. Have to confess I didn't read the original post so thought it was all a quote. -- Regards, Tom Ogilvy "Bernie Deitrick" wrote: Tom, You could repeat this pattern a total of 6 times until you ran out of numbers. I think we're in agreement: Those 'gaps' can only be produced by 6 different combinations: 01 08 15 24 37 44 02 09 16 25 38 45 03 10 17 26 39 46 04 11 18 27 40 47 05 12 19 28 41 48 06 13 20 29 42 49 But I have no idea how the OP got 925,365 combinations: Gaps 06 06 08 12 06 = x combinations ( x could be 925,365 combinations with the exact same Gaps between the balls 1-2, 2-3 ,3-4 ,4-5 & 5-6 ) Bernie- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Calculating Gaps Between Numbers
Paul,
Try the macro below.... but try it first with a lower mySize number (see the comment). It may require a supercomputer to finish, or hours and hours... 13,983,816 combinations is a lot to process. Bernie Private Sub Gaps3() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False myCol = 1 myRow = 1 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents For A = 1 To mySize - 5 'If A < 1 Then MsgBox "Finished " & A - 1 & " out of " & mySize - 5 For B = A + 1 To mySize - 4 For C = B + 1 To mySize - 3 For D = C + 1 To mySize - 2 For E = D + 1 To mySize - 1 For F = E + 1 To mySize strGap = Format(B - A - 1, "00") & " " & _ Format(C - B - 1, "00") & " " & _ Format(D - C - 1, "00") & " " & _ Format(E - D - 1, "00") & " " & _ Format(F - E - 1, "00") Set myCell = Cells.Find(strGap, , , xlPart) If myCell Is Nothing Then Cells(myRow, myCol).Value = strGap & " Count = 1" myRow = myRow + 1 If myRow = Rows.Count + 1 Then myRow = 1 myCol = myCol + 1 End If Else myCell.Value = strGap & " Count = " & Format(Val(Trim( _ Replace(myCell.Value, strGap & " Count = ", ""))) + 1, "0") End If Next F Next E Next D Next C Next B Next A Application.ScreenUpdating = True End Sub "Paul Black" wrote in message ups.com... Hi Bernie, I think the confusion is me not being able to explain myself properly. I would like a list of the total combinations for each category of Gaps calculated for each combination. For example, taking the combinations below in a 6 from 49 Lotto as ... Combination 01 02 03 04 05 06 = Gap category 00 00 00 00 00 Combination 01 02 03 04 05 07 = Gap category 00 00 00 00 01 Combination 01 02 03 04 05 08 = Gap category 00 00 00 00 02 Combination 01 02 03 04 05 09 = Gap category 00 00 00 00 03 Combination 01 02 03 04 05 10 = Gap category 00 00 00 00 04 Combination 01 02 03 04 05 11 = Gap category 00 00 00 00 05 Combination 01 02 03 04 05 12 = Gap category 00 00 00 00 06 Combination 01 11 12 14 18 21 = Gap category 09 00 01 03 02 Combination 11 21 22 24 28 31 = Gap category 09 00 01 03 02 Combination 21 22 23 24 25 31 = Gap category 00 00 00 00 05 ... the category list and total combinations for the above will be ... Category 00 00 00 00 00 = total combinations of 1 ( One ) Category 00 00 00 00 01 = total combinations of 1 ( One ) Category 00 00 00 00 02 = total combinations of 3 ( Three ) Category 00 00 00 00 03 = total combinations of 1 ( One ) Category 00 00 00 00 04 = total combinations of 1 ( One ) Category 00 00 00 00 05 = total combinations of 2 ( Two ) Category 00 00 00 00 06 = total combinations of 1 ( One ) I would also like the total combinations at the end please, which of course should be 13,983,816 combinations. I hope this makes it a bit clearer. Thanks in Advance. All the Best. Paul On Jul 10, 9:50 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, Gaps of 43 5 69,919,080 69,919,080 / 5 =13,983,816 Bernie "Paul Black" wrote in message ps.com... Thanks Bernie for producing the code & thanks Tom for running it and listing the information for me, it is greatly appreciated. Have either of you had any thoughts on achieving my main request at all please, that is the part at the beginning of my initial post and goes down to and includes the paragraph ... It would also be nice to have a Total at the bottom of the combinations list, which of course, should equal 13,983,816 combinations. Thanks in Advance. All the Best. Paul On Jul 10, 6:24 pm, Tom Ogilvy wrote: I ran it, here are the results: Gaps Gaps of 00 8,561,520 Gaps of 01 7,669,695 Gaps of 02 6,853,770 Gaps of 03 6,108,795 Gaps of 04 5,430,040 Gaps of 05 4,812,990 Gaps of 06 4,253,340 Gaps of 07 3,746,990 Gaps of 08 3,290,040 Gaps of 09 2,878,785 Gaps of 10 2,509,710 Gaps of 11 2,179,485 Gaps of 12 1,884,960 Gaps of 13 1,623,160 Gaps of 14 1,391,280 Gaps of 15 1,186,680 Gaps of 16 1,006,880 Gaps of 17 849,555 Gaps of 18 712,530 Gaps of 19 593,775 Gaps of 20 491,400 Gaps of 21 403,650 Gaps of 22 328,900 Gaps of 23 265,650 Gaps of 24 212,520 Gaps of 25 168,245 Gaps of 26 131,670 Gaps of 27 101,745 Gaps of 28 77,520 Gaps of 29 58,140 Gaps of 30 42,840 Gaps of 31 30,940 Gaps of 32 21,840 Gaps of 33 15,015 Gaps of 34 10,010 Gaps of 35 6,435 Gaps of 36 3,960 Gaps of 37 2,310 Gaps of 38 1,260 Gaps of 39 630 Gaps of 40 280 Gaps of 41 105 Gaps of 42 30 Gaps of 43 5 69,919,080 -- Regards, Tom Ogilvy "Paul Black" wrote: Thanks for the code Bernie. I don't have access to Excel at the moment so am unable to test the code. Does the code apply to my original request or to my Private Sub Gaps() effort please. I assume it applies to my effort. It is my original request that is the most important one. Thanks in Advance. All the Best. Paul On Jul 10, 5:04 pm, Tom Ogilvy wrote: Whoops! mistook your explantion for part of your question <face red. Have to confess I didn't read the original post so thought it was all a quote. -- Regards, Tom Ogilvy "Bernie Deitrick" wrote: Tom, You could repeat this pattern a total of 6 times until you ran out of numbers. I think we're in agreement: Those 'gaps' can only be produced by 6 different combinations: 01 08 15 24 37 44 02 09 16 25 38 45 03 10 17 26 39 46 04 11 18 27 40 47 05 12 19 28 41 48 06 13 20 29 42 49 But I have no idea how the OP got 925,365 combinations: Gaps 06 06 08 12 06 = x combinations ( x could be 925,365 combinations with the exact same Gaps between the balls 1-2, 2-3 ,3-4 ,4-5 & 5-6 ) Bernie- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Calculating Gaps Between Numbers
Here's a better macro, with fewer iterations - but still LOTS...
Bernie Private Sub Gaps4() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False ' myCol = 1 myRow = 1 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents For A = 0 To mySize - 5 ' If A < 0 Then MsgBox "Finished " & A & " out of " & mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B For D = 0 To mySize - 5 - C For E = 0 To mySize - 5 - D If mySize - 5 - A - B - C - D - E 0 Then strGap = Format(A, "00") & " " & _ Format(B, "00") & " " & _ Format(C, "00") & " " & _ Format(D, "00") & " " & _ Format(E, "00") & " Count = " & Format(mySize - 5 - A - B - C - D - E, "0") Cells(myRow, myCol).Value = strGap myRow = myRow + 1 If myRow = Rows.Count + 1 Then myRow = 1 myCol = myCol + 1 End If End If Next E Next D Next C Next B Next A Application.ScreenUpdating = True End Sub "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Paul, Try the macro below.... but try it first with a lower mySize number (see the comment). It may require a supercomputer to finish, or hours and hours... 13,983,816 combinations is a lot to process. Bernie Private Sub Gaps3() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False myCol = 1 myRow = 1 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents For A = 1 To mySize - 5 'If A < 1 Then MsgBox "Finished " & A - 1 & " out of " & mySize - 5 For B = A + 1 To mySize - 4 For C = B + 1 To mySize - 3 For D = C + 1 To mySize - 2 For E = D + 1 To mySize - 1 For F = E + 1 To mySize strGap = Format(B - A - 1, "00") & " " & _ Format(C - B - 1, "00") & " " & _ Format(D - C - 1, "00") & " " & _ Format(E - D - 1, "00") & " " & _ Format(F - E - 1, "00") Set myCell = Cells.Find(strGap, , , xlPart) If myCell Is Nothing Then Cells(myRow, myCol).Value = strGap & " Count = 1" myRow = myRow + 1 If myRow = Rows.Count + 1 Then myRow = 1 myCol = myCol + 1 End If Else myCell.Value = strGap & " Count = " & Format(Val(Trim( _ Replace(myCell.Value, strGap & " Count = ", ""))) + 1, "0") End If Next F Next E Next D Next C Next B Next A Application.ScreenUpdating = True End Sub "Paul Black" wrote in message ups.com... Hi Bernie, I think the confusion is me not being able to explain myself properly. I would like a list of the total combinations for each category of Gaps calculated for each combination. For example, taking the combinations below in a 6 from 49 Lotto as ... Combination 01 02 03 04 05 06 = Gap category 00 00 00 00 00 Combination 01 02 03 04 05 07 = Gap category 00 00 00 00 01 Combination 01 02 03 04 05 08 = Gap category 00 00 00 00 02 Combination 01 02 03 04 05 09 = Gap category 00 00 00 00 03 Combination 01 02 03 04 05 10 = Gap category 00 00 00 00 04 Combination 01 02 03 04 05 11 = Gap category 00 00 00 00 05 Combination 01 02 03 04 05 12 = Gap category 00 00 00 00 06 Combination 01 11 12 14 18 21 = Gap category 09 00 01 03 02 Combination 11 21 22 24 28 31 = Gap category 09 00 01 03 02 Combination 21 22 23 24 25 31 = Gap category 00 00 00 00 05 ... the category list and total combinations for the above will be ... Category 00 00 00 00 00 = total combinations of 1 ( One ) Category 00 00 00 00 01 = total combinations of 1 ( One ) Category 00 00 00 00 02 = total combinations of 3 ( Three ) Category 00 00 00 00 03 = total combinations of 1 ( One ) Category 00 00 00 00 04 = total combinations of 1 ( One ) Category 00 00 00 00 05 = total combinations of 2 ( Two ) Category 00 00 00 00 06 = total combinations of 1 ( One ) I would also like the total combinations at the end please, which of course should be 13,983,816 combinations. I hope this makes it a bit clearer. Thanks in Advance. All the Best. Paul On Jul 10, 9:50 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, Gaps of 43 5 69,919,080 69,919,080 / 5 =13,983,816 Bernie "Paul Black" wrote in message ps.com... Thanks Bernie for producing the code & thanks Tom for running it and listing the information for me, it is greatly appreciated. Have either of you had any thoughts on achieving my main request at all please, that is the part at the beginning of my initial post and goes down to and includes the paragraph ... It would also be nice to have a Total at the bottom of the combinations list, which of course, should equal 13,983,816 combinations. Thanks in Advance. All the Best. Paul On Jul 10, 6:24 pm, Tom Ogilvy wrote: I ran it, here are the results: Gaps Gaps of 00 8,561,520 Gaps of 01 7,669,695 Gaps of 02 6,853,770 Gaps of 03 6,108,795 Gaps of 04 5,430,040 Gaps of 05 4,812,990 Gaps of 06 4,253,340 Gaps of 07 3,746,990 Gaps of 08 3,290,040 Gaps of 09 2,878,785 Gaps of 10 2,509,710 Gaps of 11 2,179,485 Gaps of 12 1,884,960 Gaps of 13 1,623,160 Gaps of 14 1,391,280 Gaps of 15 1,186,680 Gaps of 16 1,006,880 Gaps of 17 849,555 Gaps of 18 712,530 Gaps of 19 593,775 Gaps of 20 491,400 Gaps of 21 403,650 Gaps of 22 328,900 Gaps of 23 265,650 Gaps of 24 212,520 Gaps of 25 168,245 Gaps of 26 131,670 Gaps of 27 101,745 Gaps of 28 77,520 Gaps of 29 58,140 Gaps of 30 42,840 Gaps of 31 30,940 Gaps of 32 21,840 Gaps of 33 15,015 Gaps of 34 10,010 Gaps of 35 6,435 Gaps of 36 3,960 Gaps of 37 2,310 Gaps of 38 1,260 Gaps of 39 630 Gaps of 40 280 Gaps of 41 105 Gaps of 42 30 Gaps of 43 5 69,919,080 -- Regards, Tom Ogilvy "Paul Black" wrote: Thanks for the code Bernie. I don't have access to Excel at the moment so am unable to test the code. Does the code apply to my original request or to my Private Sub Gaps() effort please. I assume it applies to my effort. It is my original request that is the most important one. Thanks in Advance. All the Best. Paul On Jul 10, 5:04 pm, Tom Ogilvy wrote: Whoops! mistook your explantion for part of your question <face red. Have to confess I didn't read the original post so thought it was all a quote. -- Regards, Tom Ogilvy "Bernie Deitrick" wrote: Tom, You could repeat this pattern a total of 6 times until you ran out of numbers. I think we're in agreement: Those 'gaps' can only be produced by 6 different combinations: 01 08 15 24 37 44 02 09 16 25 38 45 03 10 17 26 39 46 04 11 18 27 40 47 05 12 19 28 41 48 06 13 20 29 42 49 But I have no idea how the OP got 925,365 combinations: Gaps 06 06 08 12 06 = x combinations ( x could be 925,365 combinations with the exact same Gaps between the balls 1-2, 2-3 ,3-4 ,4-5 & 5-6 ) Bernie- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Calculating Gaps Between Numbers
Hi Bernie,
Thanks for the two Subs. As I said before I am unable to test them at the moment. The data I posted previously was incorrect, totally my fault. I have revised it and the correct data that I should have posted is below. I would like a list of the total combinations for each unique Gaps category calculated from cycling through ALL 13,983,816 combinations. For example, taking a few combinations from the 13,983,816 combinations at random in a 6 from 49 Lotto with no replacement of ... Combination 01 02 03 04 05 06 = Gap category 00 00 00 00 00 Combination 01 02 03 04 05 07 = Gap category 00 00 00 00 01 Combination 01 02 03 04 05 08 = Gap category 00 00 00 00 02 Combination 01 02 03 04 05 09 = Gap category 00 00 00 00 03 Combination 01 02 03 04 05 10 = Gap category 00 00 00 00 04 Combination 01 02 03 04 05 11 = Gap category 00 00 00 00 05 Combination 01 02 03 04 05 12 = Gap category 00 00 00 00 06 Combination 01 11 12 14 18 21 = Gap category 09 00 01 03 02 Combination 11 21 22 24 28 31 = Gap category 09 00 01 03 02 Combination 19 22 23 24 28 31 = Gap category 02 00 00 03 02 Combination 29 32 33 34 38 41 = Gap category 02 00 00 03 02 Combination 31 34 35 36 40 43 = Gap category 02 00 00 03 02 .... the category list and total combinations for the above will be ... Category 00 00 00 00 00 = total combinations of 1 < One Category 00 00 00 00 01 = total combinations of 1 < One Category 00 00 00 00 02 = total combinations of 1 < One Category 00 00 00 00 03 = total combinations of 1 < One Category 00 00 00 00 04 = total combinations of 1 < One Category 00 00 00 00 05 = total combinations of 1 < One Category 00 00 00 00 06 = total combinations of 1 < One Category 09 00 01 03 02 = total combinations of 2 < TWO Category 02 00 00 03 02 = total combinations of 3 < THREE I would also like the total combinations at the end please, which of course should be 13,983,816 combinations. I hope this makes it a bit clearer. Thanks in Advance. All the Best. Paul On Jul 11, 1:39 am, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Here's a better macro, with fewer iterations - but still LOTS... Bernie Private Sub Gaps4() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False ' myCol = 1 myRow = 1 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents For A = 0 To mySize - 5 ' If A < 0 Then MsgBox "Finished " & A & " out of " & mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B For D = 0 To mySize - 5 - C For E = 0 To mySize - 5 - D If mySize - 5 - A - B - C - D - E 0 Then strGap = Format(A, "00") & " " & _ Format(B, "00") & " " & _ Format(C, "00") & " " & _ Format(D, "00") & " " & _ Format(E, "00") & " Count = " & Format(mySize - 5 - A - B - C - D - E, "0") Cells(myRow, myCol).Value = strGap myRow = myRow + 1 If myRow = Rows.Count + 1 Then myRow = 1 myCol = myCol + 1 End If End If Next E Next D Next C Next B Next A Application.ScreenUpdating = True End Sub "Bernie Deitrick" <deitbe @ consumer dot org wrote in .. . Paul, Try the macro below.... but try it first with a lower mySize number (see the comment). It may require a supercomputer to finish, or hours and hours... 13,983,816 combinations is a lot to process. Bernie Private Sub Gaps3() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False myCol = 1 myRow = 1 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents For A = 1 To mySize - 5 'If A < 1 Then MsgBox "Finished " & A - 1 & " out of " & mySize - 5 For B = A + 1 To mySize - 4 For C = B + 1 To mySize - 3 For D = C + 1 To mySize - 2 For E = D + 1 To mySize - 1 For F = E + 1 To mySize strGap = Format(B - A - 1, "00") & " " & _ Format(C - B - 1, "00") & " " & _ Format(D - C - 1, "00") & " " & _ Format(E - D - 1, "00") & " " & _ Format(F - E - 1, "00") Set myCell = Cells.Find(strGap, , , xlPart) If myCell Is Nothing Then Cells(myRow, myCol).Value = strGap & " Count = 1" myRow = myRow + 1 If myRow = Rows.Count + 1 Then myRow = 1 myCol = myCol + 1 End If Else myCell.Value = strGap & " Count = " & Format(Val(Trim( _ Replace(myCell.Value, strGap & " Count = ", ""))) + 1, "0") End If Next F Next E Next D Next C Next B Next A Application.ScreenUpdating = True End Sub "Paul Black" wrote in message oups.com... Hi Bernie, I think the confusion is me not being able to explain myself properly. I would like a list of the total combinations for each category of Gaps calculated for each combination. For example, taking the combinations below in a 6 from 49 Lotto as ... Combination 01 02 03 04 05 06 = Gap category 00 00 00 00 00 Combination 01 02 03 04 05 07 = Gap category 00 00 00 00 01 Combination 01 02 03 04 05 08 = Gap category 00 00 00 00 02 Combination 01 02 03 04 05 09 = Gap category 00 00 00 00 03 Combination 01 02 03 04 05 10 = Gap category 00 00 00 00 04 Combination 01 02 03 04 05 11 = Gap category 00 00 00 00 05 Combination 01 02 03 04 05 12 = Gap category 00 00 00 00 06 Combination 01 11 12 14 18 21 = Gap category 09 00 01 03 02 Combination 11 21 22 24 28 31 = Gap category 09 00 01 03 02 Combination 21 22 23 24 25 31 = Gap category 00 00 00 00 05 ... the category list and total combinations for the above will be ... Category 00 00 00 00 00 = total combinations of 1 ( One ) Category 00 00 00 00 01 = total combinations of 1 ( One ) Category 00 00 00 00 02 = total combinations of 3 ( Three ) Category 00 00 00 00 03 = total combinations of 1 ( One ) Category 00 00 00 00 04 = total combinations of 1 ( One ) Category 00 00 00 00 05 = total combinations of 2 ( Two ) Category 00 00 00 00 06 = total combinations of 1 ( One ) I would also like the total combinations at the end please, which of course should be 13,983,816 combinations. I hope this makes it a bit clearer. Thanks in Advance. All the Best. Paul On Jul 10, 9:50 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, Gaps of 43 5 69,919,080 69,919,080 / 5 =13,983,816 Bernie "Paul Black" wrote in message roups.com... Thanks Bernie for producing the code & thanks Tom for running it and listing the information for me, it is greatly appreciated. Have either of you had any thoughts on achieving my main request at all please, that is the part at the beginning of my initial post and goes down to and includes the paragraph ... It would also be nice to have a Total at the bottom of the combinations list, which of course, should equal 13,983,816 combinations. Thanks in Advance. All the Best. Paul On Jul 10, 6:24 pm, Tom Ogilvy wrote: I ran it, here are the results: Gaps Gaps of 00 8,561,520 Gaps of 01 7,669,695 Gaps of 02 6,853,770 Gaps of 03 6,108,795 Gaps of 04 5,430,040 Gaps of 05 4,812,990 Gaps of 06 4,253,340 Gaps of 07 3,746,990 Gaps of 08 3,290,040 Gaps of 09 2,878,785 Gaps of 10 2,509,710 Gaps of 11 2,179,485 Gaps of 12 1,884,960 Gaps of 13 1,623,160 Gaps of 14 1,391,280 Gaps of 15 1,186,680 Gaps of 16 1,006,880 Gaps of 17 849,555 Gaps of 18 712,530 Gaps of 19 593,775 Gaps of 20 491,400 Gaps of 21 403,650 Gaps of 22 328,900 Gaps of 23 265,650 Gaps of 24 212,520 Gaps of 25 168,245 Gaps of 26 131,670 Gaps of 27 101,745 Gaps of 28 77,520 Gaps of 29 58,140 Gaps of 30 42,840 Gaps of 31 30,940 Gaps of 32 21,840 Gaps of 33 15,015 Gaps of 34 10,010 Gaps of 35 6,435 Gaps of 36 3,960 Gaps of 37 2,310 Gaps of 38 1,260 Gaps of 39 630 Gaps of 40 280 Gaps of 41 105 Gaps of 42 30 Gaps of 43 5 69,919,080 -- Regards, Tom Ogilvy "Paul Black" wrote: Thanks for the code Bernie. I don't have access to Excel at the moment so am unable to test the code. Does the code apply to my original request or to my Private Sub Gaps() effort please. I assume it applies to my effort. It is my original request that is the most important one. Thanks in Advance. All the Best. Paul On Jul 10, 5:04 pm, Tom Ogilvy wrote: Whoops! mistook your explantion for part of your question <face red. Have to confess I didn't read the original post so thought it was all a quote. -- Regards, Tom Ogilvy "Bernie Deitrick" wrote: Tom, You could repeat this pattern a total of 6 times until you ran out of numbers. I think we're in agreement: Those 'gaps' can only be produced by 6 different combinations: 01 08 15 24 37 44 02 09 16 25 38 45 03 10 17 26 39 46 04 11 18 27 40 47 05 12 19 28 41 48 06 13 20 29 42 49 But I have no idea how the OP got 925,365 combinations: Gaps 06 06 08 12 06 = x combinations ( x could be 925,365 combinations with the exact same Gaps between the balls 1-2, 2-3 ,3-4 ,4-5 & 5-6 ) Bernie- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Calculating Gaps Between Numbers
Paul,
That is what the second macro will do - list all Gaps combinations and their count. HTH, Bernie MS Excel MVP I would like a list of the total combinations for each unique Gaps category calculated from cycling through ALL 13,983,816 combinations. |
Calculating Gaps Between Numbers
This would be a better loop control structu
For A = 0 To mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B - A For D = 0 To mySize - 5 - C - B - A For E = 0 To mySize - 5 - D - C - B - A HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Here's a better macro, with fewer iterations - but still LOTS... Bernie Private Sub Gaps4() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False ' myCol = 1 myRow = 1 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents For A = 0 To mySize - 5 ' If A < 0 Then MsgBox "Finished " & A & " out of " & mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B For D = 0 To mySize - 5 - C For E = 0 To mySize - 5 - D If mySize - 5 - A - B - C - D - E 0 Then strGap = Format(A, "00") & " " & _ Format(B, "00") & " " & _ Format(C, "00") & " " & _ Format(D, "00") & " " & _ Format(E, "00") & " Count = " & Format(mySize - 5 - A - B - C - D - E, "0") Cells(myRow, myCol).Value = strGap myRow = myRow + 1 If myRow = Rows.Count + 1 Then myRow = 1 myCol = myCol + 1 End If End If Next E Next D Next C Next B Next A Application.ScreenUpdating = True End Sub "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Paul, Try the macro below.... but try it first with a lower mySize number (see the comment). It may require a supercomputer to finish, or hours and hours... 13,983,816 combinations is a lot to process. Bernie Private Sub Gaps3() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False myCol = 1 myRow = 1 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents For A = 1 To mySize - 5 'If A < 1 Then MsgBox "Finished " & A - 1 & " out of " & mySize - 5 For B = A + 1 To mySize - 4 For C = B + 1 To mySize - 3 For D = C + 1 To mySize - 2 For E = D + 1 To mySize - 1 For F = E + 1 To mySize strGap = Format(B - A - 1, "00") & " " & _ Format(C - B - 1, "00") & " " & _ Format(D - C - 1, "00") & " " & _ Format(E - D - 1, "00") & " " & _ Format(F - E - 1, "00") Set myCell = Cells.Find(strGap, , , xlPart) If myCell Is Nothing Then Cells(myRow, myCol).Value = strGap & " Count = 1" myRow = myRow + 1 If myRow = Rows.Count + 1 Then myRow = 1 myCol = myCol + 1 End If Else myCell.Value = strGap & " Count = " & Format(Val(Trim( _ Replace(myCell.Value, strGap & " Count = ", ""))) + 1, "0") End If Next F Next E Next D Next C Next B Next A Application.ScreenUpdating = True End Sub "Paul Black" wrote in message ups.com... Hi Bernie, I think the confusion is me not being able to explain myself properly. I would like a list of the total combinations for each category of Gaps calculated for each combination. For example, taking the combinations below in a 6 from 49 Lotto as ... Combination 01 02 03 04 05 06 = Gap category 00 00 00 00 00 Combination 01 02 03 04 05 07 = Gap category 00 00 00 00 01 Combination 01 02 03 04 05 08 = Gap category 00 00 00 00 02 Combination 01 02 03 04 05 09 = Gap category 00 00 00 00 03 Combination 01 02 03 04 05 10 = Gap category 00 00 00 00 04 Combination 01 02 03 04 05 11 = Gap category 00 00 00 00 05 Combination 01 02 03 04 05 12 = Gap category 00 00 00 00 06 Combination 01 11 12 14 18 21 = Gap category 09 00 01 03 02 Combination 11 21 22 24 28 31 = Gap category 09 00 01 03 02 Combination 21 22 23 24 25 31 = Gap category 00 00 00 00 05 ... the category list and total combinations for the above will be ... Category 00 00 00 00 00 = total combinations of 1 ( One ) Category 00 00 00 00 01 = total combinations of 1 ( One ) Category 00 00 00 00 02 = total combinations of 3 ( Three ) Category 00 00 00 00 03 = total combinations of 1 ( One ) Category 00 00 00 00 04 = total combinations of 1 ( One ) Category 00 00 00 00 05 = total combinations of 2 ( Two ) Category 00 00 00 00 06 = total combinations of 1 ( One ) I would also like the total combinations at the end please, which of course should be 13,983,816 combinations. I hope this makes it a bit clearer. Thanks in Advance. All the Best. Paul On Jul 10, 9:50 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, Gaps of 43 5 69,919,080 69,919,080 / 5 =13,983,816 Bernie "Paul Black" wrote in message ps.com... Thanks Bernie for producing the code & thanks Tom for running it and listing the information for me, it is greatly appreciated. Have either of you had any thoughts on achieving my main request at all please, that is the part at the beginning of my initial post and goes down to and includes the paragraph ... It would also be nice to have a Total at the bottom of the combinations list, which of course, should equal 13,983,816 combinations. Thanks in Advance. All the Best. Paul On Jul 10, 6:24 pm, Tom Ogilvy wrote: I ran it, here are the results: Gaps Gaps of 00 8,561,520 Gaps of 01 7,669,695 Gaps of 02 6,853,770 Gaps of 03 6,108,795 Gaps of 04 5,430,040 Gaps of 05 4,812,990 Gaps of 06 4,253,340 Gaps of 07 3,746,990 Gaps of 08 3,290,040 Gaps of 09 2,878,785 Gaps of 10 2,509,710 Gaps of 11 2,179,485 Gaps of 12 1,884,960 Gaps of 13 1,623,160 Gaps of 14 1,391,280 Gaps of 15 1,186,680 Gaps of 16 1,006,880 Gaps of 17 849,555 Gaps of 18 712,530 Gaps of 19 593,775 Gaps of 20 491,400 Gaps of 21 403,650 Gaps of 22 328,900 Gaps of 23 265,650 Gaps of 24 212,520 Gaps of 25 168,245 Gaps of 26 131,670 Gaps of 27 101,745 Gaps of 28 77,520 Gaps of 29 58,140 Gaps of 30 42,840 Gaps of 31 30,940 Gaps of 32 21,840 Gaps of 33 15,015 Gaps of 34 10,010 Gaps of 35 6,435 Gaps of 36 3,960 Gaps of 37 2,310 Gaps of 38 1,260 Gaps of 39 630 Gaps of 40 280 Gaps of 41 105 Gaps of 42 30 Gaps of 43 5 69,919,080 -- Regards, Tom Ogilvy "Paul Black" wrote: Thanks for the code Bernie. I don't have access to Excel at the moment so am unable to test the code. Does the code apply to my original request or to my Private Sub Gaps() effort please. I assume it applies to my effort. It is my original request that is the most important one. Thanks in Advance. All the Best. Paul On Jul 10, 5:04 pm, Tom Ogilvy wrote: Whoops! mistook your explantion for part of your question <face red. Have to confess I didn't read the original post so thought it was all a quote. -- Regards, Tom Ogilvy "Bernie Deitrick" wrote: Tom, You could repeat this pattern a total of 6 times until you ran out of numbers. I think we're in agreement: Those 'gaps' can only be produced by 6 different combinations: 01 08 15 24 37 44 02 09 16 25 38 45 03 10 17 26 39 46 04 11 18 27 40 47 05 12 19 28 41 48 06 13 20 29 42 49 But I have no idea how the OP got 925,365 combinations: Gaps 06 06 08 12 06 = x combinations ( x could be 925,365 combinations with the exact same Gaps between the balls 1-2, 2-3 ,3-4 ,4-5 & 5-6 ) Bernie- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Calculating Gaps Between Numbers
Thanks Bernie,
But how do I apply this to the existing code. Thanks in Advance. All the Best. Paul On Jul 11, 2:31 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: This would be a better loop control structu For A = 0 To mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B - A For D = 0 To mySize - 5 - C - B - A For E = 0 To mySize - 5 - D - C - B - A HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in l... Here's a better macro, with fewer iterations - but still LOTS... Bernie Private Sub Gaps4() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False ' myCol = 1 myRow = 1 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents For A = 0 To mySize - 5 ' If A < 0 Then MsgBox "Finished " & A & " out of " & mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B For D = 0 To mySize - 5 - C For E = 0 To mySize - 5 - D If mySize - 5 - A - B - C - D - E 0 Then strGap = Format(A, "00") & " " & _ Format(B, "00") & " " & _ Format(C, "00") & " " & _ Format(D, "00") & " " & _ Format(E, "00") & " Count = " & Format(mySize - 5 - A - B - C - D - E, "0") Cells(myRow, myCol).Value = strGap myRow = myRow + 1 If myRow = Rows.Count + 1 Then myRow = 1 myCol = myCol + 1 End If End If Next E Next D Next C Next B Next A Application.ScreenUpdating = True End Sub "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Paul, Try the macro below.... but try it first with a lower mySize number (see the comment). It may require a supercomputer to finish, or hours and hours... 13,983,816 combinations is a lot to process. Bernie Private Sub Gaps3() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False myCol = 1 myRow = 1 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents For A = 1 To mySize - 5 'If A < 1 Then MsgBox "Finished " & A - 1 & " out of " & mySize - 5 For B = A + 1 To mySize - 4 For C = B + 1 To mySize - 3 For D = C + 1 To mySize - 2 For E = D + 1 To mySize - 1 For F = E + 1 To mySize strGap = Format(B - A - 1, "00") & " " & _ Format(C - B - 1, "00") & " " & _ Format(D - C - 1, "00") & " " & _ Format(E - D - 1, "00") & " " & _ Format(F - E - 1, "00") Set myCell = Cells.Find(strGap, , , xlPart) If myCell Is Nothing Then Cells(myRow, myCol).Value = strGap & " Count = 1" myRow = myRow + 1 If myRow = Rows.Count + 1 Then myRow = 1 myCol = myCol + 1 End If Else myCell.Value = strGap & " Count = " & Format(Val(Trim( _ Replace(myCell.Value, strGap & " Count = ", ""))) + 1, "0") End If Next F Next E Next D Next C Next B Next A Application.ScreenUpdating = True End Sub "Paul Black" wrote in message roups.com... Hi Bernie, I think the confusion is me not being able to explain myself properly. I would like a list of the total combinations for each category of Gaps calculated for each combination. For example, taking the combinations below in a 6 from 49 Lotto as ... Combination 01 02 03 04 05 06 = Gap category 00 00 00 00 00 Combination 01 02 03 04 05 07 = Gap category 00 00 00 00 01 Combination 01 02 03 04 05 08 = Gap category 00 00 00 00 02 Combination 01 02 03 04 05 09 = Gap category 00 00 00 00 03 Combination 01 02 03 04 05 10 = Gap category 00 00 00 00 04 Combination 01 02 03 04 05 11 = Gap category 00 00 00 00 05 Combination 01 02 03 04 05 12 = Gap category 00 00 00 00 06 Combination 01 11 12 14 18 21 = Gap category 09 00 01 03 02 Combination 11 21 22 24 28 31 = Gap category 09 00 01 03 02 Combination 21 22 23 24 25 31 = Gap category 00 00 00 00 05 ... the category list and total combinations for the above will be ... Category 00 00 00 00 00 = total combinations of 1 ( One ) Category 00 00 00 00 01 = total combinations of 1 ( One ) Category 00 00 00 00 02 = total combinations of 3 ( Three ) Category 00 00 00 00 03 = total combinations of 1 ( One ) Category 00 00 00 00 04 = total combinations of 1 ( One ) Category 00 00 00 00 05 = total combinations of 2 ( Two ) Category 00 00 00 00 06 = total combinations of 1 ( One ) I would also like the total combinations at the end please, which of course should be 13,983,816 combinations. I hope this makes it a bit clearer. Thanks in Advance. All the Best. Paul On Jul 10, 9:50 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, Gaps of 43 5 69,919,080 69,919,080 / 5 =13,983,816 Bernie "Paul Black" wrote in message groups.com... Thanks Bernie for producing the code & thanks Tom for running it and listing the information for me, it is greatly appreciated. Have either of you had any thoughts on achieving my main request at all please, that is the part at the beginning of my initial post and goes down to and includes the paragraph ... It would also be nice to have a Total at the bottom of the combinations list, which of course, should equal 13,983,816 combinations. Thanks in Advance. All the Best. Paul On Jul 10, 6:24 pm, Tom Ogilvy wrote: I ran it, here are the results: Gaps Gaps of 00 8,561,520 Gaps of 01 7,669,695 Gaps of 02 6,853,770 Gaps of 03 6,108,795 Gaps of 04 5,430,040 Gaps of 05 4,812,990 Gaps of 06 4,253,340 Gaps of 07 3,746,990 Gaps of 08 3,290,040 Gaps of 09 2,878,785 Gaps of 10 2,509,710 Gaps of 11 2,179,485 Gaps of 12 1,884,960 Gaps of 13 1,623,160 Gaps of 14 1,391,280 Gaps of 15 1,186,680 Gaps of 16 1,006,880 Gaps of 17 849,555 Gaps of 18 712,530 Gaps of 19 593,775 Gaps of 20 491,400 Gaps of 21 403,650 Gaps of 22 328,900 Gaps of 23 265,650 Gaps of 24 212,520 Gaps of 25 168,245 Gaps of 26 131,670 Gaps of 27 101,745 Gaps of 28 77,520 Gaps of 29 58,140 Gaps of 30 42,840 Gaps of 31 30,940 Gaps of 32 21,840 Gaps of 33 15,015 Gaps of 34 10,010 Gaps of 35 6,435 Gaps of 36 3,960 Gaps of 37 2,310 Gaps of 38 1,260 Gaps of 39 630 Gaps of 40 280 Gaps of 41 105 Gaps of 42 30 Gaps of 43 5 69,919,080 -- Regards, Tom Ogilvy "Paul Black" wrote: Thanks for the code Bernie. I don't have access to Excel at the moment so am unable to test the code. Does the code apply to my original request or to my Private Sub Gaps() effort please. I assume it applies to my effort. It is my original request that is the most important one. Thanks in Advance. All the Best. Paul On Jul 10, 5:04 pm, Tom Ogilvy wrote: Whoops! mistook your explantion for part of your question <face red. Have to confess I didn't read the original post so thought it was all a quote. -- Regards, Tom Ogilvy "Bernie Deitrick" wrote: Tom, You could repeat this pattern a total of 6 times until you ran out of numbers. I think we're in agreement: Those 'gaps' can only be produced by 6 different combinations: 01 08 15 24 37 44 02 09 16 25 38 45 03 10 17 26 39 46 04 11 18 27 40 47 05 12 19 28 41 48 06 13 20 29 42 49 But I have no idea how the OP got 925,365 combinations: Gaps 06 06 08 12 06 = x combinations ( x could be 925,365 combinations with the exact same Gaps between the balls 1-2, 2-3 ,3-4 ,4-5 & 5-6 ) Bernie- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Calculating Gaps Between Numbers
Paul,
Replace this: For A = 0 To mySize - 5 ' If A < 0 Then MsgBox "Finished " & A & " out of " & mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B For D = 0 To mySize - 5 - C For E = 0 To mySize - 5 - D with this For A = 0 To mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B - A For D = 0 To mySize - 5 - C - B - A For E = 0 To mySize - 5 - D - C - B - A HTH, Bernie MS Excel MVP "Paul Black" wrote in message ups.com... Thanks Bernie, But how do I apply this to the existing code. Thanks in Advance. All the Best. Paul On Jul 11, 2:31 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: This would be a better loop control structu For A = 0 To mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B - A For D = 0 To mySize - 5 - C - B - A For E = 0 To mySize - 5 - D - C - B - A HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in l... Here's a better macro, with fewer iterations - but still LOTS... Bernie Private Sub Gaps4() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False ' myCol = 1 myRow = 1 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents For A = 0 To mySize - 5 ' If A < 0 Then MsgBox "Finished " & A & " out of " & mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B For D = 0 To mySize - 5 - C For E = 0 To mySize - 5 - D If mySize - 5 - A - B - C - D - E 0 Then strGap = Format(A, "00") & " " & _ Format(B, "00") & " " & _ Format(C, "00") & " " & _ Format(D, "00") & " " & _ Format(E, "00") & " Count = " & Format(mySize - 5 - A - B - C - D - E, "0") Cells(myRow, myCol).Value = strGap myRow = myRow + 1 If myRow = Rows.Count + 1 Then myRow = 1 myCol = myCol + 1 End If End If Next E Next D Next C Next B Next A Application.ScreenUpdating = True End Sub "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Paul, Try the macro below.... but try it first with a lower mySize number (see the comment). It may require a supercomputer to finish, or hours and hours... 13,983,816 combinations is a lot to process. Bernie Private Sub Gaps3() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False myCol = 1 myRow = 1 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents For A = 1 To mySize - 5 'If A < 1 Then MsgBox "Finished " & A - 1 & " out of " & mySize - 5 For B = A + 1 To mySize - 4 For C = B + 1 To mySize - 3 For D = C + 1 To mySize - 2 For E = D + 1 To mySize - 1 For F = E + 1 To mySize strGap = Format(B - A - 1, "00") & " " & _ Format(C - B - 1, "00") & " " & _ Format(D - C - 1, "00") & " " & _ Format(E - D - 1, "00") & " " & _ Format(F - E - 1, "00") Set myCell = Cells.Find(strGap, , , xlPart) If myCell Is Nothing Then Cells(myRow, myCol).Value = strGap & " Count = 1" myRow = myRow + 1 If myRow = Rows.Count + 1 Then myRow = 1 myCol = myCol + 1 End If Else myCell.Value = strGap & " Count = " & Format(Val(Trim( _ Replace(myCell.Value, strGap & " Count = ", ""))) + 1, "0") End If Next F Next E Next D Next C Next B Next A Application.ScreenUpdating = True End Sub "Paul Black" wrote in message roups.com... Hi Bernie, I think the confusion is me not being able to explain myself properly. I would like a list of the total combinations for each category of Gaps calculated for each combination. For example, taking the combinations below in a 6 from 49 Lotto as ... Combination 01 02 03 04 05 06 = Gap category 00 00 00 00 00 Combination 01 02 03 04 05 07 = Gap category 00 00 00 00 01 Combination 01 02 03 04 05 08 = Gap category 00 00 00 00 02 Combination 01 02 03 04 05 09 = Gap category 00 00 00 00 03 Combination 01 02 03 04 05 10 = Gap category 00 00 00 00 04 Combination 01 02 03 04 05 11 = Gap category 00 00 00 00 05 Combination 01 02 03 04 05 12 = Gap category 00 00 00 00 06 Combination 01 11 12 14 18 21 = Gap category 09 00 01 03 02 Combination 11 21 22 24 28 31 = Gap category 09 00 01 03 02 Combination 21 22 23 24 25 31 = Gap category 00 00 00 00 05 ... the category list and total combinations for the above will be ... Category 00 00 00 00 00 = total combinations of 1 ( One ) Category 00 00 00 00 01 = total combinations of 1 ( One ) Category 00 00 00 00 02 = total combinations of 3 ( Three ) Category 00 00 00 00 03 = total combinations of 1 ( One ) Category 00 00 00 00 04 = total combinations of 1 ( One ) Category 00 00 00 00 05 = total combinations of 2 ( Two ) Category 00 00 00 00 06 = total combinations of 1 ( One ) I would also like the total combinations at the end please, which of course should be 13,983,816 combinations. I hope this makes it a bit clearer. Thanks in Advance. All the Best. Paul On Jul 10, 9:50 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, Gaps of 43 5 69,919,080 69,919,080 / 5 =13,983,816 Bernie "Paul Black" wrote in message groups.com... Thanks Bernie for producing the code & thanks Tom for running it and listing the information for me, it is greatly appreciated. Have either of you had any thoughts on achieving my main request at all please, that is the part at the beginning of my initial post and goes down to and includes the paragraph ... It would also be nice to have a Total at the bottom of the combinations list, which of course, should equal 13,983,816 combinations. Thanks in Advance. All the Best. Paul On Jul 10, 6:24 pm, Tom Ogilvy wrote: I ran it, here are the results: Gaps Gaps of 00 8,561,520 Gaps of 01 7,669,695 Gaps of 02 6,853,770 Gaps of 03 6,108,795 Gaps of 04 5,430,040 Gaps of 05 4,812,990 Gaps of 06 4,253,340 Gaps of 07 3,746,990 Gaps of 08 3,290,040 Gaps of 09 2,878,785 Gaps of 10 2,509,710 Gaps of 11 2,179,485 Gaps of 12 1,884,960 Gaps of 13 1,623,160 Gaps of 14 1,391,280 Gaps of 15 1,186,680 Gaps of 16 1,006,880 Gaps of 17 849,555 Gaps of 18 712,530 Gaps of 19 593,775 Gaps of 20 491,400 Gaps of 21 403,650 Gaps of 22 328,900 Gaps of 23 265,650 Gaps of 24 212,520 Gaps of 25 168,245 Gaps of 26 131,670 Gaps of 27 101,745 Gaps of 28 77,520 Gaps of 29 58,140 Gaps of 30 42,840 Gaps of 31 30,940 Gaps of 32 21,840 Gaps of 33 15,015 Gaps of 34 10,010 Gaps of 35 6,435 Gaps of 36 3,960 Gaps of 37 2,310 Gaps of 38 1,260 Gaps of 39 630 Gaps of 40 280 Gaps of 41 105 Gaps of 42 30 Gaps of 43 5 69,919,080 -- Regards, Tom Ogilvy "Paul Black" wrote: Thanks for the code Bernie. I don't have access to Excel at the moment so am unable to test the code. Does the code apply to my original request or to my Private Sub Gaps() effort please. I assume it applies to my effort. It is my original request that is the most important one. Thanks in Advance. All the Best. Paul On Jul 10, 5:04 pm, Tom Ogilvy wrote: Whoops! mistook your explantion for part of your question <face red. Have to confess I didn't read the original post so thought it was all a quote. -- Regards, Tom Ogilvy "Bernie Deitrick" wrote: Tom, You could repeat this pattern a total of 6 times until you ran out of numbers. I think we're in agreement: Those 'gaps' can only be produced by 6 different combinations: 01 08 15 24 37 44 02 09 16 25 38 45 03 10 17 26 39 46 04 11 18 27 40 47 05 12 19 28 41 48 06 13 20 29 42 49 But I have no idea how the OP got 925,365 combinations: Gaps 06 06 08 12 06 = x combinations ( x could be 925,365 combinations with the exact same Gaps between the balls 1-2, 2-3 ,3-4 ,4-5 & 5-6 ) Bernie- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Calculating Gaps Between Numbers
Bernie, thanks ever so much for all the time and effort you have given
to this, it is appreciated. Just a couple of things though please. I am going over to see my niece this evening so I will be able to run the two Subs you provided below ( the second one has been updated according to your last response ). Firstly, for Private Sub Gaps2(), will it possible to have the Grand Total for ALL the combinations produced in column "C" please . This figure will be 69,919,080 as produced by Mr. Tom Ogilvy when he kindly ran the Sub for me and will go after the last entry in Column "C". Secondly, will it be possible for Private Sub Gaps4() to have ... Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" .... and the categories list starting in Cell "B3" and the Total combinations associated with each category starting in Cell "C3" like ... Column "B" Column "C" 00 00 00 00 01 xxx,xxx,xxx 00 00 08 03 02 xxx,xxx,xxx 09 08 02 02 03 xxx,xxx,xxx .... for example. Finally, will it also be possible to have the Grand Total for ALL the combinations produced in column "C" please, this will go after the last entry in Column "C". Private Sub Gaps2() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim GapsTotal(0 To 43) As Long Application.ScreenUpdating = False For i = 0 To 43 GapsTotal(i) = 0 Next i For A = 1 To 44 For B = A + 1 To 45 For C = B + 1 To 46 For D = C + 1 To 47 For E = D + 1 To 48 For F = E + 1 To 49 GapsTotal(B - A - 1) = GapsTotal(B - A - 1) + 1 GapsTotal(C - B - 1) = GapsTotal(C - B - 1) + 1 GapsTotal(D - C - 1) = GapsTotal(D - C - 1) + 1 GapsTotal(E - D - 1) = GapsTotal(E - D - 1) + 1 GapsTotal(F - E - 1) = GapsTotal(F - E - 1) + 1 Next F Next E Next D Next C Next B Next A Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" For i = 0 To 43 Sheets("Gaps Data").Cells(i + 3, 2).Value = "Gaps of " & Format(i, "00") Sheets("Gaps Data").Cells(i + 3, 3).Value = GapsTotal(i) Next i Application.ScreenUpdating = True End Sub --------------------------------------------------------------------- Private Sub Gaps4() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False ' myCol = 1 myRow = 1 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents For A = 0 To mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B - A For D = 0 To mySize - 5 - C - B - A For E = 0 To mySize - 5 - D - C - B - A If mySize - 5 - A - B - C - D - E 0 Then strGap = Format(A, "00") & " " & _ Format(B, "00") & " " & _ Format(C, "00") & " " & _ Format(D, "00") & " " & _ Format(E, "00") & " Count = " & Format(mySize - 5 - A - B - C - D - E, "0") Cells(myRow, myCol).Value = strGap myRow = myRow + 1 If myRow = Rows.Count + 1 Then myRow = 1 myCol = myCol + 1 End If End If Next E Next D Next C Next B Next A Application.ScreenUpdating = True End Sub I will then be able to run them later as I said. Thanks in Advance. All the Best. Paul On Jul 11, 4:34 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, Replace this: For A = 0 To mySize - 5 ' If A < 0 Then MsgBox "Finished " & A & " out of " & mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B For D = 0 To mySize - 5 - C For E = 0 To mySize - 5 - D with this For A = 0 To mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B - A For D = 0 To mySize - 5 - C - B - A For E = 0 To mySize - 5 - D - C - B - A HTH, Bernie MS Excel MVP "Paul Black" wrote in message ups.com... Thanks Bernie, But how do I apply this to the existing code. Thanks in Advance. All the Best. Paul On Jul 11, 2:31 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: This would be a better loop control structu For A = 0 To mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B - A For D = 0 To mySize - 5 - C - B - A For E = 0 To mySize - 5 - D - C - B - A HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in l... Here's a better macro, with fewer iterations - but still LOTS... Bernie Private Sub Gaps4() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False ' myCol = 1 myRow = 1 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents For A = 0 To mySize - 5 ' If A < 0 Then MsgBox "Finished " & A & " out of " & mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B For D = 0 To mySize - 5 - C For E = 0 To mySize - 5 - D If mySize - 5 - A - B - C - D - E 0 Then strGap = Format(A, "00") & " " & _ Format(B, "00") & " " & _ Format(C, "00") & " " & _ Format(D, "00") & " " & _ Format(E, "00") & " Count = " & Format(mySize - 5 - A - B - C - D - E, "0") Cells(myRow, myCol).Value = strGap myRow = myRow + 1 If myRow = Rows.Count + 1 Then myRow = 1 myCol = myCol + 1 End If End If Next E Next D Next C Next B Next A Application.ScreenUpdating = True End Sub "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Paul, Try the macro below.... but try it first with a lower mySize number (see the comment). It may require a supercomputer to finish, or hours and hours... 13,983,816 combinations is a lot to process. Bernie Private Sub Gaps3() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False myCol = 1 myRow = 1 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents For A = 1 To mySize - 5 'If A < 1 Then MsgBox "Finished " & A - 1 & " out of " & mySize - 5 For B = A + 1 To mySize - 4 For C = B + 1 To mySize - 3 For D = C + 1 To mySize - 2 For E = D + 1 To mySize - 1 For F = E + 1 To mySize strGap = Format(B - A - 1, "00") & " " & _ Format(C - B - 1, "00") & " " & _ Format(D - C - 1, "00") & " " & _ Format(E - D - 1, "00") & " " & _ Format(F - E - 1, "00") Set myCell = Cells.Find(strGap, , , xlPart) If myCell Is Nothing Then Cells(myRow, myCol).Value = strGap & " Count = 1" myRow = myRow + 1 If myRow = Rows.Count + 1 Then myRow = 1 myCol = myCol + 1 End If Else myCell.Value = strGap & " Count = " & Format(Val(Trim( _ Replace(myCell.Value, strGap & " Count = ", ""))) + 1, "0") End If Next F Next E Next D Next C Next B Next A Application.ScreenUpdating = True End Sub "Paul Black" wrote in message roups.com... Hi Bernie, I think the confusion is me not being able to explain myself properly. I would like a list of the total combinations for each category of Gaps calculated for each combination. For example, taking the combinations below in a 6 from 49 Lotto as ... Combination 01 02 03 04 05 06 = Gap category 00 00 00 00 00 Combination 01 02 03 04 05 07 = Gap category 00 00 00 00 01 Combination 01 02 03 04 05 08 = Gap category 00 00 00 00 02 Combination 01 02 03 04 05 09 = Gap category 00 00 00 00 03 Combination 01 02 03 04 05 10 = Gap category 00 00 00 00 04 Combination 01 02 03 04 05 11 = Gap category 00 00 00 00 05 Combination 01 02 03 04 05 12 = Gap category 00 00 00 00 06 Combination 01 11 12 14 18 21 = Gap category 09 00 01 03 02 Combination 11 21 22 24 28 31 = Gap category 09 00 01 03 02 Combination 21 22 23 24 25 31 = Gap category 00 00 00 00 05 ... the category list and total combinations for the above will be ... Category 00 00 00 00 00 = total combinations of 1 ( One ) Category 00 00 00 00 01 = total combinations of 1 ( One ) Category 00 00 00 00 02 = total combinations of 3 ( Three ) Category 00 00 00 00 03 = total combinations of 1 ( One ) Category 00 00 00 00 04 = total combinations of 1 ( One ) Category 00 00 00 00 05 = total combinations of 2 ( Two ) Category 00 00 00 00 06 = total combinations of 1 ( One ) I would also like the total combinations at the end please, which of course should be 13,983,816 combinations. I hope this makes it a bit clearer. Thanks in Advance. All the Best. Paul On Jul 10, 9:50 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, Gaps of 43 5 69,919,080 69,919,080 / 5 =13,983,816 Bernie "Paul Black" wrote in message groups.com... Thanks Bernie for producing the code & thanks Tom for running it and listing the information for me, it is greatly appreciated. Have either of you had any thoughts on achieving my main request at all please, that is the part at the beginning of my initial post and goes down to and includes the paragraph ... It would also be nice to have a Total at the bottom of the combinations list, which of course, should equal 13,983,816 combinations. Thanks in Advance. All the Best. Paul On Jul 10, 6:24 pm, Tom Ogilvy wrote: I ran it, here are the results: Gaps Gaps of 00 8,561,520 Gaps of 01 7,669,695 Gaps of 02 6,853,770 Gaps of 03 6,108,795 Gaps of 04 5,430,040 Gaps of 05 4,812,990 Gaps of 06 4,253,340 Gaps of 07 3,746,990 Gaps of 08 3,290,040 Gaps of 09 2,878,785 Gaps of 10 2,509,710 Gaps of 11 2,179,485 Gaps of 12 1,884,960 Gaps of 13 1,623,160 Gaps of 14 1,391,280 Gaps of 15 1,186,680 Gaps of 16 1,006,880 Gaps of 17 849,555 Gaps of 18 712,530 Gaps of 19 593,775 Gaps of 20 491,400 Gaps of 21 403,650 Gaps of 22 328,900 Gaps of 23 265,650 Gaps of 24 212,520 Gaps of 25 168,245 Gaps of 26 131,670 Gaps of 27 101,745 Gaps of 28 77,520 Gaps of 29 58,140 Gaps of 30 42,840 Gaps of 31 30,940 Gaps of 32 21,840 Gaps of 33 15,015 Gaps of 34 10,010 Gaps of 35 6,435 Gaps of 36 3,960 Gaps of 37 2,310 Gaps of 38 1,260 Gaps of 39 630 Gaps of 40 280 Gaps of 41 105 Gaps of 42 30 Gaps of 43 5 69,919,080 -- Regards, Tom Ogilvy "Paul Black" wrote: Thanks for the code Bernie. I don't have access to Excel at the moment so am unable to test the code. Does the code apply to my original request or to my Private Sub Gaps() effort please. I assume it applies to my effort. It is my original request that is the most important one. Thanks in Advance. All the Best. Paul On Jul 10, 5:04 pm, Tom Ogilvy wrote: Whoops! mistook your explantion for part of your question <face red. Have to confess ... read more »- Hide quoted text - - Show quoted text - |
Calculating Gaps Between Numbers
Paul,
The new version, Gaps2B (below), will produce the grand total formula at the bottom. The new version, Gaps4B (below Gaps2B) will produce the separate string / count columns, but it will still take up most of a worksheet - a single column won't hold the results HTH, Bernie MS Excel MVP Private Sub Gaps2B() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim GapsTotal(0 To 43) As Long Application.ScreenUpdating = False For i = 0 To 43 GapsTotal(i) = 0 Next i For A = 1 To 44 For B = A + 1 To 45 For C = B + 1 To 46 For D = C + 1 To 47 For E = D + 1 To 48 For F = E + 1 To 49 GapsTotal(B - A - 1) = GapsTotal(B - A - 1) + 1 GapsTotal(C - B - 1) = GapsTotal(C - B - 1) + 1 GapsTotal(D - C - 1) = GapsTotal(D - C - 1) + 1 GapsTotal(E - D - 1) = GapsTotal(E - D - 1) + 1 GapsTotal(F - E - 1) = GapsTotal(F - E - 1) + 1 Next F Next E Next D Next C Next B Next A Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" For i = 0 To 43 Sheets("Gaps Data").Cells(i + 3, 2).Value = "Gaps of " & Format(i, "00") Sheets("Gaps Data").Cells(i + 3, 3).Value = GapsTotal(i) Next i Sheets("Gaps Data").Cells(47, 2).Value = "Grand Total" Sheets("Gaps Data").Cells(47, 3).Formula = "=SUM(C3:C46)" Application.ScreenUpdating = True End Sub Private Sub Gaps4B() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False ' myCol = 1 myRow = 3 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents Cells(2, 1).Value = "Gap" Cells(2, 2).Value = "Total" For A = 0 To mySize - 5 ' If A < 0 Then MsgBox "Finished " & A & " out of " & mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B - A For D = 0 To mySize - 5 - C - B - A For E = 0 To mySize - 5 - D - C - B - A If mySize - 5 - A - B - C - D - E 0 Then strGap = Format(A, "00") & " " & _ Format(B, "00") & " " & _ Format(C, "00") & " " & _ Format(D, "00") & " " & _ Format(E, "00") Cells(myRow, myCol).Value = strGap Cells(myRow, myCol + 1).Value = mySize - 5 - A - B - C - D - E myRow = myRow + 1 If myRow = Rows.Count + 1 Then myRow = 3 myCol = myCol + 2 Cells(2, myCol).Value = "Gap ID" Cells(2, myCol + 1).Value = "Count" End If End If Next E Next D Next C Next B Next A Application.ScreenUpdating = True End Sub "Paul Black" wrote in message ps.com... Bernie, thanks ever so much for all the time and effort you have given to this, it is appreciated. Just a couple of things though please. I am going over to see my niece this evening so I will be able to run the two Subs you provided below ( the second one has been updated according to your last response ). Firstly, for Private Sub Gaps2(), will it possible to have the Grand Total for ALL the combinations produced in column "C" please . This figure will be 69,919,080 as produced by Mr. Tom Ogilvy when he kindly ran the Sub for me and will go after the last entry in Column "C". Secondly, will it be possible for Private Sub Gaps4() to have ... Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" .... and the categories list starting in Cell "B3" and the Total combinations associated with each category starting in Cell "C3" like ... Column "B" Column "C" 00 00 00 00 01 xxx,xxx,xxx 00 00 08 03 02 xxx,xxx,xxx 09 08 02 02 03 xxx,xxx,xxx .... for example. Finally, will it also be possible to have the Grand Total for ALL the combinations produced in column "C" please, this will go after the last entry in Column "C". Private Sub Gaps2() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim GapsTotal(0 To 43) As Long Application.ScreenUpdating = False For i = 0 To 43 GapsTotal(i) = 0 Next i For A = 1 To 44 For B = A + 1 To 45 For C = B + 1 To 46 For D = C + 1 To 47 For E = D + 1 To 48 For F = E + 1 To 49 GapsTotal(B - A - 1) = GapsTotal(B - A - 1) + 1 GapsTotal(C - B - 1) = GapsTotal(C - B - 1) + 1 GapsTotal(D - C - 1) = GapsTotal(D - C - 1) + 1 GapsTotal(E - D - 1) = GapsTotal(E - D - 1) + 1 GapsTotal(F - E - 1) = GapsTotal(F - E - 1) + 1 Next F Next E Next D Next C Next B Next A Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" For i = 0 To 43 Sheets("Gaps Data").Cells(i + 3, 2).Value = "Gaps of " & Format(i, "00") Sheets("Gaps Data").Cells(i + 3, 3).Value = GapsTotal(i) Next i Application.ScreenUpdating = True End Sub --------------------------------------------------------------------- Private Sub Gaps4() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False ' myCol = 1 myRow = 1 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents For A = 0 To mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B - A For D = 0 To mySize - 5 - C - B - A For E = 0 To mySize - 5 - D - C - B - A If mySize - 5 - A - B - C - D - E 0 Then strGap = Format(A, "00") & " " & _ Format(B, "00") & " " & _ Format(C, "00") & " " & _ Format(D, "00") & " " & _ Format(E, "00") & " Count = " & Format(mySize - 5 - A - B - C - D - E, "0") Cells(myRow, myCol).Value = strGap myRow = myRow + 1 If myRow = Rows.Count + 1 Then myRow = 1 myCol = myCol + 1 End If End If Next E Next D Next C Next B Next A Application.ScreenUpdating = True End Sub I will then be able to run them later as I said. Thanks in Advance. All the Best. Paul On Jul 11, 4:34 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, Replace this: For A = 0 To mySize - 5 ' If A < 0 Then MsgBox "Finished " & A & " out of " & mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B For D = 0 To mySize - 5 - C For E = 0 To mySize - 5 - D with this For A = 0 To mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B - A For D = 0 To mySize - 5 - C - B - A For E = 0 To mySize - 5 - D - C - B - A HTH, Bernie MS Excel MVP "Paul Black" wrote in message ups.com... Thanks Bernie, But how do I apply this to the existing code. Thanks in Advance. All the Best. Paul On Jul 11, 2:31 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: This would be a better loop control structu For A = 0 To mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B - A For D = 0 To mySize - 5 - C - B - A For E = 0 To mySize - 5 - D - C - B - A HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in l... Here's a better macro, with fewer iterations - but still LOTS... Bernie Private Sub Gaps4() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False ' myCol = 1 myRow = 1 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents For A = 0 To mySize - 5 ' If A < 0 Then MsgBox "Finished " & A & " out of " & mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B For D = 0 To mySize - 5 - C For E = 0 To mySize - 5 - D If mySize - 5 - A - B - C - D - E 0 Then strGap = Format(A, "00") & " " & _ Format(B, "00") & " " & _ Format(C, "00") & " " & _ Format(D, "00") & " " & _ Format(E, "00") & " Count = " & Format(mySize - 5 - A - B - C - D - E, "0") Cells(myRow, myCol).Value = strGap myRow = myRow + 1 If myRow = Rows.Count + 1 Then myRow = 1 myCol = myCol + 1 End If End If Next E Next D Next C Next B Next A Application.ScreenUpdating = True End Sub "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Paul, Try the macro below.... but try it first with a lower mySize number (see the comment). It may require a supercomputer to finish, or hours and hours... 13,983,816 combinations is a lot to process. Bernie Private Sub Gaps3() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False myCol = 1 myRow = 1 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents For A = 1 To mySize - 5 'If A < 1 Then MsgBox "Finished " & A - 1 & " out of " & mySize - 5 For B = A + 1 To mySize - 4 For C = B + 1 To mySize - 3 For D = C + 1 To mySize - 2 For E = D + 1 To mySize - 1 For F = E + 1 To mySize strGap = Format(B - A - 1, "00") & " " & _ Format(C - B - 1, "00") & " " & _ Format(D - C - 1, "00") & " " & _ Format(E - D - 1, "00") & " " & _ Format(F - E - 1, "00") Set myCell = Cells.Find(strGap, , , xlPart) If myCell Is Nothing Then Cells(myRow, myCol).Value = strGap & " Count = 1" myRow = myRow + 1 If myRow = Rows.Count + 1 Then myRow = 1 myCol = myCol + 1 End If Else myCell.Value = strGap & " Count = " & Format(Val(Trim( _ Replace(myCell.Value, strGap & " Count = ", ""))) + 1, "0") End If Next F Next E Next D Next C Next B Next A Application.ScreenUpdating = True End Sub "Paul Black" wrote in message roups.com... Hi Bernie, I think the confusion is me not being able to explain myself properly. I would like a list of the total combinations for each category of Gaps calculated for each combination. For example, taking the combinations below in a 6 from 49 Lotto as ... Combination 01 02 03 04 05 06 = Gap category 00 00 00 00 00 Combination 01 02 03 04 05 07 = Gap category 00 00 00 00 01 Combination 01 02 03 04 05 08 = Gap category 00 00 00 00 02 Combination 01 02 03 04 05 09 = Gap category 00 00 00 00 03 Combination 01 02 03 04 05 10 = Gap category 00 00 00 00 04 Combination 01 02 03 04 05 11 = Gap category 00 00 00 00 05 Combination 01 02 03 04 05 12 = Gap category 00 00 00 00 06 Combination 01 11 12 14 18 21 = Gap category 09 00 01 03 02 Combination 11 21 22 24 28 31 = Gap category 09 00 01 03 02 Combination 21 22 23 24 25 31 = Gap category 00 00 00 00 05 ... the category list and total combinations for the above will be ... Category 00 00 00 00 00 = total combinations of 1 ( One ) Category 00 00 00 00 01 = total combinations of 1 ( One ) Category 00 00 00 00 02 = total combinations of 3 ( Three ) Category 00 00 00 00 03 = total combinations of 1 ( One ) Category 00 00 00 00 04 = total combinations of 1 ( One ) Category 00 00 00 00 05 = total combinations of 2 ( Two ) Category 00 00 00 00 06 = total combinations of 1 ( One ) I would also like the total combinations at the end please, which of course should be 13,983,816 combinations. I hope this makes it a bit clearer. Thanks in Advance. All the Best. Paul On Jul 10, 9:50 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, Gaps of 43 5 69,919,080 69,919,080 / 5 =13,983,816 Bernie "Paul Black" wrote in message groups.com... Thanks Bernie for producing the code & thanks Tom for running it and listing the information for me, it is greatly appreciated. Have either of you had any thoughts on achieving my main request at all please, that is the part at the beginning of my initial post and goes down to and includes the paragraph ... It would also be nice to have a Total at the bottom of the combinations list, which of course, should equal 13,983,816 combinations. Thanks in Advance. All the Best. Paul On Jul 10, 6:24 pm, Tom Ogilvy wrote: I ran it, here are the results: Gaps Gaps of 00 8,561,520 Gaps of 01 7,669,695 Gaps of 02 6,853,770 Gaps of 03 6,108,795 Gaps of 04 5,430,040 Gaps of 05 4,812,990 Gaps of 06 4,253,340 Gaps of 07 3,746,990 Gaps of 08 3,290,040 Gaps of 09 2,878,785 Gaps of 10 2,509,710 Gaps of 11 2,179,485 Gaps of 12 1,884,960 Gaps of 13 1,623,160 Gaps of 14 1,391,280 Gaps of 15 1,186,680 Gaps of 16 1,006,880 Gaps of 17 849,555 Gaps of 18 712,530 Gaps of 19 593,775 Gaps of 20 491,400 Gaps of 21 403,650 Gaps of 22 328,900 Gaps of 23 265,650 Gaps of 24 212,520 Gaps of 25 168,245 Gaps of 26 131,670 Gaps of 27 101,745 Gaps of 28 77,520 Gaps of 29 58,140 Gaps of 30 42,840 Gaps of 31 30,940 Gaps of 32 21,840 Gaps of 33 15,015 Gaps of 34 10,010 Gaps of 35 6,435 Gaps of 36 3,960 Gaps of 37 2,310 Gaps of 38 1,260 Gaps of 39 630 Gaps of 40 280 Gaps of 41 105 Gaps of 42 30 Gaps of 43 5 69,919,080 -- Regards, Tom Ogilvy "Paul Black" wrote: Thanks for the code Bernie. I don't have access to Excel at the moment so am unable to test the code. Does the code apply to my original request or to my Private Sub Gaps() effort please. I assume it applies to my effort. It is my original request that is the most important one. Thanks in Advance. All the Best. Paul On Jul 10, 5:04 pm, Tom Ogilvy wrote: Whoops! mistook your explantion for part of your question <face red. Have to confess ... read more »- Hide quoted text - - Show quoted text - |
Calculating Gaps Between Numbers
Thanks Bernie,
I do not understand what you mean when you say ... The new version, Gaps4B (below Gaps2B) will produce the separate string / count columns, but it will still take up most of a worksheet - a single column won't hold the results. .... Does it mean that I need to add a Column OffSet or something?. Do you mean that there is too much data for the category list produced in Column "B" and for the Total combinations associated produced in Column "C". This makes the data more than 65,536 rows, is that correct. If so how can the code be adapted to cater for this?. Thanks again in Advance. All the Best. Paul On Jul 12, 2:12 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, The new version, Gaps2B (below), will produce the grand total formula at the bottom. The new version, Gaps4B (below Gaps2B) will produce the separate string / count columns, but it will still take up most of a worksheet - a single column won't hold the results HTH, Bernie MS Excel MVP Private Sub Gaps2B() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim GapsTotal(0 To 43) As Long Application.ScreenUpdating = False For i = 0 To 43 GapsTotal(i) = 0 Next i For A = 1 To 44 For B = A + 1 To 45 For C = B + 1 To 46 For D = C + 1 To 47 For E = D + 1 To 48 For F = E + 1 To 49 GapsTotal(B - A - 1) = GapsTotal(B - A - 1) + 1 GapsTotal(C - B - 1) = GapsTotal(C - B - 1) + 1 GapsTotal(D - C - 1) = GapsTotal(D - C - 1) + 1 GapsTotal(E - D - 1) = GapsTotal(E - D - 1) + 1 GapsTotal(F - E - 1) = GapsTotal(F - E - 1) + 1 Next F Next E Next D Next C Next B Next A Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" For i = 0 To 43 Sheets("Gaps Data").Cells(i + 3, 2).Value = "Gaps of " & Format(i, "00") Sheets("Gaps Data").Cells(i + 3, 3).Value = GapsTotal(i) Next i Sheets("Gaps Data").Cells(47, 2).Value = "Grand Total" Sheets("Gaps Data").Cells(47, 3).Formula = "=SUM(C3:C46)" Application.ScreenUpdating = True End Sub Private Sub Gaps4B() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False ' myCol = 1 myRow = 3 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents Cells(2, 1).Value = "Gap" Cells(2, 2).Value = "Total" For A = 0 To mySize - 5 ' If A < 0 Then MsgBox "Finished " & A & " out of " & mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B - A For D = 0 To mySize - 5 - C - B - A For E = 0 To mySize - 5 - D - C - B - A If mySize - 5 - A - B - C - D - E 0 Then strGap = Format(A, "00") & " " & _ Format(B, "00") & " " & _ Format(C, "00") & " " & _ Format(D, "00") & " " & _ Format(E, "00") Cells(myRow, myCol).Value = strGap Cells(myRow, myCol + 1).Value = mySize - 5 - A - B - C - D - E myRow = myRow + 1 If myRow = Rows.Count + 1 Then myRow = 3 myCol = myCol + 2 Cells(2, myCol).Value = "Gap ID" Cells(2, myCol + 1).Value = "Count" End If End If Next E Next D Next C Next B Next A Application.ScreenUpdating = True End Sub "Paul Black" wrote in message ps.com... Bernie, thanks ever so much for all the time and effort you have given to this, it is appreciated. Just a couple of things though please. I am going over to see my niece this evening so I will be able to run the two Subs you provided below ( the second one has been updated according to your last response ). Firstly, for Private Sub Gaps2(), will it possible to have the Grand Total for ALL the combinations produced in column "C" please . This figure will be 69,919,080 as produced by Mr. Tom Ogilvy when he kindly ran the Sub for me and will go after the last entry in Column "C". Secondly, will it be possible for Private Sub Gaps4() to have ... Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" ... and the categories list starting in Cell "B3" and the Total combinations associated with each category starting in Cell "C3" like ... Column "B" Column "C" 00 00 00 00 01 xxx,xxx,xxx 00 00 08 03 02 xxx,xxx,xxx 09 08 02 02 03 xxx,xxx,xxx ... for example. Finally, will it also be possible to have the Grand Total for ALL the combinations produced in column "C" please, this will go after the last entry in Column "C". Private Sub Gaps2() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim GapsTotal(0 To 43) As Long Application.ScreenUpdating = False For i = 0 To 43 GapsTotal(i) = 0 Next i For A = 1 To 44 For B = A + 1 To 45 For C = B + 1 To 46 For D = C + 1 To 47 For E = D + 1 To 48 For F = E + 1 To 49 GapsTotal(B - A - 1) = GapsTotal(B - A - 1) + 1 GapsTotal(C - B - 1) = GapsTotal(C - B - 1) + 1 GapsTotal(D - C - 1) = GapsTotal(D - C - 1) + 1 GapsTotal(E - D - 1) = GapsTotal(E - D - 1) + 1 GapsTotal(F - E - 1) = GapsTotal(F - E - 1) + 1 Next F Next E Next D Next C Next B Next A Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" For i = 0 To 43 Sheets("Gaps Data").Cells(i + 3, 2).Value = "Gaps of " & Format(i, "00") Sheets("Gaps Data").Cells(i + 3, 3).Value = GapsTotal(i) Next i Application.ScreenUpdating = True End Sub --------------------------------------------------------------------- Private Sub Gaps4() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False ' myCol = 1 myRow = 1 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents For A = 0 To mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B - A For D = 0 To mySize - 5 - C - B - A For E = 0 To mySize - 5 - D - C - B - A If mySize - 5 - A - B - C - D - E 0 Then strGap = Format(A, "00") & " " & _ Format(B, "00") & " " & _ Format(C, "00") & " " & _ Format(D, "00") & " " & _ Format(E, "00") & " Count = " & Format(mySize - 5 - A - B - C - D - E, "0") Cells(myRow, myCol).Value = strGap myRow = myRow + 1 If myRow = Rows.Count + 1 Then myRow = 1 myCol = myCol + 1 End If End If Next E Next D Next C Next B Next A Application.ScreenUpdating = True End Sub I will then be able to run them later as I said. Thanks in Advance. All the Best. Paul On Jul 11, 4:34 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, Replace this: For A = 0 To mySize - 5 ' If A < 0 Then MsgBox "Finished " & A & " out of " & mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B For D = 0 To mySize - 5 - C For E = 0 To mySize - 5 - D with this For A = 0 To mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B - A For D = 0 To mySize - 5 - C - B - A For E = 0 To mySize - 5 - D - C - B - A HTH, Bernie MS Excel MVP "Paul Black" wrote in message oups.com... Thanks Bernie, But how do I apply this to the existing code. Thanks in Advance. All the Best. Paul On Jul 11, 2:31 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: This would be a better loop control structu For A = 0 To mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B - A For D = 0 To mySize - 5 - C - B - A For E = 0 To mySize - 5 - D - C - B - A HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in l... Here's a better macro, with fewer iterations - but still LOTS... Bernie Private Sub Gaps4() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False ' myCol = 1 myRow = 1 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents For A = 0 To mySize - 5 ' If A < 0 Then MsgBox "Finished " & A & " out of " & mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B For D = 0 To mySize - 5 - C For E = 0 To mySize - 5 - D If mySize - 5 - A - B - C - D - E 0 Then strGap = Format(A, "00") & " " & _ Format(B, "00") & " " & _ Format(C, "00") & " " & _ Format(D, "00") & " " & _ Format(E, "00") & " Count = " & Format(mySize - 5 - A - B - C - D - E, "0") Cells(myRow, myCol).Value = strGap myRow = myRow + 1 If myRow = Rows.Count + 1 Then myRow = 1 myCol = myCol + 1 End If End If Next E Next D Next C Next B Next A Application.ScreenUpdating = True End Sub "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Paul, Try the macro below.... but try it first with a lower mySize number (see the comment). It may require a supercomputer to finish, or hours and hours... 13,983,816 combinations is a lot to process. Bernie Private Sub Gaps3() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False myCol = 1 myRow = 1 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents For A = 1 To mySize - 5 'If A < 1 Then MsgBox "Finished " & A - 1 & " out of " & mySize - 5 For B = A + 1 To mySize - 4 For C = B + 1 To mySize - 3 For D = C + 1 To mySize - 2 For E = D + 1 To mySize - 1 For F = E + 1 To mySize strGap = Format(B - ... read more »- Hide quoted text - - Show quoted text - |
Calculating Gaps Between Numbers
Paul,
The code is written to account for that already... I have been running the code on another machine for the last hour or so, and it is up to column AX - and should soon be done. This part of the code myRow = myRow + 1 If myRow = Rows.Count + 1 Then myRow = 3 myCol = myCol + 2 accounts for the needed change in column (to move the data write over two columns) and row (to move the data writing back to the top of the column) Just run it... but give it lots of time... HTH, Bernie MS Excel MVP "Paul Black" wrote in message oups.com... Thanks Bernie, I do not understand what you mean when you say ... The new version, Gaps4B (below Gaps2B) will produce the separate string / count columns, but it will still take up most of a worksheet - a single column won't hold the results. .... Does it mean that I need to add a Column OffSet or something?. Do you mean that there is too much data for the category list produced in Column "B" and for the Total combinations associated produced in Column "C". This makes the data more than 65,536 rows, is that correct. If so how can the code be adapted to cater for this?. Thanks again in Advance. All the Best. Paul On Jul 12, 2:12 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, The new version, Gaps2B (below), will produce the grand total formula at the bottom. The new version, Gaps4B (below Gaps2B) will produce the separate string / count columns, but it will still take up most of a worksheet - a single column won't hold the results HTH, Bernie MS Excel MVP Private Sub Gaps2B() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim GapsTotal(0 To 43) As Long Application.ScreenUpdating = False For i = 0 To 43 GapsTotal(i) = 0 Next i For A = 1 To 44 For B = A + 1 To 45 For C = B + 1 To 46 For D = C + 1 To 47 For E = D + 1 To 48 For F = E + 1 To 49 GapsTotal(B - A - 1) = GapsTotal(B - A - 1) + 1 GapsTotal(C - B - 1) = GapsTotal(C - B - 1) + 1 GapsTotal(D - C - 1) = GapsTotal(D - C - 1) + 1 GapsTotal(E - D - 1) = GapsTotal(E - D - 1) + 1 GapsTotal(F - E - 1) = GapsTotal(F - E - 1) + 1 Next F Next E Next D Next C Next B Next A Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" For i = 0 To 43 Sheets("Gaps Data").Cells(i + 3, 2).Value = "Gaps of " & Format(i, "00") Sheets("Gaps Data").Cells(i + 3, 3).Value = GapsTotal(i) Next i Sheets("Gaps Data").Cells(47, 2).Value = "Grand Total" Sheets("Gaps Data").Cells(47, 3).Formula = "=SUM(C3:C46)" Application.ScreenUpdating = True End Sub Private Sub Gaps4B() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False ' myCol = 1 myRow = 3 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents Cells(2, 1).Value = "Gap" Cells(2, 2).Value = "Total" For A = 0 To mySize - 5 ' If A < 0 Then MsgBox "Finished " & A & " out of " & mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B - A For D = 0 To mySize - 5 - C - B - A For E = 0 To mySize - 5 - D - C - B - A If mySize - 5 - A - B - C - D - E 0 Then strGap = Format(A, "00") & " " & _ Format(B, "00") & " " & _ Format(C, "00") & " " & _ Format(D, "00") & " " & _ Format(E, "00") Cells(myRow, myCol).Value = strGap Cells(myRow, myCol + 1).Value = mySize - 5 - A - B - C - D - E myRow = myRow + 1 If myRow = Rows.Count + 1 Then myRow = 3 myCol = myCol + 2 Cells(2, myCol).Value = "Gap ID" Cells(2, myCol + 1).Value = "Count" End If End If Next E Next D Next C Next B Next A Application.ScreenUpdating = True End Sub "Paul Black" wrote in message ps.com... Bernie, thanks ever so much for all the time and effort you have given to this, it is appreciated. Just a couple of things though please. I am going over to see my niece this evening so I will be able to run the two Subs you provided below ( the second one has been updated according to your last response ). Firstly, for Private Sub Gaps2(), will it possible to have the Grand Total for ALL the combinations produced in column "C" please . This figure will be 69,919,080 as produced by Mr. Tom Ogilvy when he kindly ran the Sub for me and will go after the last entry in Column "C". Secondly, will it be possible for Private Sub Gaps4() to have ... Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" ... and the categories list starting in Cell "B3" and the Total combinations associated with each category starting in Cell "C3" like ... Column "B" Column "C" 00 00 00 00 01 xxx,xxx,xxx 00 00 08 03 02 xxx,xxx,xxx 09 08 02 02 03 xxx,xxx,xxx ... for example. Finally, will it also be possible to have the Grand Total for ALL the combinations produced in column "C" please, this will go after the last entry in Column "C". Private Sub Gaps2() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim GapsTotal(0 To 43) As Long Application.ScreenUpdating = False For i = 0 To 43 GapsTotal(i) = 0 Next i For A = 1 To 44 For B = A + 1 To 45 For C = B + 1 To 46 For D = C + 1 To 47 For E = D + 1 To 48 For F = E + 1 To 49 GapsTotal(B - A - 1) = GapsTotal(B - A - 1) + 1 GapsTotal(C - B - 1) = GapsTotal(C - B - 1) + 1 GapsTotal(D - C - 1) = GapsTotal(D - C - 1) + 1 GapsTotal(E - D - 1) = GapsTotal(E - D - 1) + 1 GapsTotal(F - E - 1) = GapsTotal(F - E - 1) + 1 Next F Next E Next D Next C Next B Next A Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" For i = 0 To 43 Sheets("Gaps Data").Cells(i + 3, 2).Value = "Gaps of " & Format(i, "00") Sheets("Gaps Data").Cells(i + 3, 3).Value = GapsTotal(i) Next i Application.ScreenUpdating = True End Sub --------------------------------------------------------------------- Private Sub Gaps4() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False ' myCol = 1 myRow = 1 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents For A = 0 To mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B - A For D = 0 To mySize - 5 - C - B - A For E = 0 To mySize - 5 - D - C - B - A If mySize - 5 - A - B - C - D - E 0 Then strGap = Format(A, "00") & " " & _ Format(B, "00") & " " & _ Format(C, "00") & " " & _ Format(D, "00") & " " & _ Format(E, "00") & " Count = " & Format(mySize - 5 - A - B - C - D - E, "0") Cells(myRow, myCol).Value = strGap myRow = myRow + 1 If myRow = Rows.Count + 1 Then myRow = 1 myCol = myCol + 1 End If End If Next E Next D Next C Next B Next A Application.ScreenUpdating = True End Sub I will then be able to run them later as I said. Thanks in Advance. All the Best. Paul On Jul 11, 4:34 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, Replace this: For A = 0 To mySize - 5 ' If A < 0 Then MsgBox "Finished " & A & " out of " & mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B For D = 0 To mySize - 5 - C For E = 0 To mySize - 5 - D with this For A = 0 To mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B - A For D = 0 To mySize - 5 - C - B - A For E = 0 To mySize - 5 - D - C - B - A HTH, Bernie MS Excel MVP "Paul Black" wrote in message oups.com... Thanks Bernie, But how do I apply this to the existing code. Thanks in Advance. All the Best. Paul On Jul 11, 2:31 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: This would be a better loop control structu For A = 0 To mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B - A For D = 0 To mySize - 5 - C - B - A For E = 0 To mySize - 5 - D - C - B - A HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in l... Here's a better macro, with fewer iterations - but still LOTS... Bernie Private Sub Gaps4() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False ' myCol = 1 myRow = 1 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents For A = 0 To mySize - 5 ' If A < 0 Then MsgBox "Finished " & A & " out of " & mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B For D = 0 To mySize - 5 - C For E = 0 To mySize - 5 - D If mySize - 5 - A - B - C - D - E 0 Then strGap = Format(A, "00") & " " & _ Format(B, "00") & " " & _ Format(C, "00") & " " & _ Format(D, "00") & " " & _ Format(E, "00") & " Count = " & Format(mySize - 5 - A - B - C - D - E, "0") Cells(myRow, myCol).Value = strGap myRow = myRow + 1 If myRow = Rows.Count + 1 Then myRow = 1 myCol = myCol + 1 End If End If Next E Next D Next C Next B Next A Application.ScreenUpdating = True End Sub "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Paul, Try the macro below.... but try it first with a lower mySize number (see the comment). It may require a supercomputer to finish, or hours and hours... 13,983,816 combinations is a lot to process. Bernie Private Sub Gaps3() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False myCol = 1 myRow = 1 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents For A = 1 To mySize - 5 'If A < 1 Then MsgBox "Finished " & A - 1 & " out of " & mySize - 5 For B = A + 1 To mySize - 4 For C = B + 1 To mySize - 3 For D = C + 1 To mySize - 2 For E = D + 1 To mySize - 1 For F = E + 1 To mySize strGap = Format(B - ... read more »- Hide quoted text - - Show quoted text - |
Calculating Gaps Between Numbers
Paul,
It's finished - 1,712,304 unique combinations of gaps listed, out to column BB (two columns per record set), with a resulting file size of 77 meg. HTH, Bernie MS Excel MVP "Paul Black" wrote in message oups.com... Thanks Bernie, I do not understand what you mean when you say ... The new version, Gaps4B (below Gaps2B) will produce the separate string / count columns, but it will still take up most of a worksheet - a single column won't hold the results. .... Does it mean that I need to add a Column OffSet or something?. Do you mean that there is too much data for the category list produced in Column "B" and for the Total combinations associated produced in Column "C". This makes the data more than 65,536 rows, is that correct. If so how can the code be adapted to cater for this?. Thanks again in Advance. All the Best. Paul On Jul 12, 2:12 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, The new version, Gaps2B (below), will produce the grand total formula at the bottom. The new version, Gaps4B (below Gaps2B) will produce the separate string / count columns, but it will still take up most of a worksheet - a single column won't hold the results HTH, Bernie MS Excel MVP Private Sub Gaps2B() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim GapsTotal(0 To 43) As Long Application.ScreenUpdating = False For i = 0 To 43 GapsTotal(i) = 0 Next i For A = 1 To 44 For B = A + 1 To 45 For C = B + 1 To 46 For D = C + 1 To 47 For E = D + 1 To 48 For F = E + 1 To 49 GapsTotal(B - A - 1) = GapsTotal(B - A - 1) + 1 GapsTotal(C - B - 1) = GapsTotal(C - B - 1) + 1 GapsTotal(D - C - 1) = GapsTotal(D - C - 1) + 1 GapsTotal(E - D - 1) = GapsTotal(E - D - 1) + 1 GapsTotal(F - E - 1) = GapsTotal(F - E - 1) + 1 Next F Next E Next D Next C Next B Next A Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" For i = 0 To 43 Sheets("Gaps Data").Cells(i + 3, 2).Value = "Gaps of " & Format(i, "00") Sheets("Gaps Data").Cells(i + 3, 3).Value = GapsTotal(i) Next i Sheets("Gaps Data").Cells(47, 2).Value = "Grand Total" Sheets("Gaps Data").Cells(47, 3).Formula = "=SUM(C3:C46)" Application.ScreenUpdating = True End Sub Private Sub Gaps4B() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False ' myCol = 1 myRow = 3 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents Cells(2, 1).Value = "Gap" Cells(2, 2).Value = "Total" For A = 0 To mySize - 5 ' If A < 0 Then MsgBox "Finished " & A & " out of " & mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B - A For D = 0 To mySize - 5 - C - B - A For E = 0 To mySize - 5 - D - C - B - A If mySize - 5 - A - B - C - D - E 0 Then strGap = Format(A, "00") & " " & _ Format(B, "00") & " " & _ Format(C, "00") & " " & _ Format(D, "00") & " " & _ Format(E, "00") Cells(myRow, myCol).Value = strGap Cells(myRow, myCol + 1).Value = mySize - 5 - A - B - C - D - E myRow = myRow + 1 If myRow = Rows.Count + 1 Then myRow = 3 myCol = myCol + 2 Cells(2, myCol).Value = "Gap ID" Cells(2, myCol + 1).Value = "Count" End If End If Next E Next D Next C Next B Next A Application.ScreenUpdating = True End Sub "Paul Black" wrote in message ps.com... Bernie, thanks ever so much for all the time and effort you have given to this, it is appreciated. Just a couple of things though please. I am going over to see my niece this evening so I will be able to run the two Subs you provided below ( the second one has been updated according to your last response ). Firstly, for Private Sub Gaps2(), will it possible to have the Grand Total for ALL the combinations produced in column "C" please . This figure will be 69,919,080 as produced by Mr. Tom Ogilvy when he kindly ran the Sub for me and will go after the last entry in Column "C". Secondly, will it be possible for Private Sub Gaps4() to have ... Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" ... and the categories list starting in Cell "B3" and the Total combinations associated with each category starting in Cell "C3" like ... Column "B" Column "C" 00 00 00 00 01 xxx,xxx,xxx 00 00 08 03 02 xxx,xxx,xxx 09 08 02 02 03 xxx,xxx,xxx ... for example. Finally, will it also be possible to have the Grand Total for ALL the combinations produced in column "C" please, this will go after the last entry in Column "C". Private Sub Gaps2() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim GapsTotal(0 To 43) As Long Application.ScreenUpdating = False For i = 0 To 43 GapsTotal(i) = 0 Next i For A = 1 To 44 For B = A + 1 To 45 For C = B + 1 To 46 For D = C + 1 To 47 For E = D + 1 To 48 For F = E + 1 To 49 GapsTotal(B - A - 1) = GapsTotal(B - A - 1) + 1 GapsTotal(C - B - 1) = GapsTotal(C - B - 1) + 1 GapsTotal(D - C - 1) = GapsTotal(D - C - 1) + 1 GapsTotal(E - D - 1) = GapsTotal(E - D - 1) + 1 GapsTotal(F - E - 1) = GapsTotal(F - E - 1) + 1 Next F Next E Next D Next C Next B Next A Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" For i = 0 To 43 Sheets("Gaps Data").Cells(i + 3, 2).Value = "Gaps of " & Format(i, "00") Sheets("Gaps Data").Cells(i + 3, 3).Value = GapsTotal(i) Next i Application.ScreenUpdating = True End Sub --------------------------------------------------------------------- Private Sub Gaps4() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False ' myCol = 1 myRow = 1 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents For A = 0 To mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B - A For D = 0 To mySize - 5 - C - B - A For E = 0 To mySize - 5 - D - C - B - A If mySize - 5 - A - B - C - D - E 0 Then strGap = Format(A, "00") & " " & _ Format(B, "00") & " " & _ Format(C, "00") & " " & _ Format(D, "00") & " " & _ Format(E, "00") & " Count = " & Format(mySize - 5 - A - B - C - D - E, "0") Cells(myRow, myCol).Value = strGap myRow = myRow + 1 If myRow = Rows.Count + 1 Then myRow = 1 myCol = myCol + 1 End If End If Next E Next D Next C Next B Next A Application.ScreenUpdating = True End Sub I will then be able to run them later as I said. Thanks in Advance. All the Best. Paul On Jul 11, 4:34 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, Replace this: For A = 0 To mySize - 5 ' If A < 0 Then MsgBox "Finished " & A & " out of " & mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B For D = 0 To mySize - 5 - C For E = 0 To mySize - 5 - D with this For A = 0 To mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B - A For D = 0 To mySize - 5 - C - B - A For E = 0 To mySize - 5 - D - C - B - A HTH, Bernie MS Excel MVP "Paul Black" wrote in message oups.com... Thanks Bernie, But how do I apply this to the existing code. Thanks in Advance. All the Best. Paul On Jul 11, 2:31 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: This would be a better loop control structu For A = 0 To mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B - A For D = 0 To mySize - 5 - C - B - A For E = 0 To mySize - 5 - D - C - B - A HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in l... Here's a better macro, with fewer iterations - but still LOTS... Bernie Private Sub Gaps4() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False ' myCol = 1 myRow = 1 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents For A = 0 To mySize - 5 ' If A < 0 Then MsgBox "Finished " & A & " out of " & mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B For D = 0 To mySize - 5 - C For E = 0 To mySize - 5 - D If mySize - 5 - A - B - C - D - E 0 Then strGap = Format(A, "00") & " " & _ Format(B, "00") & " " & _ Format(C, "00") & " " & _ Format(D, "00") & " " & _ Format(E, "00") & " Count = " & Format(mySize - 5 - A - B - C - D - E, "0") Cells(myRow, myCol).Value = strGap myRow = myRow + 1 If myRow = Rows.Count + 1 Then myRow = 1 myCol = myCol + 1 End If End If Next E Next D Next C Next B Next A Application.ScreenUpdating = True End Sub "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Paul, Try the macro below.... but try it first with a lower mySize number (see the comment). It may require a supercomputer to finish, or hours and hours... 13,983,816 combinations is a lot to process. Bernie Private Sub Gaps3() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False myCol = 1 myRow = 1 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents For A = 1 To mySize - 5 'If A < 1 Then MsgBox "Finished " & A - 1 & " out of " & mySize - 5 For B = A + 1 To mySize - 4 For C = B + 1 To mySize - 3 For D = C + 1 To mySize - 2 For E = D + 1 To mySize - 1 For F = E + 1 To mySize strGap = Format(B - ... read more »- Hide quoted text - - Show quoted text - |
Calculating Gaps Between Numbers
Brilliant Bernie,
I ran both the Subs and got the results I was after, thank you so much. Is there a way to adapt the Sub Gaps2B() code to make the Grand Total float so to speak according to the categories and the Total combinations produced. If I was to change the maximum number of balls from 49 to say 36 for arguments sake, the Grand Total will still appear in Cell "C47" as it does now, leaving a gap of several blank cells above before the last Gaps Total entry. I know I can change the ... Dim GapsTotal(0 To 43) As Long .... and ... For A = 1 To 44 For B = A + 1 To 45 For C = B + 1 To 46 For D = C + 1 To 47 For E = D + 1 To 48 For F = E + 1 To 49 .... and ... For i = 0 To 43 Sheets("Gaps Data").Cells(i + 3, 2).Value = "Gaps of " & Format(i, "00") Sheets("Gaps Data").Cells(i + 3, 3).Value = GapsTotal(i) Next i .... figure of 43 to whatever. Is there a way so I don't have to specify the number 0 to 43 in the Array but it works it out for you?. Could possibly the LBound and UBound be used so the values do not have to be hard coded?. The Sub Gaps4B() works perfectly except for the fact that there are no thousand seperators for the Total combinations for each category and there is no Grand Total which should equal 39,983,816 combinations. Would ... Cells(myRow, myCol + 1).Value = Format(mySize - 5 - A - B - C - D - E, "0,000,000") .... sort out the thousands seperator?. Thanks in Advance. All the Best. Paul On Jul 12, 4:25 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, It's finished - 1,712,304 unique combinations of gaps listed, out to column BB (two columns per record set), with a resulting file size of 77 meg. HTH, Bernie MS Excel MVP "Paul Black" wrote in message oups.com... Thanks Bernie, I do not understand what you mean when you say ... The new version, Gaps4B (below Gaps2B) will produce the separate string / count columns, but it will still take up most of a worksheet - a single column won't hold the results. ... Does it mean that I need to add a Column OffSet or something?. Do you mean that there is too much data for the category list produced in Column "B" and for the Total combinations associated produced in Column "C". This makes the data more than 65,536 rows, is that correct. If so how can the code be adapted to cater for this?. Thanks again in Advance. All the Best. Paul On Jul 12, 2:12 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, The new version, Gaps2B (below), will produce the grand total formula at the bottom. The new version, Gaps4B (below Gaps2B) will produce the separate string / count columns, but it will still take up most of a worksheet - a single column won't hold the results HTH, Bernie MS Excel MVP Private Sub Gaps2B() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim GapsTotal(0 To 43) As Long Application.ScreenUpdating = False For i = 0 To 43 GapsTotal(i) = 0 Next i For A = 1 To 44 For B = A + 1 To 45 For C = B + 1 To 46 For D = C + 1 To 47 For E = D + 1 To 48 For F = E + 1 To 49 GapsTotal(B - A - 1) = GapsTotal(B - A - 1) + 1 GapsTotal(C - B - 1) = GapsTotal(C - B - 1) + 1 GapsTotal(D - C - 1) = GapsTotal(D - C - 1) + 1 GapsTotal(E - D - 1) = GapsTotal(E - D - 1) + 1 GapsTotal(F - E - 1) = GapsTotal(F - E - 1) + 1 Next F Next E Next D Next C Next B Next A Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" For i = 0 To 43 Sheets("Gaps Data").Cells(i + 3, 2).Value = "Gaps of " & Format(i, "00") Sheets("Gaps Data").Cells(i + 3, 3).Value = GapsTotal(i) Next i Sheets("Gaps Data").Cells(47, 2).Value = "Grand Total" Sheets("Gaps Data").Cells(47, 3).Formula = "=SUM(C3:C46)" Application.ScreenUpdating = True End Sub Private Sub Gaps4B() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False ' myCol = 1 myRow = 3 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents Cells(2, 1).Value = "Gap" Cells(2, 2).Value = "Total" For A = 0 To mySize - 5 ' If A < 0 Then MsgBox "Finished " & A & " out of " & mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B - A For D = 0 To mySize - 5 - C - B - A For E = 0 To mySize - 5 - D - C - B - A If mySize - 5 - A - B - C - D - E 0 Then strGap = Format(A, "00") & " " & _ Format(B, "00") & " " & _ Format(C, "00") & " " & _ Format(D, "00") & " " & _ Format(E, "00") Cells(myRow, myCol).Value = strGap Cells(myRow, myCol + 1).Value = mySize - 5 - A - B - C - D - E myRow = myRow + 1 If myRow = Rows.Count + 1 Then myRow = 3 myCol = myCol + 2 Cells(2, myCol).Value = "Gap ID" Cells(2, myCol + 1).Value = "Count" End If End If Next E Next D Next C Next B Next A Application.ScreenUpdating = True End Sub "Paul Black" wrote in message ups.com... Bernie, thanks ever so much for all the time and effort you have given to this, it is appreciated. Just a couple of things though please. I am going over to see my niece this evening so I will be able to run the two Subs you provided below ( the second one has been updated according to your last response ). Firstly, for Private Sub Gaps2(), will it possible to have the Grand Total for ALL the combinations produced in column "C" please . This figure will be 69,919,080 as produced by Mr. Tom Ogilvy when he kindly ran the Sub for me and will go after the last entry in Column "C". Secondly, will it be possible for Private Sub Gaps4() to have ... Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" ... and the categories list starting in Cell "B3" and the Total combinations associated with each category starting in Cell "C3" like ... Column "B" Column "C" 00 00 00 00 01 xxx,xxx,xxx 00 00 08 03 02 xxx,xxx,xxx 09 08 02 02 03 xxx,xxx,xxx ... for example. Finally, will it also be possible to have the Grand Total for ALL the combinations produced in column "C" please, this will go after the last entry in Column "C". Private Sub Gaps2() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim GapsTotal(0 To 43) As Long Application.ScreenUpdating = False For i = 0 To 43 GapsTotal(i) = 0 Next i For A = 1 To 44 For B = A + 1 To 45 For C = B + 1 To 46 For D = C + 1 To 47 For E = D + 1 To 48 For F = E + 1 To 49 GapsTotal(B - A - 1) = GapsTotal(B - A - 1) + 1 GapsTotal(C - B - 1) = GapsTotal(C - B - 1) + 1 GapsTotal(D - C - 1) = GapsTotal(D - C - 1) + 1 GapsTotal(E - D - 1) = GapsTotal(E - D - 1) + 1 GapsTotal(F - E - 1) = GapsTotal(F - E - 1) + 1 Next F Next E Next D Next C Next B Next A Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" For i = 0 To 43 Sheets("Gaps Data").Cells(i + 3, 2).Value = "Gaps of " & Format(i, "00") Sheets("Gaps Data").Cells(i + 3, 3).Value = GapsTotal(i) Next i Application.ScreenUpdating = True End Sub --------------------------------------------------------------------- Private Sub Gaps4() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False ' myCol = 1 myRow = 1 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents For A = 0 To mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B - A For D = 0 To mySize - 5 - C - B - A For E = 0 To mySize - 5 - D - C - B - A If mySize - 5 - A - B - C - D - E 0 Then strGap = Format(A, "00") & " " & _ Format(B, "00") & " " & _ Format(C, "00") & " " & _ Format(D, "00") & " " & _ Format(E, "00") & " Count = " & Format(mySize - 5 - A - B - C - D - E, "0") Cells(myRow, myCol).Value = strGap myRow = myRow + 1 If myRow = Rows.Count + 1 Then myRow = 1 myCol = myCol + 1 End If End If Next E Next D Next C Next B Next A Application.ScreenUpdating = True End Sub I will then be able to run them later as I said. Thanks in Advance. All the Best. Paul On Jul 11, 4:34 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, Replace this: For A = 0 To mySize - 5 ' If A < 0 Then MsgBox "Finished " & A & " out of " & mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B For D = 0 To mySize - 5 - C For E = 0 To mySize - 5 - D with this For A = 0 To mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B - A For D = 0 To mySize - 5 - C - B - A For E = 0 To mySize - 5 - D - C - B - A HTH, Bernie MS Excel MVP "Paul Black" wrote in message oups.com... Thanks Bernie, But how do I apply this to the existing code. Thanks in Advance. All the Best. Paul On Jul 11, 2:31 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: This would be a better loop control structu For A = 0 To mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B - A For D = 0 To mySize - 5 - C - B - A For E = 0 To mySize - 5 - D - C - B - A HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in l... Here's a better macro, with fewer iterations - but still LOTS... Bernie Private Sub Gaps4() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False ' myCol = 1 myRow = 1 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents For A = 0 To mySize - 5 ' If A < 0 Then MsgBox "Finished " & A & " out of " & mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B For D = 0 To mySize - 5 - C For E = 0 To mySize - 5 - D If mySize - 5 - A - B - C - D - E 0 Then strGap = Format(A, "00") & " " & _ Format(B, "00") & " " & _ Format(C, "00") & " " & _ Format(D, "00") & " " & _ ... read more »- Hide quoted text - - Show quoted text - |
Calculating Gaps Between Numbers
Paul,
For the first part, try the version below. For the second part, I think you are confused as to how many times each of the GAP strings actually appear. The maximum value is 44, which clearly doesn't need to be comma separated. The grand total ("Grand Total which should equal 39,983,816 ") is actually 13,983,816 - and note that there are 1,712,304 different combinations, for a count of about 8 1/6 per gap string on average. In my last message I wrote: "It's finished - 1,712,304 unique combinations of gaps listed, out to column BB (two columns per record set)" You can calculate that by simply using =SUM(A:BB) in a cell in column BC. HTH, Bernie Private Sub GapsVariable() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim mySize As Integer Dim GapsTotal() As Long mySize = 49 ' Change the size here ReDim GapsTotal(0 To mySize - 6) Application.ScreenUpdating = False For i = 0 To UBound(GapsTotal) GapsTotal(i) = 0 Next i For A = 1 To mySize - 5 For B = A + 1 To mySize - 4 For C = B + 1 To mySize - 3 For D = C + 1 To mySize - 2 For E = D + 1 To mySize - 1 For F = E + 1 To mySize GapsTotal(B - A - 1) = GapsTotal(B - A - 1) + 1 GapsTotal(C - B - 1) = GapsTotal(C - B - 1) + 1 GapsTotal(D - C - 1) = GapsTotal(D - C - 1) + 1 GapsTotal(E - D - 1) = GapsTotal(E - D - 1) + 1 GapsTotal(F - E - 1) = GapsTotal(F - E - 1) + 1 Next F Next E Next D Next C Next B Next A Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" For i = 0 To UBound(GapsTotal) Sheets("Gaps Data").Cells(i + 3, 2).Value = "Gaps of " & Format(i, "00") Sheets("Gaps Data").Cells(i + 3, 3).Value = GapsTotal(i) Next i i = UBound(GapsTotal) + 4 Sheets("Gaps Data").Cells(i, 2).Value = "Grand Total" Sheets("Gaps Data").Cells(i, 3).Formula = "=SUM(C3:C" & UBound(GapsTotal) + 3 & ")" Application.ScreenUpdating = True End Sub "Paul Black" wrote in message ups.com... Brilliant Bernie, I ran both the Subs and got the results I was after, thank you so much. Is there a way to adapt the Sub Gaps2B() code to make the Grand Total float so to speak according to the categories and the Total combinations produced. If I was to change the maximum number of balls from 49 to say 36 for arguments sake, the Grand Total will still appear in Cell "C47" as it does now, leaving a gap of several blank cells above before the last Gaps Total entry. I know I can change the ... Dim GapsTotal(0 To 43) As Long .... and ... For A = 1 To 44 For B = A + 1 To 45 For C = B + 1 To 46 For D = C + 1 To 47 For E = D + 1 To 48 For F = E + 1 To 49 .... and ... For i = 0 To 43 Sheets("Gaps Data").Cells(i + 3, 2).Value = "Gaps of " & Format(i, "00") Sheets("Gaps Data").Cells(i + 3, 3).Value = GapsTotal(i) Next i .... figure of 43 to whatever. Is there a way so I don't have to specify the number 0 to 43 in the Array but it works it out for you?. Could possibly the LBound and UBound be used so the values do not have to be hard coded?. The Sub Gaps4B() works perfectly except for the fact that there are no thousand seperators for the Total combinations for each category and there is no Grand Total which should equal 39,983,816 combinations. Would ... Cells(myRow, myCol + 1).Value = Format(mySize - 5 - A - B - C - D - E, "0,000,000") .... sort out the thousands seperator?. Thanks in Advance. All the Best. Paul On Jul 12, 4:25 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, It's finished - 1,712,304 unique combinations of gaps listed, out to column BB (two columns per record set), with a resulting file size of 77 meg. HTH, Bernie MS Excel MVP "Paul Black" wrote in message oups.com... Thanks Bernie, I do not understand what you mean when you say ... The new version, Gaps4B (below Gaps2B) will produce the separate string / count columns, but it will still take up most of a worksheet - a single column won't hold the results. ... Does it mean that I need to add a Column OffSet or something?. Do you mean that there is too much data for the category list produced in Column "B" and for the Total combinations associated produced in Column "C". This makes the data more than 65,536 rows, is that correct. If so how can the code be adapted to cater for this?. Thanks again in Advance. All the Best. Paul On Jul 12, 2:12 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, The new version, Gaps2B (below), will produce the grand total formula at the bottom. The new version, Gaps4B (below Gaps2B) will produce the separate string / count columns, but it will still take up most of a worksheet - a single column won't hold the results HTH, Bernie MS Excel MVP Private Sub Gaps2B() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim GapsTotal(0 To 43) As Long Application.ScreenUpdating = False For i = 0 To 43 GapsTotal(i) = 0 Next i For A = 1 To 44 For B = A + 1 To 45 For C = B + 1 To 46 For D = C + 1 To 47 For E = D + 1 To 48 For F = E + 1 To 49 GapsTotal(B - A - 1) = GapsTotal(B - A - 1) + 1 GapsTotal(C - B - 1) = GapsTotal(C - B - 1) + 1 GapsTotal(D - C - 1) = GapsTotal(D - C - 1) + 1 GapsTotal(E - D - 1) = GapsTotal(E - D - 1) + 1 GapsTotal(F - E - 1) = GapsTotal(F - E - 1) + 1 Next F Next E Next D Next C Next B Next A Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" For i = 0 To 43 Sheets("Gaps Data").Cells(i + 3, 2).Value = "Gaps of " & Format(i, "00") Sheets("Gaps Data").Cells(i + 3, 3).Value = GapsTotal(i) Next i Sheets("Gaps Data").Cells(47, 2).Value = "Grand Total" Sheets("Gaps Data").Cells(47, 3).Formula = "=SUM(C3:C46)" Application.ScreenUpdating = True End Sub Private Sub Gaps4B() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False ' myCol = 1 myRow = 3 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents Cells(2, 1).Value = "Gap" Cells(2, 2).Value = "Total" For A = 0 To mySize - 5 ' If A < 0 Then MsgBox "Finished " & A & " out of " & mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B - A For D = 0 To mySize - 5 - C - B - A For E = 0 To mySize - 5 - D - C - B - A If mySize - 5 - A - B - C - D - E 0 Then strGap = Format(A, "00") & " " & _ Format(B, "00") & " " & _ Format(C, "00") & " " & _ Format(D, "00") & " " & _ Format(E, "00") Cells(myRow, myCol).Value = strGap Cells(myRow, myCol + 1).Value = mySize - 5 - A - B - C - D - E myRow = myRow + 1 If myRow = Rows.Count + 1 Then myRow = 3 myCol = myCol + 2 Cells(2, myCol).Value = "Gap ID" Cells(2, myCol + 1).Value = "Count" End If End If Next E Next D Next C Next B Next A Application.ScreenUpdating = True End Sub "Paul Black" wrote in message ups.com... Bernie, thanks ever so much for all the time and effort you have given to this, it is appreciated. Just a couple of things though please. I am going over to see my niece this evening so I will be able to run the two Subs you provided below ( the second one has been updated according to your last response ). Firstly, for Private Sub Gaps2(), will it possible to have the Grand Total for ALL the combinations produced in column "C" please . This figure will be 69,919,080 as produced by Mr. Tom Ogilvy when he kindly ran the Sub for me and will go after the last entry in Column "C". Secondly, will it be possible for Private Sub Gaps4() to have ... Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" ... and the categories list starting in Cell "B3" and the Total combinations associated with each category starting in Cell "C3" like ... Column "B" Column "C" 00 00 00 00 01 xxx,xxx,xxx 00 00 08 03 02 xxx,xxx,xxx 09 08 02 02 03 xxx,xxx,xxx ... for example. Finally, will it also be possible to have the Grand Total for ALL the combinations produced in column "C" please, this will go after the last entry in Column "C". Private Sub Gaps2() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim GapsTotal(0 To 43) As Long Application.ScreenUpdating = False For i = 0 To 43 GapsTotal(i) = 0 Next i For A = 1 To 44 For B = A + 1 To 45 For C = B + 1 To 46 For D = C + 1 To 47 For E = D + 1 To 48 For F = E + 1 To 49 GapsTotal(B - A - 1) = GapsTotal(B - A - 1) + 1 GapsTotal(C - B - 1) = GapsTotal(C - B - 1) + 1 GapsTotal(D - C - 1) = GapsTotal(D - C - 1) + 1 GapsTotal(E - D - 1) = GapsTotal(E - D - 1) + 1 GapsTotal(F - E - 1) = GapsTotal(F - E - 1) + 1 Next F Next E Next D Next C Next B Next A Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" For i = 0 To 43 Sheets("Gaps Data").Cells(i + 3, 2).Value = "Gaps of " & Format(i, "00") Sheets("Gaps Data").Cells(i + 3, 3).Value = GapsTotal(i) Next i Application.ScreenUpdating = True End Sub --------------------------------------------------------------------- Private Sub Gaps4() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False ' myCol = 1 myRow = 1 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents For A = 0 To mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B - A For D = 0 To mySize - 5 - C - B - A For E = 0 To mySize - 5 - D - C - B - A If mySize - 5 - A - B - C - D - E 0 Then strGap = Format(A, "00") & " " & _ Format(B, "00") & " " & _ Format(C, "00") & " " & _ Format(D, "00") & " " & _ Format(E, "00") & " Count = " & Format(mySize - 5 - A - B - C - D - E, "0") Cells(myRow, myCol).Value = strGap myRow = myRow + 1 If myRow = Rows.Count + 1 Then myRow = 1 myCol = myCol + 1 End If End If Next E Next D Next C Next B Next A Application.ScreenUpdating = True End Sub I will then be able to run them later as I said. Thanks in Advance. All the Best. Paul On Jul 11, 4:34 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, Replace this: For A = 0 To mySize - 5 ' If A < 0 Then MsgBox "Finished " & A & " out of " & mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B For D = 0 To mySize - 5 - C For E = 0 To mySize - 5 - D with this For A = 0 To mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B - A For D = 0 To mySize - 5 - C - B - A For E = 0 To mySize - 5 - D - C - B - A HTH, Bernie MS Excel MVP "Paul Black" wrote in message oups.com... Thanks Bernie, But how do I apply this to the existing code. Thanks in Advance. All the Best. Paul On Jul 11, 2:31 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: This would be a better loop control structu For A = 0 To mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B - A For D = 0 To mySize - 5 - C - B - A For E = 0 To mySize - 5 - D - C - B - A HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in l... Here's a better macro, with fewer iterations - but still LOTS... Bernie Private Sub Gaps4() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False ' myCol = 1 myRow = 1 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents For A = 0 To mySize - 5 ' If A < 0 Then MsgBox "Finished " & A & " out of " & mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B For D = 0 To mySize - 5 - C For E = 0 To mySize - 5 - D If mySize - 5 - A - B - C - D - E 0 Then strGap = Format(A, "00") & " " & _ Format(B, "00") & " " & _ Format(C, "00") & " " & _ Format(D, "00") & " " & _ ... read more »- Hide quoted text - - Show quoted text - |
Calculating Gaps Between Numbers
Thanks very much Bernie for the revised code.
Sub Gaps4B() is absolutely fine. I will just add the Formula you suggested in a Cell somewhere. You are quite right, I confused myself. I meant to say about the thousands seperator for the Total combinations in the Sub Gaps2B(). Will ... Sheets("Gaps Data").Cells(i + 3, 3).Value = Format(GapsTotal(i), "0,000,000") .... do the trick please. One final point, is there any way that ... Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" .... and ... Sheets("Gaps Data").Cells(47, 2).Value = "Grand Total" Sheets("Gaps Data").Cells(47, 3).Formula = "=SUM(C3:C46)" .... can be re-written by using With & End With for example. Thanks in Advance. All the Best. Paul On Jul 12, 10:58 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, For the first part, try the version below. For the second part, I think you are confused as to how many times each of the GAP strings actually appear. The maximum value is 44, which clearly doesn't need to be comma separated. The grand total ("Grand Total which should equal 39,983,816 ") is actually 13,983,816 - and note that there are 1,712,304 different combinations, for a count of about 8 1/6 per gap string on average. In my last message I wrote: "It's finished - 1,712,304 unique combinations of gaps listed, out to column BB (two columns per record set)" You can calculate that by simply using =SUM(A:BB) in a cell in column BC. HTH, Bernie Private Sub GapsVariable() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim mySize As Integer Dim GapsTotal() As Long mySize = 49 ' Change the size here ReDim GapsTotal(0 To mySize - 6) Application.ScreenUpdating = False For i = 0 To UBound(GapsTotal) GapsTotal(i) = 0 Next i For A = 1 To mySize - 5 For B = A + 1 To mySize - 4 For C = B + 1 To mySize - 3 For D = C + 1 To mySize - 2 For E = D + 1 To mySize - 1 For F = E + 1 To mySize GapsTotal(B - A - 1) = GapsTotal(B - A - 1) + 1 GapsTotal(C - B - 1) = GapsTotal(C - B - 1) + 1 GapsTotal(D - C - 1) = GapsTotal(D - C - 1) + 1 GapsTotal(E - D - 1) = GapsTotal(E - D - 1) + 1 GapsTotal(F - E - 1) = GapsTotal(F - E - 1) + 1 Next F Next E Next D Next C Next B Next A Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" For i = 0 To UBound(GapsTotal) Sheets("Gaps Data").Cells(i + 3, 2).Value = "Gaps of " & Format(i, "00") Sheets("Gaps Data").Cells(i + 3, 3).Value = GapsTotal(i) Next i i = UBound(GapsTotal) + 4 Sheets("Gaps Data").Cells(i, 2).Value = "Grand Total" Sheets("Gaps Data").Cells(i, 3).Formula = "=SUM(C3:C" & UBound(GapsTotal) + 3 & ")" Application.ScreenUpdating = True End Sub "Paul Black" wrote in message ups.com... Brilliant Bernie, I ran both the Subs and got the results I was after, thank you so much. Is there a way to adapt the Sub Gaps2B() code to make the Grand Total float so to speak according to the categories and the Total combinations produced. If I was to change the maximum number of balls from 49 to say 36 for arguments sake, the Grand Total will still appear in Cell "C47" as it does now, leaving a gap of several blank cells above before the last Gaps Total entry. I know I can change the ... Dim GapsTotal(0 To 43) As Long ... and ... For A = 1 To 44 For B = A + 1 To 45 For C = B + 1 To 46 For D = C + 1 To 47 For E = D + 1 To 48 For F = E + 1 To 49 ... and ... For i = 0 To 43 Sheets("Gaps Data").Cells(i + 3, 2).Value = "Gaps of " & Format(i, "00") Sheets("Gaps Data").Cells(i + 3, 3).Value = GapsTotal(i) Next i ... figure of 43 to whatever. Is there a way so I don't have to specify the number 0 to 43 in the Array but it works it out for you?. Could possibly the LBound and UBound be used so the values do not have to be hard coded?. The Sub Gaps4B() works perfectly except for the fact that there are no thousand seperators for the Total combinations for each category and there is no Grand Total which should equal 39,983,816 combinations. Would ... Cells(myRow, myCol + 1).Value = Format(mySize - 5 - A - B - C - D - E, "0,000,000") ... sort out the thousands seperator?. Thanks in Advance. All the Best. Paul On Jul 12, 4:25 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, It's finished - 1,712,304 unique combinations of gaps listed, out to column BB (two columns per record set), with a resulting file size of 77 meg. HTH, Bernie MS Excel MVP "Paul Black" wrote in message roups.com... Thanks Bernie, I do not understand what you mean when you say ... The new version, Gaps4B (below Gaps2B) will produce the separate string / count columns, but it will still take up most of a worksheet - a single column won't hold the results. ... Does it mean that I need to add a Column OffSet or something?. Do you mean that there is too much data for the category list produced in Column "B" and for the Total combinations associated produced in Column "C". This makes the data more than 65,536 rows, is that correct. If so how can the code be adapted to cater for this?. Thanks again in Advance. All the Best. Paul On Jul 12, 2:12 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, The new version, Gaps2B (below), will produce the grand total formula at the bottom. The new version, Gaps4B (below Gaps2B) will produce the separate string / count columns, but it will still take up most of a worksheet - a single column won't hold the results HTH, Bernie MS Excel MVP Private Sub Gaps2B() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim GapsTotal(0 To 43) As Long Application.ScreenUpdating = False For i = 0 To 43 GapsTotal(i) = 0 Next i For A = 1 To 44 For B = A + 1 To 45 For C = B + 1 To 46 For D = C + 1 To 47 For E = D + 1 To 48 For F = E + 1 To 49 GapsTotal(B - A - 1) = GapsTotal(B - A - 1) + 1 GapsTotal(C - B - 1) = GapsTotal(C - B - 1) + 1 GapsTotal(D - C - 1) = GapsTotal(D - C - 1) + 1 GapsTotal(E - D - 1) = GapsTotal(E - D - 1) + 1 GapsTotal(F - E - 1) = GapsTotal(F - E - 1) + 1 Next F Next E Next D Next C Next B Next A Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" For i = 0 To 43 Sheets("Gaps Data").Cells(i + 3, 2).Value = "Gaps of " & Format(i, "00") Sheets("Gaps Data").Cells(i + 3, 3).Value = GapsTotal(i) Next i Sheets("Gaps Data").Cells(47, 2).Value = "Grand Total" Sheets("Gaps Data").Cells(47, 3).Formula = "=SUM(C3:C46)" Application.ScreenUpdating = True End Sub Private Sub Gaps4B() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False ' myCol = 1 myRow = 3 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents Cells(2, 1).Value = "Gap" Cells(2, 2).Value = "Total" For A = 0 To mySize - 5 ' If A < 0 Then MsgBox "Finished " & A & " out of " & mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B - A For D = 0 To mySize - 5 - C - B - A For E = 0 To mySize - 5 - D - C - B - A If mySize - 5 - A - B - C - D - E 0 Then strGap = Format(A, "00") & " " & _ Format(B, "00") & " " & _ Format(C, "00") & " " & _ Format(D, "00") & " " & _ Format(E, "00") Cells(myRow, myCol).Value = strGap Cells(myRow, myCol + 1).Value = mySize - 5 - A - B - C - D - E myRow = myRow + 1 If myRow = Rows.Count + 1 Then myRow = 3 myCol = myCol + 2 Cells(2, myCol).Value = "Gap ID" Cells(2, myCol + 1).Value = "Count" End If End If Next E Next D Next C Next B Next A Application.ScreenUpdating = True End Sub "Paul Black" wrote in message ups.com... Bernie, thanks ever so much for all the time and effort you have given to this, it is appreciated. Just a couple of things though please. I am going over to see my niece this evening so I will be able to run the two Subs you provided below ( the second one has been updated according to your last response ). Firstly, for Private Sub Gaps2(), will it possible to have the Grand Total for ALL the combinations produced in column "C" please . This figure will be 69,919,080 as produced by Mr. Tom Ogilvy when he kindly ran the Sub for me and will go after the last entry in Column "C". Secondly, will it be possible for Private Sub Gaps4() to have ... Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" ... and the categories list starting in Cell "B3" and the Total combinations associated with each category starting in Cell "C3" like ... Column "B" Column "C" 00 00 00 00 01 xxx,xxx,xxx 00 00 08 03 02 xxx,xxx,xxx 09 08 02 02 03 xxx,xxx,xxx ... for example. Finally, will it also be possible to have the Grand Total for ALL the combinations produced in column "C" please, this will go after the last entry in Column "C". Private Sub Gaps2() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim GapsTotal(0 To 43) As Long Application.ScreenUpdating = False For i = 0 To 43 GapsTotal(i) = 0 Next i For A = 1 To 44 For B = A + 1 To 45 For C = B + 1 To 46 For D = C + 1 To 47 For E = D + 1 To 48 For F = E + 1 To 49 GapsTotal(B - A - 1) = GapsTotal(B - A - 1) + 1 GapsTotal(C - B - 1) = GapsTotal(C - B - 1) + 1 GapsTotal(D - C - 1) = GapsTotal(D - C - 1) + 1 GapsTotal(E - D - 1) = GapsTotal(E - D - 1) + 1 GapsTotal(F - E - 1) = GapsTotal(F - E - 1) + 1 Next F Next E Next D Next C Next B Next A Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps ... read more »- Hide quoted text - - Show quoted text - |
Calculating Gaps Between Numbers
Paul,
Sheets("Gaps Data").Cells(i + 3, 3).Value = Format(GapsTotal(i), "0,000,000") would be better With Sheets("Gaps Data").Cells(i + 3, 3) .NumberFormat = "0,000,000" .Value = GapsTotal(i) End With To rewrite: Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" You would use With Sheets("Gaps Data").Range("B2") .Value = "Gaps" .Offset(0,1).Value = "Total" End With And to re-write Sheets("Gaps Data").Cells(47, 2).Value = "Grand Total" Sheets("Gaps Data").Cells(47, 3).Formula = "=SUM(C3:C46)" You could use i = UBound(GapsTotal) + 4 With Sheets("Gaps Data").Cells(i, 2) .Value = "Grand Total" .Offset(0,1).Formula = "=SUM(C3:C" & UBound(GapsTotal) + 3 & ")" End With Of course, this is done with the latest version's code..... HTH, Bernie MS Excel MVP "Paul Black" wrote in message ps.com... Thanks very much Bernie for the revised code. Sub Gaps4B() is absolutely fine. I will just add the Formula you suggested in a Cell somewhere. You are quite right, I confused myself. I meant to say about the thousands seperator for the Total combinations in the Sub Gaps2B(). Will ... Sheets("Gaps Data").Cells(i + 3, 3).Value = Format(GapsTotal(i), "0,000,000") .... do the trick please. One final point, is there any way that ... Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" .... and ... Sheets("Gaps Data").Cells(47, 2).Value = "Grand Total" Sheets("Gaps Data").Cells(47, 3).Formula = "=SUM(C3:C46)" .... can be re-written by using With & End With for example. Thanks in Advance. All the Best. Paul On Jul 12, 10:58 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, For the first part, try the version below. For the second part, I think you are confused as to how many times each of the GAP strings actually appear. The maximum value is 44, which clearly doesn't need to be comma separated. The grand total ("Grand Total which should equal 39,983,816 ") is actually 13,983,816 - and note that there are 1,712,304 different combinations, for a count of about 8 1/6 per gap string on average. In my last message I wrote: "It's finished - 1,712,304 unique combinations of gaps listed, out to column BB (two columns per record set)" You can calculate that by simply using =SUM(A:BB) in a cell in column BC. HTH, Bernie Private Sub GapsVariable() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim mySize As Integer Dim GapsTotal() As Long mySize = 49 ' Change the size here ReDim GapsTotal(0 To mySize - 6) Application.ScreenUpdating = False For i = 0 To UBound(GapsTotal) GapsTotal(i) = 0 Next i For A = 1 To mySize - 5 For B = A + 1 To mySize - 4 For C = B + 1 To mySize - 3 For D = C + 1 To mySize - 2 For E = D + 1 To mySize - 1 For F = E + 1 To mySize GapsTotal(B - A - 1) = GapsTotal(B - A - 1) + 1 GapsTotal(C - B - 1) = GapsTotal(C - B - 1) + 1 GapsTotal(D - C - 1) = GapsTotal(D - C - 1) + 1 GapsTotal(E - D - 1) = GapsTotal(E - D - 1) + 1 GapsTotal(F - E - 1) = GapsTotal(F - E - 1) + 1 Next F Next E Next D Next C Next B Next A Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" For i = 0 To UBound(GapsTotal) Sheets("Gaps Data").Cells(i + 3, 2).Value = "Gaps of " & Format(i, "00") Sheets("Gaps Data").Cells(i + 3, 3).Value = GapsTotal(i) Next i i = UBound(GapsTotal) + 4 Sheets("Gaps Data").Cells(i, 2).Value = "Grand Total" Sheets("Gaps Data").Cells(i, 3).Formula = "=SUM(C3:C" & UBound(GapsTotal) + 3 & ")" Application.ScreenUpdating = True End Sub "Paul Black" wrote in message ups.com... Brilliant Bernie, I ran both the Subs and got the results I was after, thank you so much. Is there a way to adapt the Sub Gaps2B() code to make the Grand Total float so to speak according to the categories and the Total combinations produced. If I was to change the maximum number of balls from 49 to say 36 for arguments sake, the Grand Total will still appear in Cell "C47" as it does now, leaving a gap of several blank cells above before the last Gaps Total entry. I know I can change the ... Dim GapsTotal(0 To 43) As Long ... and ... For A = 1 To 44 For B = A + 1 To 45 For C = B + 1 To 46 For D = C + 1 To 47 For E = D + 1 To 48 For F = E + 1 To 49 ... and ... For i = 0 To 43 Sheets("Gaps Data").Cells(i + 3, 2).Value = "Gaps of " & Format(i, "00") Sheets("Gaps Data").Cells(i + 3, 3).Value = GapsTotal(i) Next i ... figure of 43 to whatever. Is there a way so I don't have to specify the number 0 to 43 in the Array but it works it out for you?. Could possibly the LBound and UBound be used so the values do not have to be hard coded?. The Sub Gaps4B() works perfectly except for the fact that there are no thousand seperators for the Total combinations for each category and there is no Grand Total which should equal 39,983,816 combinations. Would ... Cells(myRow, myCol + 1).Value = Format(mySize - 5 - A - B - C - D - E, "0,000,000") ... sort out the thousands seperator?. Thanks in Advance. All the Best. Paul On Jul 12, 4:25 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, It's finished - 1,712,304 unique combinations of gaps listed, out to column BB (two columns per record set), with a resulting file size of 77 meg. HTH, Bernie MS Excel MVP "Paul Black" wrote in message roups.com... Thanks Bernie, I do not understand what you mean when you say ... The new version, Gaps4B (below Gaps2B) will produce the separate string / count columns, but it will still take up most of a worksheet - a single column won't hold the results. ... Does it mean that I need to add a Column OffSet or something?. Do you mean that there is too much data for the category list produced in Column "B" and for the Total combinations associated produced in Column "C". This makes the data more than 65,536 rows, is that correct. If so how can the code be adapted to cater for this?. Thanks again in Advance. All the Best. Paul On Jul 12, 2:12 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, The new version, Gaps2B (below), will produce the grand total formula at the bottom. The new version, Gaps4B (below Gaps2B) will produce the separate string / count columns, but it will still take up most of a worksheet - a single column won't hold the results HTH, Bernie MS Excel MVP Private Sub Gaps2B() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim GapsTotal(0 To 43) As Long Application.ScreenUpdating = False For i = 0 To 43 GapsTotal(i) = 0 Next i For A = 1 To 44 For B = A + 1 To 45 For C = B + 1 To 46 For D = C + 1 To 47 For E = D + 1 To 48 For F = E + 1 To 49 GapsTotal(B - A - 1) = GapsTotal(B - A - 1) + 1 GapsTotal(C - B - 1) = GapsTotal(C - B - 1) + 1 GapsTotal(D - C - 1) = GapsTotal(D - C - 1) + 1 GapsTotal(E - D - 1) = GapsTotal(E - D - 1) + 1 GapsTotal(F - E - 1) = GapsTotal(F - E - 1) + 1 Next F Next E Next D Next C Next B Next A Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" For i = 0 To 43 Sheets("Gaps Data").Cells(i + 3, 2).Value = "Gaps of " & Format(i, "00") Sheets("Gaps Data").Cells(i + 3, 3).Value = GapsTotal(i) Next i Sheets("Gaps Data").Cells(47, 2).Value = "Grand Total" Sheets("Gaps Data").Cells(47, 3).Formula = "=SUM(C3:C46)" Application.ScreenUpdating = True End Sub Private Sub Gaps4B() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False ' myCol = 1 myRow = 3 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents Cells(2, 1).Value = "Gap" Cells(2, 2).Value = "Total" For A = 0 To mySize - 5 ' If A < 0 Then MsgBox "Finished " & A & " out of " & mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B - A For D = 0 To mySize - 5 - C - B - A For E = 0 To mySize - 5 - D - C - B - A If mySize - 5 - A - B - C - D - E 0 Then strGap = Format(A, "00") & " " & _ Format(B, "00") & " " & _ Format(C, "00") & " " & _ Format(D, "00") & " " & _ Format(E, "00") Cells(myRow, myCol).Value = strGap Cells(myRow, myCol + 1).Value = mySize - 5 - A - B - C - D - E myRow = myRow + 1 If myRow = Rows.Count + 1 Then myRow = 3 myCol = myCol + 2 Cells(2, myCol).Value = "Gap ID" Cells(2, myCol + 1).Value = "Count" End If End If Next E Next D Next C Next B Next A Application.ScreenUpdating = True End Sub "Paul Black" wrote in message ups.com... Bernie, thanks ever so much for all the time and effort you have given to this, it is appreciated. Just a couple of things though please. I am going over to see my niece this evening so I will be able to run the two Subs you provided below ( the second one has been updated according to your last response ). Firstly, for Private Sub Gaps2(), will it possible to have the Grand Total for ALL the combinations produced in column "C" please . This figure will be 69,919,080 as produced by Mr. Tom Ogilvy when he kindly ran the Sub for me and will go after the last entry in Column "C". Secondly, will it be possible for Private Sub Gaps4() to have ... Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" ... and the categories list starting in Cell "B3" and the Total combinations associated with each category starting in Cell "C3" like ... Column "B" Column "C" 00 00 00 00 01 xxx,xxx,xxx 00 00 08 03 02 xxx,xxx,xxx 09 08 02 02 03 xxx,xxx,xxx ... for example. Finally, will it also be possible to have the Grand Total for ALL the combinations produced in column "C" please, this will go after the last entry in Column "C". Private Sub Gaps2() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim GapsTotal(0 To 43) As Long Application.ScreenUpdating = False For i = 0 To 43 GapsTotal(i) = 0 Next i For A = 1 To 44 For B = A + 1 To 45 For C = B + 1 To 46 For D = C + 1 To 47 For E = D + 1 To 48 For F = E + 1 To 49 GapsTotal(B - A - 1) = GapsTotal(B - A - 1) + 1 GapsTotal(C - B - 1) = GapsTotal(C - B - 1) + 1 GapsTotal(D - C - 1) = GapsTotal(D - C - 1) + 1 GapsTotal(E - D - 1) = GapsTotal(E - D - 1) + 1 GapsTotal(F - E - 1) = GapsTotal(F - E - 1) + 1 Next F Next E Next D Next C Next B Next A Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps ... read more »- Hide quoted text - - Show quoted text - |
Calculating Gaps Between Numbers
Brilliant Bernie, thanks VERY much for all your time, effort and
patience with regard to my request, it is appreciated. I have added all the revised code you have provided into the Sub which is posted below. Would you kindly have a quick look through it to make sure everything is OK. Sub GapsVariable2BRevised() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim mySize As Integer Dim GapsTotal() As Long mySize = 49 '< Change the Size here ReDim GapsTotal(0 To mySize - 6) Application.ScreenUpdating = False For i = 0 To UBound(GapsTotal) GapsTotal(i) = 0 Next i For A = 1 To mySize - 5 For B = A + 1 To mySize - 4 For C = B + 1 To mySize - 3 For D = C + 1 To mySize - 2 For E = D + 1 To mySize - 1 For F = E + 1 To mySize GapsTotal(B - A - 1) = GapsTotal(B - A - 1) + 1 GapsTotal(C - B - 1) = GapsTotal(C - B - 1) + 1 GapsTotal(D - C - 1) = GapsTotal(D - C - 1) + 1 GapsTotal(E - D - 1) = GapsTotal(E - D - 1) + 1 GapsTotal(F - E - 1) = GapsTotal(F - E - 1) + 1 Next F Next E Next D Next C Next B Next A With Sheets("Gaps Data").Range("B2") .Value = "Gaps" .Offset(0,1).Value = "Total" End With For i = 0 To UBound(GapsTotal) With Sheets("Gaps Data").Cells(i + 3, 3) .NumberFormat = "0,000,000" .Value = GapsTotal(i) End With Next i i = UBound(GapsTotal) + 4 With Sheets("Gaps Data").Cells(i, 2) .Value = "Grand Total" .Offset(0,1).Formula = "=SUM(C3:C" & UBound(GapsTotal) + 3 & ")" End With Application.ScreenUpdating = True End Sub Have a Great Weekend. All the Best. Paul On Jul 13, 12:40 am, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, Sheets("Gaps Data").Cells(i + 3, 3).Value = Format(GapsTotal(i), "0,000,000") would be better With Sheets("Gaps Data").Cells(i + 3, 3) .NumberFormat = "0,000,000" .Value = GapsTotal(i) End With To rewrite: Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" You would use With Sheets("Gaps Data").Range("B2") .Value = "Gaps" .Offset(0,1).Value = "Total" End With And to re-write Sheets("Gaps Data").Cells(47, 2).Value = "Grand Total" Sheets("Gaps Data").Cells(47, 3).Formula = "=SUM(C3:C46)" You could use i = UBound(GapsTotal) + 4 With Sheets("Gaps Data").Cells(i, 2) .Value = "Grand Total" .Offset(0,1).Formula = "=SUM(C3:C" & UBound(GapsTotal) + 3 & ")" End With Of course, this is done with the latest version's code..... HTH, Bernie MS Excel MVP "Paul Black" wrote in message ps.com... Thanks very much Bernie for the revised code. Sub Gaps4B() is absolutely fine. I will just add the Formula you suggested in a Cell somewhere. You are quite right, I confused myself. I meant to say about the thousands seperator for the Total combinations in the Sub Gaps2B(). Will ... Sheets("Gaps Data").Cells(i + 3, 3).Value = Format(GapsTotal(i), "0,000,000") ... do the trick please. One final point, is there any way that ... Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" ... and ... Sheets("Gaps Data").Cells(47, 2).Value = "Grand Total" Sheets("Gaps Data").Cells(47, 3).Formula = "=SUM(C3:C46)" ... can be re-written by using With & End With for example. Thanks in Advance. All the Best. Paul On Jul 12, 10:58 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, For the first part, try the version below. For the second part, I think you are confused as to how many times each of the GAP strings actually appear. The maximum value is 44, which clearly doesn't need to be comma separated. The grand total ("Grand Total which should equal 39,983,816 ") is actually 13,983,816 - and note that there are 1,712,304 different combinations, for a count of about 8 1/6 per gap string on average. In my last message I wrote: "It's finished - 1,712,304 unique combinations of gaps listed, out to column BB (two columns per record set)" You can calculate that by simply using =SUM(A:BB) in a cell in column BC. HTH, Bernie Private Sub GapsVariable() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim mySize As Integer Dim GapsTotal() As Long mySize = 49 ' Change the size here ReDim GapsTotal(0 To mySize - 6) Application.ScreenUpdating = False For i = 0 To UBound(GapsTotal) GapsTotal(i) = 0 Next i For A = 1 To mySize - 5 For B = A + 1 To mySize - 4 For C = B + 1 To mySize - 3 For D = C + 1 To mySize - 2 For E = D + 1 To mySize - 1 For F = E + 1 To mySize GapsTotal(B - A - 1) = GapsTotal(B - A - 1) + 1 GapsTotal(C - B - 1) = GapsTotal(C - B - 1) + 1 GapsTotal(D - C - 1) = GapsTotal(D - C - 1) + 1 GapsTotal(E - D - 1) = GapsTotal(E - D - 1) + 1 GapsTotal(F - E - 1) = GapsTotal(F - E - 1) + 1 Next F Next E Next D Next C Next B Next A Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" For i = 0 To UBound(GapsTotal) Sheets("Gaps Data").Cells(i + 3, 2).Value = "Gaps of " & Format(i, "00") Sheets("Gaps Data").Cells(i + 3, 3).Value = GapsTotal(i) Next i i = UBound(GapsTotal) + 4 Sheets("Gaps Data").Cells(i, 2).Value = "Grand Total" Sheets("Gaps Data").Cells(i, 3).Formula = "=SUM(C3:C" & UBound(GapsTotal) + 3 & ")" Application.ScreenUpdating = True End Sub "Paul Black" wrote in message oups.com... Brilliant Bernie, I ran both the Subs and got the results I was after, thank you so much. Is there a way to adapt the Sub Gaps2B() code to make the Grand Total float so to speak according to the categories and the Total combinations produced. If I was to change the maximum number of balls from 49 to say 36 for arguments sake, the Grand Total will still appear in Cell "C47" as it does now, leaving a gap of several blank cells above before the last Gaps Total entry. I know I can change the ... Dim GapsTotal(0 To 43) As Long ... and ... For A = 1 To 44 For B = A + 1 To 45 For C = B + 1 To 46 For D = C + 1 To 47 For E = D + 1 To 48 For F = E + 1 To 49 ... and ... For i = 0 To 43 Sheets("Gaps Data").Cells(i + 3, 2).Value = "Gaps of " & Format(i, "00") Sheets("Gaps Data").Cells(i + 3, 3).Value = GapsTotal(i) Next i ... figure of 43 to whatever. Is there a way so I don't have to specify the number 0 to 43 in the Array but it works it out for you?. Could possibly the LBound and UBound be used so the values do not have to be hard coded?. The Sub Gaps4B() works perfectly except for the fact that there are no thousand seperators for the Total combinations for each category and there is no Grand Total which should equal 39,983,816 combinations. Would ... Cells(myRow, myCol + 1).Value = Format(mySize - 5 - A - B - C - D - E, "0,000,000") ... sort out the thousands seperator?. Thanks in Advance. All the Best. Paul On Jul 12, 4:25 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, It's finished - 1,712,304 unique combinations of gaps listed, out to column BB (two columns per record set), with a resulting file size of 77 meg. HTH, Bernie MS Excel MVP "Paul Black" wrote in message roups.com... Thanks Bernie, I do not understand what you mean when you say ... The new version, Gaps4B (below Gaps2B) will produce the separate string / count columns, but it will still take up most of a worksheet - a single column won't hold the results. ... Does it mean that I need to add a Column OffSet or something?. Do you mean that there is too much data for the category list produced in Column "B" and for the Total combinations associated produced in Column "C". This makes the data more than 65,536 rows, is that correct. If so how can the code be adapted to cater for this?. Thanks again in Advance. All the Best. Paul On Jul 12, 2:12 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, The new version, Gaps2B (below), will produce the grand total formula at the bottom. The new version, Gaps4B (below Gaps2B) will produce the separate string / count columns, but it will still take up most of a worksheet - a single column won't hold the results HTH, Bernie MS Excel MVP Private Sub Gaps2B() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim GapsTotal(0 To 43) As Long Application.ScreenUpdating = False For i = 0 To 43 GapsTotal(i) = 0 Next i For A = 1 To 44 For B = A + 1 To 45 For C = B + 1 To 46 For D = C + 1 To 47 For E = D + 1 To 48 For F = E + 1 To 49 GapsTotal(B - A - 1) = GapsTotal(B - A - 1) + 1 GapsTotal(C - B - 1) = GapsTotal(C - B - 1) + 1 GapsTotal(D - C - 1) = GapsTotal(D - C - 1) + 1 GapsTotal(E - D - 1) = GapsTotal(E - D - 1) + 1 GapsTotal(F - E - 1) = GapsTotal(F - E - 1) + 1 Next F Next E Next D Next C Next B Next A Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" For i = 0 To 43 Sheets("Gaps Data").Cells(i + 3, 2).Value = "Gaps of " & Format(i, "00") Sheets("Gaps Data").Cells(i + 3, 3).Value = GapsTotal(i) Next i Sheets("Gaps Data").Cells(47, 2).Value = "Grand Total" Sheets("Gaps Data").Cells(47, 3).Formula = "=SUM(C3:C46)" Application.ScreenUpdating = True End Sub Private Sub Gaps4B() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False ' myCol = 1 myRow = 3 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents Cells(2, 1).Value = "Gap" Cells(2, 2).Value = "Total" For A = 0 To mySize - 5 ' If A < 0 Then MsgBox "Finished " & A & " out of " & mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B - A For D = 0 To mySize - 5 - C - B - A For E = 0 To mySize - 5 - D - C - B - A If mySize - 5 - A - B - C - D - E 0 Then strGap = Format(A, "00") & " " & _ Format(B, "00") & " " & _ Format(C, "00") & " " & _ Format(D, "00") & " " & _ Format(E, "00") Cells(myRow, myCol).Value = strGap Cells(myRow, myCol + 1).Value = mySize - 5 - A - B - C - D - E myRow = myRow + 1 If myRow = Rows.Count + 1 Then myRow = 3 myCol = myCol + 2 Cells(2, myCol).Value = "Gap ID" Cells(2, myCol + 1).Value = "Count" End If End If Next E Next D Next C Next B Next A Application.ScreenUpdating = ... read more »- Hide quoted text - - Show quoted text - |
Calculating Gaps Between Numbers
Paul,
You left out the column of labels: For i = 0 To UBound(GapsTotal) With Sheets("Gaps Data").Cells(i + 3, 3) .NumberFormat = "0,000,000" .Value = GapsTotal(i) End With Next i should be For i = 0 To UBound(GapsTotal) With Sheets("Gaps Data").Cells(i + 3, 2) .Value = "Gaps of " & Format(i, "00") .Offset(0, 1).NumberFormat = "#,###,###" .Offset(0, 1).Value = GapsTotal(i) End With Next i I changed the format string to #,###,### so that there wouldn't be leading values. Other than that, it works fine. HTH, Bernie MS Excel MVP "Paul Black" wrote in message oups.com... Brilliant Bernie, thanks VERY much for all your time, effort and patience with regard to my request, it is appreciated. I have added all the revised code you have provided into the Sub which is posted below. Would you kindly have a quick look through it to make sure everything is OK. Sub GapsVariable2BRevised() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim mySize As Integer Dim GapsTotal() As Long mySize = 49 '< Change the Size here ReDim GapsTotal(0 To mySize - 6) Application.ScreenUpdating = False For i = 0 To UBound(GapsTotal) GapsTotal(i) = 0 Next i For A = 1 To mySize - 5 For B = A + 1 To mySize - 4 For C = B + 1 To mySize - 3 For D = C + 1 To mySize - 2 For E = D + 1 To mySize - 1 For F = E + 1 To mySize GapsTotal(B - A - 1) = GapsTotal(B - A - 1) + 1 GapsTotal(C - B - 1) = GapsTotal(C - B - 1) + 1 GapsTotal(D - C - 1) = GapsTotal(D - C - 1) + 1 GapsTotal(E - D - 1) = GapsTotal(E - D - 1) + 1 GapsTotal(F - E - 1) = GapsTotal(F - E - 1) + 1 Next F Next E Next D Next C Next B Next A With Sheets("Gaps Data").Range("B2") .Value = "Gaps" .Offset(0,1).Value = "Total" End With For i = 0 To UBound(GapsTotal) With Sheets("Gaps Data").Cells(i + 3, 3) .NumberFormat = "0,000,000" .Value = GapsTotal(i) End With Next i i = UBound(GapsTotal) + 4 With Sheets("Gaps Data").Cells(i, 2) .Value = "Grand Total" .Offset(0,1).Formula = "=SUM(C3:C" & UBound(GapsTotal) + 3 & ")" End With Application.ScreenUpdating = True End Sub Have a Great Weekend. All the Best. Paul On Jul 13, 12:40 am, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, Sheets("Gaps Data").Cells(i + 3, 3).Value = Format(GapsTotal(i), "0,000,000") would be better With Sheets("Gaps Data").Cells(i + 3, 3) .NumberFormat = "0,000,000" .Value = GapsTotal(i) End With To rewrite: Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" You would use With Sheets("Gaps Data").Range("B2") .Value = "Gaps" .Offset(0,1).Value = "Total" End With And to re-write Sheets("Gaps Data").Cells(47, 2).Value = "Grand Total" Sheets("Gaps Data").Cells(47, 3).Formula = "=SUM(C3:C46)" You could use i = UBound(GapsTotal) + 4 With Sheets("Gaps Data").Cells(i, 2) .Value = "Grand Total" .Offset(0,1).Formula = "=SUM(C3:C" & UBound(GapsTotal) + 3 & ")" End With Of course, this is done with the latest version's code..... HTH, Bernie MS Excel MVP "Paul Black" wrote in message ps.com... Thanks very much Bernie for the revised code. Sub Gaps4B() is absolutely fine. I will just add the Formula you suggested in a Cell somewhere. You are quite right, I confused myself. I meant to say about the thousands seperator for the Total combinations in the Sub Gaps2B(). Will ... Sheets("Gaps Data").Cells(i + 3, 3).Value = Format(GapsTotal(i), "0,000,000") ... do the trick please. One final point, is there any way that ... Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" ... and ... Sheets("Gaps Data").Cells(47, 2).Value = "Grand Total" Sheets("Gaps Data").Cells(47, 3).Formula = "=SUM(C3:C46)" ... can be re-written by using With & End With for example. Thanks in Advance. All the Best. Paul On Jul 12, 10:58 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, For the first part, try the version below. For the second part, I think you are confused as to how many times each of the GAP strings actually appear. The maximum value is 44, which clearly doesn't need to be comma separated. The grand total ("Grand Total which should equal 39,983,816 ") is actually 13,983,816 - and note that there are 1,712,304 different combinations, for a count of about 8 1/6 per gap string on average. In my last message I wrote: "It's finished - 1,712,304 unique combinations of gaps listed, out to column BB (two columns per record set)" You can calculate that by simply using =SUM(A:BB) in a cell in column BC. HTH, Bernie Private Sub GapsVariable() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim mySize As Integer Dim GapsTotal() As Long mySize = 49 ' Change the size here ReDim GapsTotal(0 To mySize - 6) Application.ScreenUpdating = False For i = 0 To UBound(GapsTotal) GapsTotal(i) = 0 Next i For A = 1 To mySize - 5 For B = A + 1 To mySize - 4 For C = B + 1 To mySize - 3 For D = C + 1 To mySize - 2 For E = D + 1 To mySize - 1 For F = E + 1 To mySize GapsTotal(B - A - 1) = GapsTotal(B - A - 1) + 1 GapsTotal(C - B - 1) = GapsTotal(C - B - 1) + 1 GapsTotal(D - C - 1) = GapsTotal(D - C - 1) + 1 GapsTotal(E - D - 1) = GapsTotal(E - D - 1) + 1 GapsTotal(F - E - 1) = GapsTotal(F - E - 1) + 1 Next F Next E Next D Next C Next B Next A Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" For i = 0 To UBound(GapsTotal) Sheets("Gaps Data").Cells(i + 3, 2).Value = "Gaps of " & Format(i, "00") Sheets("Gaps Data").Cells(i + 3, 3).Value = GapsTotal(i) Next i i = UBound(GapsTotal) + 4 Sheets("Gaps Data").Cells(i, 2).Value = "Grand Total" Sheets("Gaps Data").Cells(i, 3).Formula = "=SUM(C3:C" & UBound(GapsTotal) + 3 & ")" Application.ScreenUpdating = True End Sub "Paul Black" wrote in message oups.com... Brilliant Bernie, I ran both the Subs and got the results I was after, thank you so much. Is there a way to adapt the Sub Gaps2B() code to make the Grand Total float so to speak according to the categories and the Total combinations produced. If I was to change the maximum number of balls from 49 to say 36 for arguments sake, the Grand Total will still appear in Cell "C47" as it does now, leaving a gap of several blank cells above before the last Gaps Total entry. I know I can change the ... Dim GapsTotal(0 To 43) As Long ... and ... For A = 1 To 44 For B = A + 1 To 45 For C = B + 1 To 46 For D = C + 1 To 47 For E = D + 1 To 48 For F = E + 1 To 49 ... and ... For i = 0 To 43 Sheets("Gaps Data").Cells(i + 3, 2).Value = "Gaps of " & Format(i, "00") Sheets("Gaps Data").Cells(i + 3, 3).Value = GapsTotal(i) Next i ... figure of 43 to whatever. Is there a way so I don't have to specify the number 0 to 43 in the Array but it works it out for you?. Could possibly the LBound and UBound be used so the values do not have to be hard coded?. The Sub Gaps4B() works perfectly except for the fact that there are no thousand seperators for the Total combinations for each category and there is no Grand Total which should equal 39,983,816 combinations. Would ... Cells(myRow, myCol + 1).Value = Format(mySize - 5 - A - B - C - D - E, "0,000,000") ... sort out the thousands seperator?. Thanks in Advance. All the Best. Paul On Jul 12, 4:25 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, It's finished - 1,712,304 unique combinations of gaps listed, out to column BB (two columns per record set), with a resulting file size of 77 meg. HTH, Bernie MS Excel MVP "Paul Black" wrote in message roups.com... Thanks Bernie, I do not understand what you mean when you say ... The new version, Gaps4B (below Gaps2B) will produce the separate string / count columns, but it will still take up most of a worksheet - a single column won't hold the results. ... Does it mean that I need to add a Column OffSet or something?. Do you mean that there is too much data for the category list produced in Column "B" and for the Total combinations associated produced in Column "C". This makes the data more than 65,536 rows, is that correct. If so how can the code be adapted to cater for this?. Thanks again in Advance. All the Best. Paul On Jul 12, 2:12 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, The new version, Gaps2B (below), will produce the grand total formula at the bottom. The new version, Gaps4B (below Gaps2B) will produce the separate string / count columns, but it will still take up most of a worksheet - a single column won't hold the results HTH, Bernie MS Excel MVP Private Sub Gaps2B() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim GapsTotal(0 To 43) As Long Application.ScreenUpdating = False For i = 0 To 43 GapsTotal(i) = 0 Next i For A = 1 To 44 For B = A + 1 To 45 For C = B + 1 To 46 For D = C + 1 To 47 For E = D + 1 To 48 For F = E + 1 To 49 GapsTotal(B - A - 1) = GapsTotal(B - A - 1) + 1 GapsTotal(C - B - 1) = GapsTotal(C - B - 1) + 1 GapsTotal(D - C - 1) = GapsTotal(D - C - 1) + 1 GapsTotal(E - D - 1) = GapsTotal(E - D - 1) + 1 GapsTotal(F - E - 1) = GapsTotal(F - E - 1) + 1 Next F Next E Next D Next C Next B Next A Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" For i = 0 To 43 Sheets("Gaps Data").Cells(i + 3, 2).Value = "Gaps of " & Format(i, "00") Sheets("Gaps Data").Cells(i + 3, 3).Value = GapsTotal(i) Next i Sheets("Gaps Data").Cells(47, 2).Value = "Grand Total" Sheets("Gaps Data").Cells(47, 3).Formula = "=SUM(C3:C46)" Application.ScreenUpdating = True End Sub Private Sub Gaps4B() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim i As Integer Dim mySize As Integer Dim strGap As String Dim myCol As Integer Dim myRow As Long Dim myCell As Range Application.ScreenUpdating = False ' myCol = 1 myRow = 3 mySize = 49 'start with a lower number here to try it.... Cells.ClearContents Cells(2, 1).Value = "Gap" Cells(2, 2).Value = "Total" For A = 0 To mySize - 5 ' If A < 0 Then MsgBox "Finished " & A & " out of " & mySize - 5 For B = 0 To mySize - 5 - A For C = 0 To mySize - 5 - B - A For D = 0 To mySize - 5 - C - B - A For E = 0 To mySize - 5 - D - C - B - A If mySize - 5 - A - B - C - D - E 0 Then strGap = Format(A, "00") & " " & _ Format(B, "00") & " " & _ Format(C, "00") & " " & _ Format(D, "00") & " " & _ Format(E, "00") Cells(myRow, myCol).Value = strGap Cells(myRow, myCol + 1).Value = mySize - 5 - A - B - C - D - E myRow = myRow + 1 If myRow = Rows.Count + 1 Then myRow = 3 myCol = myCol + 2 Cells(2, myCol).Value = "Gap ID" Cells(2, myCol + 1).Value = "Count" End If End If Next E Next D Next C Next B Next A Application.ScreenUpdating = ... read more »- Hide quoted text - - Show quoted text - |
Calculating Gaps Between Numbers
Thanks Bernie,
I have amended my code accordingly. Than you so much for ALL your help, time and patience. Have a great weekend. All the Best. Paul On Jul 13, 1:37 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, You left out the column of labels: For i = 0 To UBound(GapsTotal) With Sheets("Gaps Data").Cells(i + 3, 3) .NumberFormat = "0,000,000" .Value = GapsTotal(i) End With Next i should be For i = 0 To UBound(GapsTotal) With Sheets("Gaps Data").Cells(i + 3, 2) .Value = "Gaps of " & Format(i, "00") .Offset(0, 1).NumberFormat = "#,###,###" .Offset(0, 1).Value = GapsTotal(i) End With Next i I changed the format string to #,###,### so that there wouldn't be leading values. Other than that, it works fine. HTH, Bernie MS Excel MVP "Paul Black" wrote in message oups.com... Brilliant Bernie, thanks VERY much for all your time, effort and patience with regard to my request, it is appreciated. I have added all the revised code you have provided into the Sub which is posted below. Would you kindly have a quick look through it to make sure everything is OK. Sub GapsVariable2BRevised() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim mySize As Integer Dim GapsTotal() As Long mySize = 49 '< Change the Size here ReDim GapsTotal(0 To mySize - 6) Application.ScreenUpdating = False For i = 0 To UBound(GapsTotal) GapsTotal(i) = 0 Next i For A = 1 To mySize - 5 For B = A + 1 To mySize - 4 For C = B + 1 To mySize - 3 For D = C + 1 To mySize - 2 For E = D + 1 To mySize - 1 For F = E + 1 To mySize GapsTotal(B - A - 1) = GapsTotal(B - A - 1) + 1 GapsTotal(C - B - 1) = GapsTotal(C - B - 1) + 1 GapsTotal(D - C - 1) = GapsTotal(D - C - 1) + 1 GapsTotal(E - D - 1) = GapsTotal(E - D - 1) + 1 GapsTotal(F - E - 1) = GapsTotal(F - E - 1) + 1 Next F Next E Next D Next C Next B Next A With Sheets("Gaps Data").Range("B2") .Value = "Gaps" .Offset(0,1).Value = "Total" End With For i = 0 To UBound(GapsTotal) With Sheets("Gaps Data").Cells(i + 3, 3) .NumberFormat = "0,000,000" .Value = GapsTotal(i) End With Next i i = UBound(GapsTotal) + 4 With Sheets("Gaps Data").Cells(i, 2) .Value = "Grand Total" .Offset(0,1).Formula = "=SUM(C3:C" & UBound(GapsTotal) + 3 & ")" End With Application.ScreenUpdating = True End Sub Have a Great Weekend. All the Best. Paul On Jul 13, 12:40 am, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, Sheets("Gaps Data").Cells(i + 3, 3).Value = Format(GapsTotal(i), "0,000,000") would be better With Sheets("Gaps Data").Cells(i + 3, 3) .NumberFormat = "0,000,000" .Value = GapsTotal(i) End With To rewrite: Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" You would use With Sheets("Gaps Data").Range("B2") .Value = "Gaps" .Offset(0,1).Value = "Total" End With And to re-write Sheets("Gaps Data").Cells(47, 2).Value = "Grand Total" Sheets("Gaps Data").Cells(47, 3).Formula = "=SUM(C3:C46)" You could use i = UBound(GapsTotal) + 4 With Sheets("Gaps Data").Cells(i, 2) .Value = "Grand Total" .Offset(0,1).Formula = "=SUM(C3:C" & UBound(GapsTotal) + 3 & ")" End With Of course, this is done with the latest version's code..... HTH, Bernie MS Excel MVP "Paul Black" wrote in message ups.com... Thanks very much Bernie for the revised code. Sub Gaps4B() is absolutely fine. I will just add the Formula you suggested in a Cell somewhere. You are quite right, I confused myself. I meant to say about the thousands seperator for the Total combinations in the Sub Gaps2B(). Will ... Sheets("Gaps Data").Cells(i + 3, 3).Value = Format(GapsTotal(i), "0,000,000") ... do the trick please. One final point, is there any way that ... Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" ... and ... Sheets("Gaps Data").Cells(47, 2).Value = "Grand Total" Sheets("Gaps Data").Cells(47, 3).Formula = "=SUM(C3:C46)" ... can be re-written by using With & End With for example. Thanks in Advance. All the Best. Paul On Jul 12, 10:58 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, For the first part, try the version below. For the second part, I think you are confused as to how many times each of the GAP strings actually appear. The maximum value is 44, which clearly doesn't need to be comma separated. The grand total ("Grand Total which should equal 39,983,816 ") is actually 13,983,816 - and note that there are 1,712,304 different combinations, for a count of about 8 1/6 per gap string on average. In my last message I wrote: "It's finished - 1,712,304 unique combinations of gaps listed, out to column BB (two columns per record set)" You can calculate that by simply using =SUM(A:BB) in a cell in column BC. HTH, Bernie Private Sub GapsVariable() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim i As Integer Dim mySize As Integer Dim GapsTotal() As Long mySize = 49 ' Change the size here ReDim GapsTotal(0 To mySize - 6) Application.ScreenUpdating = False For i = 0 To UBound(GapsTotal) GapsTotal(i) = 0 Next i For A = 1 To mySize - 5 For B = A + 1 To mySize - 4 For C = B + 1 To mySize - 3 For D = C + 1 To mySize - 2 For E = D + 1 To mySize - 1 For F = E + 1 To mySize GapsTotal(B - A - 1) = GapsTotal(B - A - 1) + 1 GapsTotal(C - B - 1) = GapsTotal(C - B - 1) + 1 GapsTotal(D - C - 1) = GapsTotal(D - C - 1) + 1 GapsTotal(E - D - 1) = GapsTotal(E - D - 1) + 1 GapsTotal(F - E - 1) = GapsTotal(F - E - 1) + 1 Next F Next E Next D Next C Next B Next A Sheets("Gaps Data").Range("B2").Value = "Gaps" Sheets("Gaps Data").Range("C2").Value = "Total" For i = 0 To UBound(GapsTotal) Sheets("Gaps Data").Cells(i + 3, 2).Value = "Gaps of " & Format(i, "00") Sheets("Gaps Data").Cells(i + 3, 3).Value = GapsTotal(i) Next i i = UBound(GapsTotal) + 4 Sheets("Gaps Data").Cells(i, 2).Value = "Grand Total" Sheets("Gaps Data").Cells(i, 3).Formula = "=SUM(C3:C" & UBound(GapsTotal) + 3 & ")" Application.ScreenUpdating = True End Sub "Paul Black" wrote in message oups.com... Brilliant Bernie, I ran both the Subs and got the results I was after, thank you so much. Is there a way to adapt the Sub Gaps2B() code to make the Grand Total float so to speak according to the categories and the Total combinations produced. If I was to change the maximum number of balls from 49 to say 36 for arguments sake, the Grand Total will still appear in Cell "C47" as it does now, leaving a gap of several blank cells above before the last Gaps Total entry. I know I can change the ... Dim GapsTotal(0 To 43) As Long ... and ... For A = 1 To 44 For B = A + 1 To 45 For C = B + 1 To 46 For D = C + 1 To 47 For E = D + 1 To 48 For F = E + 1 To 49 ... and ... For i = 0 To 43 Sheets("Gaps Data").Cells(i + 3, 2).Value = "Gaps of " & Format(i, "00") Sheets("Gaps Data").Cells(i + 3, 3).Value = GapsTotal(i) Next i ... figure of 43 to whatever. Is there a way so I don't have to specify the number 0 to 43 in the Array but it works it out for you?. Could possibly the LBound and UBound be used so the values do not have to be hard coded?. The Sub Gaps4B() works perfectly except for the fact that there are no thousand seperators for the Total combinations for each category and there is no Grand Total which should equal 39,983,816 combinations. Would ... Cells(myRow, myCol + 1).Value = Format(mySize - 5 - A - B - C - D - E, "0,000,000") ... sort out the thousands seperator?. Thanks in Advance. All the Best. Paul On Jul 12, 4:25 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, It's finished - 1,712,304 unique combinations of gaps listed, out to column BB (two columns per record set), with a resulting file size of 77 meg. HTH, Bernie MS Excel MVP "Paul Black" wrote in message roups.com... Thanks Bernie, I do not understand what you mean when you say ... The new version, Gaps4B (below Gaps2B) will produce the separate string / count columns, but it will still take up most of a worksheet - a single column won't hold the results. ... Does it mean that I need to add a Column OffSet or something?. Do you mean that there is too much data for the category list produced in Column "B" and for the Total combinations associated produced in Column "C". This makes the data more than 65,536 rows, is that correct. If so how can the code be adapted to cater for this?. Thanks again in Advance. All the Best. Paul On Jul 12, 2:12 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Paul, The new version, Gaps2B (below), will produce the grand total formula at the bottom. The new version, Gaps4B (below Gaps2B) will produce the ... read more »- Hide quoted text - - Show quoted text - |
| All times are GMT +1. The time now is 02:57 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com