none
Alpha Numeric within 3 characters - VBA or Formula RRS feed

  • Question

  • Hi,

    I would need all Possibility Permutation & Combination of 3 characters (3digits) with Alpha numeric..

    Some of examples will clearly make u understand

    1. 1AA
    2. 1AB
    3. 1AC
    4. 2BA
    5. 2BB
    6. A1A
    7. A2A

    Provide me a column which can work through VBA or Formula


    Yogananda

    Saturday, November 24, 2012 8:26 AM

Answers

All replies

  • hi yogananda,

    do you mean to say: if we take 3 characters like ABC:
    The following would be your output.

    ABC
    ACB
    BCA
    BAC
    CAB
    CBA

    pls clarity

    Regards
    Repath.


    Love the Love that Loves the Love and Hate the Love that Loves the Hate.

    Saturday, November 24, 2012 11:34 AM
  • Hi Athyala,

    your right to expectation but i would need 1 numeric with 3 characters. ( Numeric can come in any place 1 or 2 or 3 place)

    Example : 1A1 or A1A or AA1 like till all Possibility


    Yogananda

    Saturday, November 24, 2012 1:43 PM
  • On Sat, 24 Nov 2012 13:43:17 +0000, Yogananda Muthaiah wrote:
     
    >
    >
    >Hi Athyala,
    >
    >your right to expectation but i would need 1 numeric with 3 characters. ( Numeric can come in any place 1 or 2 or 3 place)
    >
    >Example : 1A1 or A1A or AA1 like till all Possibility
    >
    >
    >
    >Yogananda
     
    Your specifications seem to call for any single digit plus any two letters giving a three character result.  And you allow for the letters to repeat (e.g: AA1 has a repeating A).
    This computes to a bit more than 20,000 possibilities (e.g. 10x26x26x3).  Is this, in fact, what you are looking for?
     

    Ron
    Saturday, November 24, 2012 9:19 PM
  • Dear Ron,

    Series from  1 to 9 and A to Z combinations.

    eg    (9X26X26) or (26X9X26) or (26X26X9)    



    Yogananda

    Sunday, November 25, 2012 2:14 AM
  • On Sun, 25 Nov 2012 02:14:29 +0000, Yogananda Muthaiah wrote:
     
    >
    >
    >Dear Ron,
    >
    >Series from  1 to 9 and A to Z combinations.
    >
    >eg    (9X26X26) or (26X9X26) or (26X26X9)    
    >
    >
     
    There's probably a better way of doing this, but the following seems to provide the desired results (if I understand your requirements correctly).
     
    It uses a VBA macro to develop all of the permutations, and will then write the results to column A of the active worksheet.   You should be able to figure it out how it works by examining the macro.
     
     
     
    To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
    Ensure your project is highlighted in the Project Explorer window.
    Then, from the top menu, select Insert/Module and
    paste the code below into the window that opens.
     
    To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.
     
    ======================================
    Option Explicit
    Sub PermutX3()
        Dim aRes() As String
        Dim aResFinal() As String
        Dim lCnt As Long
        Dim i As Long, j As Long
        Dim rDest As Range
    Set rDest = Range("A1")
    lCnt = 26 * 26 * 9
    ReDim aRes(1 To lCnt, 1 To 3)
    ReDim aResFinal(1 To lCnt * 3, 1 To 2)
     
    'set the single digit in column 3
     
    'populate column 1
    For i = 1 To lCnt Step lCnt / 26
        For j = 1 To lCnt / 26
            aRes(i - 1 + j, 1) = Chr(Int(i / (lCnt / 26)) + 65)
        Next j
    Next i
     
    'populate column 2
    For i = 1 To lCnt Step 9
        For j = 1 To 9
            aRes(i - 1 + j, 2) = Chr(Int(i / 9) Mod 26 + 65)
        Next j
    Next i
     
    'populate column 3
    For i = 1 To lCnt
        aRes(i, 3) = (i - 1) Mod 9 + 1
    Next i
     
    'Final Results
    'copy aRes to aResFinal
    'column order 1 2 3
    For i = 1 To lCnt
        aResFinal(i, 1) = aRes(i, 1) & aRes(i, 2) & aRes(i, 3)
    Next i
    'column order 1 3 2
    For i = 1 + lCnt To lCnt * 2
        aResFinal(i, 1) = aRes(i - lCnt, 1) & aRes(i - lCnt, 3) & aRes(i - lCnt, 2)
    Next i
    'column order 3 1 2
    For i = 1 + lCnt * 2 To lCnt * 3
        aResFinal(i, 1) = aRes(i - lCnt * 2, 3) & aRes(i - lCnt * 2, 1) & aRes(i - lCnt * 2, 2)
    Next i
     
    rDest.EntireColumn.Clear
    Set rDest = rDest.Resize(rowsize:=UBound(aResFinal, 1))
    rDest = aResFinal
    End Sub
    =================================================
     

    Ron
    • Proposed as answer by Ron Rosenfeld Friday, November 30, 2012 11:45 AM
    Monday, November 26, 2012 2:53 AM
  • Really Superb Ron .... Give a day to mark this Answered and rating as Outstanding.

    Yogananda

    Tuesday, November 27, 2012 4:55 PM