# Alpha Numeric within 3 characters - VBA or Formula

• ### 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

• Really Superb Ron .... Give a day to mark this Answered and rating as Outstanding.

Yogananda

Tuesday, November 27, 2012 4:55 PM

### 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 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