2 digit combinations of 1-70 (gemmathehusky, where are you))))

[lala]

hi, i need to get all possible 2 digit combinations (order doesn't matter) or numbers from 1-70


gemma, you did this for me before but it was a little different because i needed all digit combinations and the range was froom 0-9. i could've went back and tried adjusting that code but i remember you saying that if the range is over 9 then it might caus overflow.

so can you point me in the right direction, even telling me the right words to google would be enough? i tried googling and since i don't know the right names for what i'm looking for i'm not getting any good results

thank you

 

[plog]

I would create a table with one numeric field and it would have 70 records in it--1 for each number. Next you create a query and bring that table in twice without linking them. Then you bring in the numeric field from each table and run the query which should yield your 4,900 rows of data.

 

[lala]

i only need half of the combinations, for my purposes 1 - 2 and 2 - 1 is the same thing. and also, i need either vba code or a function that creates the combinations so i can do things with them

thank you though!!

 

[Beetle]

i only need half of the combinations, for my purposes 1 - 2 and 2 - 1 is the same thing.

The do like Plog said and create a table of numbers 1-70 and use a query like the following;

Select YourTable.NumberField, YourTable.NumberField
From YourTable, YourTable As B
Where B.NumberField>=YourTable.NumberField
Order By YourTable.NumberField, B.NumberField;

This returns 2485 possible combinations. If you want to use it in vba, then open a recordset using a SQL statement like the above. Then you can loop through it or whatever.

 

[lala]

thank you, i didn't think of doing that, i was so stuck on looking for a function that creates combinations that i didn't realize that for the purposes of this project the above would work. by the way, i found it, if anyone ever needs it.

Option Compare Database

Public intNumNames     As Integer      'largest number in the list (1-70, so 70)
Public intNumPlayers   As Integer      'how many numbers should the combinations consist of (for example, 2 will give you 1-2,1-21,1-34, etc)
Public intPlayerNum    As Integer      'The number of the player whose name is being exported, effectively the column of the output
Public intRow          As Integer      'A number holding the row number for the next combination
Public i, j, k                         'counters
Public intDepth        As Integer      'Number of loops that are running
Public intLoopRange    As Integer      'Number of iterations in any one loop
Public arr()           As Integer      'An array of variables that are used to recursively loop
Public strOutput       As String       'An output string for debugging purposes- easire to read numbers and check them. Note that there are only so many lines in the debug window, so you may not see
                                        'all results for a large number of combinations
Public intexpected     As Integer      'Expected number of combinations

Public Function OutputNames()
    
    intNumNames = 30
    intNumPlayers = 2
    intLoopRange = intNumNames - intNumPlayers + 1
    
    Do
        AnotherLoop (0)
    Loop While intDepth > 0
    
    'intexpected = Combin(intNumNames, intNumPlayers)

End Function

Public Function AnotherLoop(a As Integer)
    intDepth = intDepth + 1
    ReDim Preserve arr(intDepth)
    'For arr(intDepth) = a + 1 To Min(a + intLoopRange, intNumNames)
    For arr(intDepth) = a + 1 To (a + intLoopRange)
        If intDepth = intNumPlayers Then
            intRow = intRow + 1
            strOutput = Format(intRow, "000") & Space(3)
            
            For j = 1 To intNumPlayers
                'Range("A1").Offset(intRow - 1, j - 1) = rngNames(arr(j))
                strOutput = strOutput & arr(j) & Space(3)
                MsgBox strOutput
            Next j
          
          Else
            AnotherLoop (arr(intDepth))
        End If
    Next arr(intDepth)
    intDepth = intDepth - 1
End Function

so thank you very much plog and sorry for not realizing that you gave me a solution
and thank you Beetle for pointing it out

EDIT this code was converted from Excel and i'm not done tweaking it yet, so that's why you see some things that shouldn't have been there and variables that are not used and stuff

 

[lala]

Gemma, if you come across this, i'd still would love to have an update for your code that goes over 10. it was so elegant....

 

[Galaxiom]

Gemma, if you come across this, i'd still would love to have an update for your code that goes over 10. it was so elegant....

BTW "Gemma" is actually Dave. Gemma was his dog.

 

[lala]

BTW "Gemma" is actually Dave. Gemma was his dog.

i learned after the last time he helped me)))) and saw the pic of the dog, shes beautiful)))))))))))
But i don't know him close enough so i call him by his nick

 

[lala]

BTW "Gemma" is actually Dave. Gemma was his dog.

Gemma-Dave is not answering yet, do you want to look at his code and see if you can change it so that it goes over 10?

 

[lala]

Option Compare Database
Option Explicit

Dim result(1000) As String
Dim maxresult As Long

Function combine(required As Long, max As Long, combo As String) As String
Dim x As Long
Dim startfrom As Long
Dim newstr As String

If Len(combo) = required Then
    maxresult = maxresult + 1
    result(maxresult) = combo
    Exit Function
End If
    
If Len(combo) = 0 Then
    startfrom = 0
Else
    startfrom = CLng(Right(combo, 1))
End If


For x = startfrom + 1 To ((max - required) + Len(combo) + 1)
    newstr = combo & CStr(x)
    Call combine(required, max, newstr)
Next


End Function


Sub main()
Dim x As Long
Dim s As String

maxresult = 0
Call combine(3, 7, "")


s = ""
For x = 1 To maxresult
    s = s & result(x) & vbCrLf

Next
MsgBox (maxresult & " permutations" & vbCrLf & vbCrLf & s)

End Sub

anyhow, if anyone wants to try it, here's Gemma's code that gives any number of combinations from a string of up to 9

 

[gemma-the-husky]

The reason my code stopped at 9, is that it makes a string from the possible combinations, in the range 1 to 9. It starts at the rightmost value, 1, and generates all potential next values

so 1 generates
12
13
14
15
16
17
18
19

if you go bigger than 19 the next item is
110 - which confuses the algorithm, as it will start again from the either the 0, or the second 1. Because it never stops eventually you run out of space - either the stack fills, or in this case the stroage array ends up with more than 1000 items.


So. In order to deal with two digit numbers in this way, you need to treat all the values as 2 digit numbers, from 01 to 99 - and modify the algorithm to select the last 2 characters, instead of the last one. For presentation, I have actually added an extra space between numbers.

-----
so this slightly modified version generates strings like
01 02
01 03
....
28 29
28 30
29 30



instead of

12
13
14
..
78
79
89

for 2 from 30, you get 30x29/2 combinations = 435.

Now the only trouble is you can't see all the possible answers, so you will need to do something with the array, to present it differently

But try this code

Option Compare Database
Option Explicit

Dim result(1000) As String
Dim maxresult As Long

Function combine(required As Long, max As Long, combo As String) As String
Dim x As Long
Dim startfrom As Long
Dim newstr As String

If Len(combo) = required * 3 Then
    maxresult = maxresult + 1
    result(maxresult) = combo
    Exit Function
End If
    
If Len(combo) = 0 Then
    startfrom = 0
Else
    startfrom = CLng(Right(combo, 2))
End If


For x = startfrom + 1 To ((max - required) + (Len(combo) / 3) + 1)
    newstr = combo & Format(x, " 00")
    Call combine(required, max, newstr)
Next


End Function


Sub main()
Dim x As Long
Dim s As String

maxresult = 0
Call combine(2, 30, "")


s = ""
For x = 1 To maxresult
        s = s & result(x) & vbCrLf
Next
MsgBox (maxresult & " permutations" & vbCrLf & vbCrLf & s)

End Sub