Calculate Last Digits

[Paul Black]

Hi everyone,

I am trying to loop through ALL the combinations and count the number
of occurances of the last digit for each of the combinations.
There are 10 categories of last digit :-

111111 2887500
211110 6930000
221100 2772000
222000 105600
311100 924000
321000 316800
330000 3960
411000 39600
420000 3960
510000 396
Total = 13983816

111111 means there are 2,887,500 combinations with all last digits
different.
321000 means there are 316,800 combinations where 3 of the last digits
are the same, 2 of the last digits are the same (but a different last
digit to the 3) and 1 last digit (but a different last digit to the 3
or 2).

Here is what I have so far :-

Option Explicit
Dim A As Long, B As Long, C As Long, D As Long, E As Long, F As Long

Sub LastDigit()
Dim i As Integer
Dim LastDigit As Integer
Dim DigitCounts(0 To 9) As Integer ' This will hold counters for each
digit 0-9
Dim nDupl As Integer
Const minVal As Integer = 1 ' The minimum value in ANY
combination
Const maxVal As Integer = 49 ' The maximum value in ANY
combination

Application.ScreenUpdating = False

For i = 0 To 9
DigitCounts(i) = 0
Next i

For A = minVal To maxVal - 5
For B = A + 1 To maxVal - 4
For C = B + 1 To maxVal - 3
For D = C + 1 To maxVal - 2
For E = D + 1 To maxVal - 1
For F = E + 1 To maxVal

For i = 0 To 4
LastDigit = i - 10 * Int(i) / 10
DigitCounts(LastDigit) = DigitCounts(LastDigit) + 1
Next i

nDupl = 0

For i = 0 To 9
If DigitCounts(i) > 1 Then nDupl = nDupl +
DigitCounts(i)
Next i

Next F
Next E
Next D
Next C
Next B
Next A

ActiveCell.Offset(0, 5).Value = nDupl
ActiveCell.Offset(1, 0).Select

Application.ScreenUpdating = True
End Sub

Any help will be greatly appreciated.
Thanks in Advance.
All the Best.
Paul

 

[Peter T]

I don't follow this at all

"111111 means there are 2,887,500 combinations with all last digits
different."

Why ?

What's a combination ?
What's a category ?

321000 means there are 316,800 combinations where 3 of the last digits
are the same, 2 of the last digits are the same (but a different last
digit to the 3) and 1 last digit (but a different last digit to the 3
or 2).

I defy any one to make sense of that !

Why is 6930000 more than 2887500, seems out of sequence ?

In passing, looks like you'll be dealing with numbers over 32k. If
potentially so you should declare them 'As Long' to avoid overflows (As
Integer in VB/VBA is virtually redundant in 32bit systems). Also FWIW, no
need to disable screen updating for the sake of populating just two cells.

Regards,
Peter T

 

[Paul Black]

Hi peter,

They are 6 number combinations. So for 6 numbers from 49 numbers there
are 13,983,816 total combinations.
111111 could be numbers 01 02 03 04 05 06, making ALL 6 last digits
different.
321000 could be numbers 01 11 21 30 40 49.

The full list of categories are :-
111111
211110
221100
222000
311100
321000
330000
411000
420000
510000

The program will hopefully calculate the the total combinations for
ALL the categories and list them one under the other.

Thanks in Advance.
All the Best.
Paul

 

[Peter T]

I've narrowed it down to a few different interpretations of what you might
mean. But you win - I give up!

I'm curious now to see if someone else can understand <g>

Regards,
Peter T

 

[Charlie]

Definitely strange, but I do see one pattern: All the digits add up to 6.
Subtract one from the right-most 1-digit and add one to the left-most 1-digit
to keep the total at 6, and so on. But the list is missing 600000. Other
than that I'm lost.

 

[Paul Black]

Sorry guys,

Charlie you are quite right, I left out the 600000 category, my
appologies.
Basically, there are 13,983,816 combinations of 6 numbers. EACH 6
number combination has a last digit. The program will ideally
calculate each 6 numbers last digit category and keep a count. These
will then be listed.

Thanks in Advance.
All the Best.
Paul

 

[Dana DeLouis]

Hi. Interesting challenge from a timing point of view. It appears you
posted a solution. Are you looking to verify the solution?
I used another program and got slightly different answers. Here's what I
show:

{6}, 0}
{5, 1}, 396}
{4, 2}, 3,960}
{4, 1, 1}, 39,600}
{3, 3}, 3,963}
{3, 2, 1}, 317,082}
{3, 1, 1, 1}, 924,791}
{2, 2, 2}, 105,684}
{2, 2, 1, 1}, 2,773,289}
{2, 1, 1, 1, 1}, 6,929,822}
{1, 1, 1, 1, 1, 1}, 2,885,229}

Note that 6 has no solution. Suppose we had an ending value of 1.
Then
{1,11,21,31,41,51}
is the only possible solution However, such a sequence does not exists
since the max size is 49.
Hence, no 6's.

The only solutions I have that matches yours are:
(5,1), (4,2), and (4,1,1)

Your "Pattern" is also known as the "Integer Partitions" of the number 6, of
which there are 11.
As pointed out, you were missing "6". If you copied the solution from
somewhere else, it was probably because they left it out since there are
none.

 

[Dana DeLouis]

Well, I apologize. I just attacked the problem from a different method and
got the time down to about 2 minutes.
However, I now get the same solution as you. I really don't see where the
error was in my previous code.
Are you trying to use Excel to verify the solution? I'm curious where you
got the answers.
Anyway, I'm not sure how long Excel would take to arrive at a solution, but
I'm guessing a very long time.

LastDigit = i - 10 * Int(i) / 10

I can't follow your code to well, but in the above, the variable 'I' is
already an integer, so you are not doing much to "I"
It "appears" you meant something like "Int(I/10)*10 to extract the last
digit.
Just to mention, the last digit is also = Mod(n,10)

Your initial set are the numbers 1-49. After generating all 13,983,816
subsets, you extract the last digit of each.
That's =6*COMBIN(49,6), or 83,902,896 different Mod () operations alone.
I suggest starting with a set with just the last digit. (ie apply the Mod to
each of the 49 numbers.)
Then go into your Subset routine.

Anyway, I get the same solution as you, so please disregard my previous
attempt.

 

[Paul Black]

Thanks for the reply Dana,

I worked out the results using the COMBIN formula in Excel for each
category.
I am reasonably new to VBA and thought it would be a good excercise to
create the same answers using VBA. I realise now that this is a much
bigger task than I can manage.
I don't need the 13,983,816 combinations themselves, I was just trying
to produce the total combinations for each last digit category. The
code I produced was what I thought was needed to achieve this.
When you say ...
However, I now get the same solution as you. I really don't see where
the error was in my previous code.
.... does that mean you have the code to produce the results I am
looking for please?.

Thanks in Advance.
All the Best.
Paul

 

[Dana DeLouis]

Hi. No, I used another program. I'm not sure how I would do this using
Excel vba in a reasonable amount of time.
Actually, I'd be curious to learn how you did it using Combin. I'd be
interested to learn If you can do this via formulas. I sure don't see it.
:>~

--
Thanks
Dana DeLouis

Thanks for the reply Dana,

I worked out the results using the COMBIN formula in Excel for each
category.
I am reasonably new to VBA and thought it would be a good excercise to
create the same answers using VBA. I realise now that this is a much
bigger task than I can manage.
I don't need the 13,983,816 combinations themselves, I was just trying
to produce the total combinations for each last digit category. The
code I produced was what I thought was needed to achieve this.
When you say ...
However, I now get the same solution as you. I really don't see where
the error was in my previous code.
... does that mean you have the code to produce the results I am
looking for please?.

Thanks in Advance.
All the Best.
Paul

 

[Paul Black]

Hi Dana,

Here are the formulas I used to calculate the last digits in a 649
Lotto. This is assuming that ALL numbers from 1 to 49 are 2 digits.
For example, 1 is 01 etc.

111111 = 2,887,500 combinations
=COMBIN(9,6)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)+COMBIN(9,5)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(1,1)*COMBIN(4,1)

211110 = 6,930,000 combinations
=COMBIN(9,1)*COMBIN(5,2)*COMBIN(8,4)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)+COMBIN(9,1)*COMBIN(5,2)*COMBIN(8,3)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(1,1)*COMBIN(4,1)+COMBIN(9,4)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(1,1)*COMBIN(4,2)

221100 = 2,772,000 combinations
=COMBIN(9,2)*COMBIN(5,2)*COMBIN(5,2)*COMBIN(7,2)*COMBIN(5,1)*COMBIN(5,1)+COMBIN(9,2)*COMBIN(5,2)*COMBIN(5,2)*COMBIN(7,1)*COMBIN(5,1)*COMBIN(1,1)*COMBIN(4,1)+COMBIN(9,1)*COMBIN(5,2)*COMBIN(8,2)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(1,1)*COMBIN(4,2)

222000 = 105,600 combinations
=COMBIN(9,3)*COMBIN(5,2)*COMBIN(5,2)*COMBIN(5,2)+COMBIN(9,2)*COMBIN(5,2)*COMBIN(5,2)*COMBIN(1,1)*COMBIN(4,2)

311100 = 924,000 combinations
=COMBIN(9,1)*COMBIN(5,3)*COMBIN(8,3)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)+COMBIN(9,1)*COMBIN(5,3)*COMBIN(8,2)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(1,1)*COMBIN(4,1)+COMBIN(9,3)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(1,1)*COMBIN(4,3)

321000 = 316,800 combinations
=COMBIN(9,1)*COMBIN(5,3)*COMBIN(8,1)*COMBIN(5,2)*COMBIN(7,1)*COMBIN(5,1)+COMBIN(9,1)*COMBIN(5,3)*COMBIN(8,1)*COMBIN(5,2)*COMBIN(1,1)*COMBIN(4,1)+COMBIN(9,1)*COMBIN(5,3)*COMBIN(8,1)*COMBIN(5,1)*COMBIN(1,1)*COMBIN(4,2)+COMBIN(9,1)*COMBIN(5,2)*COMBIN(8,1)*COMBIN(5,1)*COMBIN(1,1)*COMBIN(4,3)

330000 = 3,960 combinations
=COMBIN(9,2)*COMBIN(5,3)*COMBIN(5,3)+COMBIN(9,1)*COMBIN(5,3)*COMBIN(1,1)*COMBIN(4,3)

411000 = 39,600 combinations
=COMBIN(9,1)*COMBIN(5,4)*COMBIN(8,2)*COMBIN(5,1)*COMBIN(5,1)+COMBIN(9,1)*COMBIN(5,4)*COMBIN(8,1)*COMBIN(5,1)*COMBIN(1,1)*COMBIN(4,1)+COMBIN(9,2)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(1,1)*COMBIN(4,4)

420000 = 3,960 combinations
=COMBIN(9,1)*COMBIN(5,4)*COMBIN(8,1)*COMBIN(5,2)+COMBIN(9,1)*COMBIN(5,4)*COMBIN(1,1)*COMBIN(4,2)+COMBIN(9,1)*COMBIN(5,2)*COMBIN(1,1)*COMBIN(4,4)

510000 = 396 combinations
=COMBIN(9,1)*COMBIN(5,5)*COMBIN(8,1)*COMBIN(5,1)+COMBIN(9,1)*COMBIN(5,5)*COMBIN(1,1)*COMBIN(4,1)

Making a grand total of 13,983,816 combinations.

All the Best.
Paul

 

[Paul Black]

Hi Dana,

For interest.
To calculate them for the 6 numbers drawn in a Lotto draw I used ...
=SUMPRODUCT(--(RIGHT($O117:$T117,1)="0"))
=SUMPRODUCT(--(RIGHT($O117:$T117,1)="1"))
=SUMPRODUCT(--(RIGHT($O117:$T117,1)="2"))
=SUMPRODUCT(--(RIGHT($O117:$T117,1)="3"))
=SUMPRODUCT(--(RIGHT($O117:$T117,1)="4"))
=SUMPRODUCT(--(RIGHT($O117:$T117,1)="5"))
=SUMPRODUCT(--(RIGHT($O117:$T117,1)="6"))
=SUMPRODUCT(--(RIGHT($O117:$T117,1)="7"))
=SUMPRODUCT(--(RIGHT($O117:$T117,1)="8"))
=SUMPRODUCT(--(RIGHT($O117:$T117,1)="9"))
.... to calculate the last digit for the 6 numbers, I used ...
=SUM(DO117X117)
.... to check the total was 6 (for 6 numbers drawn), then ...
=--
CONCATENATE(DO117,DP117,DQ117,DR117,DS117,DT117,DU117,DV117,DW117,DX117)
.... to string them together, and ...
=LARGE(DO117X117,1)*100000+LARGE(DO117X117,2)*10000+LARGE(DO117X117,3)*1000+LARGE(DO117X117,4)*100+LARGE(DO117X117,5)*10+LARGE(DO117X117,6)
.... to transfer them into the category.

Hope this helps.
All the Best.
Paul

 

[Dana DeLouis]

Wow. Very nice. I don't understand the solution just yet, but I'll study
it some more. :>~

111111 = 2,887,500 combinations
=COMBIN(9,6)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)+COMBIN(9,5)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(5,1)*COMBIN(1,1)*COMBIN(4,1)

The only thing I could add is that
Combin(n,1) reduces to just n.

So, maybe the above could be re-written as:

= COMBIN(9,6)*POWER(5,6)+COMBIN(9,5)*POWER(5,5)*4
= 2,887,500

Anyway, thanks for the info.

 

[Paul Black]

Hi Dana,

Just a few ideas.
I know that we could use MOD 10 to Calculate the Last Digit or
something like :-
LastDigit = A - 10 * Int(A / 10)

I think the code below would be a start :-

Option Explicit
Option Base 1

Sub Last_Digits()
Dim A As Integer, B As Integer, C As Integer, D As Integer, E As
Integer, F As Integer
Dim nVal(10) As Double
Dim i As Integer

Application.ScreenUpdating = False

For i = 1 To 10
nVal(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 nVal = 111111 Then nVal(1) = nVal(1) + 1
If nVal = 211110 Then nVal(2) = nVal(2) + 1
If nVal = 221100 Then nVal(3) = nVal(3) + 1
If nVal = 222000 Then nVal(4) = nVal(4) + 1
If nVal = 311100 Then nVal(5) = nVal(5) + 1
If nVal = 321000 Then nVal(6) = nVal(6) + 1
If nVal = 330000 Then nVal(7) = nVal(7) + 1
If nVal = 411000 Then nVal(8) = nVal(8) + 1
If nVal = 420000 Then nVal(9) = nVal(9) + 1
If nVal = 510000 Then nVal(10) = nVal(10) + 1

Next F
Next E
Next D
Next C
Next B
Next A

Range("B1").Select
For i = 1 To 10
ActiveCell.Offset(i, 0).Value = nVal(i)
Next i

End Sub

The main problem is going to be if there is MORE than one Last Digit
in a Combination. Somehow we are going to need to sum them.
We could use the "Concatenate" Function (&) to string them together
and then use the "Large" Function to sort the highest from left to
right.

Thanks ib Advance.
All the Best.
Paul