Combination Of Numbers

[hrayani]

Hello To All,

i need a formula which would result in possible combination of two digit figures.

For Example

Cell A1=503

Possible Combination of two digits figure are

00-03-05-50-53-55-30-33-35

Is there any formula by which we can obtain the above mentioned result.

Awaiting reply,

Humayun

 

[repairman615]

We were thinking alike, I even did the same with adding an additional loop.

I even used "l" for the variable like you!

I am still trying to use this.

Will keep you posted.

Jeff

 

[ChrisOswald]

Hopefully I'm not interrupting too badly, but I got 'distracted' by this...

If I'm not mistaken, you want all possible combinations of length 'N' from a string with X unique 'letters' in it (i.e., '19', '91', '119' '199', etc. should all return the same list because the have the same unique letters of '1' and '9')

Sub MakeValues()
    Dim d As Object
    Dim i As Long, j As Long
    Dim strNbr As String
    Dim lngChoices As Long
    Dim mykeys
    Dim strChoice As String
    Dim lngPossibles As Long
    'change these input box inputs to pull directly from spreadsheet
    'if desired.
    strNbr = InputBox("enter the string to create all combos from")
    lngChoices = InputBox("enter the number of output digits")
 
    Set d = CreateObject("Scripting.Dictionary")
 
    'find out which digits are being used: get unique list
    For i = 1 To Len(Trim(strNbr))
        If Not d.exists(Mid(Trim(strNbr), i, 1)) Then
            d.Add Mid(Trim(strNbr), i, 1), ""
        End If
    Next i
 
    'mykeys is an array containing the unique values to
    'get the combo's from
    mykeys = d.keys()
 
    Range("C:C").Clear
    'hopefully we don't blow out the max size of i
    'of course, if we do, we won't be able to
    'output to a single column anyway :)
    lngPossibles = d.Count
    For i = 0 To (lngPossibles) ^ lngChoices - 1
        strChoice = ""
        For j = lngChoices - 1 To 0 Step -1
            strChoice = strChoice & mykeys(Int(i / (lngPossibles ^ j)) Mod lngPossibles)
        Next j
        'change i+1 to i+x to start inputing in row x
        Cells(i + 1, 3).NumberFormat = "@"
        Cells(i + 1, 3) = strChoice
    Next i
    Set d = Nothing
End Sub

Now, if somebody could figure out the way to do this with a recursive function (my first impulse because the initial methods posted require a way to handle a N deep nested loop, but a technique I'm really bad at)...

 

[repairman615]

Hello ChrisOswald,

I am glad you found interest in this!

Your code is fantastic, I have been looking at it for fifteen minutes and am really impressed with the Math and coding.

Excellent Job!!



-Jeff

 

[hrayani]

Dear Jeff & ChrisOswald,

First of all, i am very sorry for a late late reply as i am some internet connectivity issues.

Thanks for you concern dear ChrisOswald...

The code you provided is working just fantastic and yes i agree with you that the code which i copied form the other thread and ammended had some issues left to be solved - which you did it.. Thanks once again

And Jeff thanks to you too for you continous support

Regards,

Humayun Rayani