Formula To Flash Fill Goofy Pattern

[brod78311]

Hey All,

Wondering if you can assist. I'm trying to have Excel flash fill a pattern (similar to a alphanumeric serial number), but due to the patterns construction, Excel cannot identify and fill. Is there a good function or VBA code to use to have Excel continue the pattern?

The pattern reads like this:

TA0000
TA0001
TA0002
""
TA0009

Then switches to:
TA00A0
TA00A1
TA00A2
""
Then moves to:
TA00B0
TA00B1
TA00B2
""

Once that goes all the way to TA00Z9, it switches again, this time to:
TA0100
TA0101

It gets nuttier from there, but let's start with this. Any help is appreciated.

 

[Eric W]

Well, maybe:

	A	B
1	TA0000	TA0000
2	TA0001	TA0001
3	TA0002	TA0002
4	TA0003	TA0003
5	TA0004	TA0004
6	TA0005	TA0005
7	TA0006	TA0006
8	TA0007	TA0007
9	TA0008	TA0008
10	TA0009	TA0009
11	TA00A0	TA000A
12	TA00A1	TA000B
13	TA00A2	TA000C
14	TA00A3	TA000D
15	TA00A4	TA000E
16	TA00A5	TA000F
17	TA00A6	TA000G
18	TA00A7	TA000H
19	TA00A8	TA000I
20	TA00A9	TA000J
21	TA00B0	TA000K

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet10​

Worksheet Formulas Cell Formula A2 =IF(RIGHT(A1)<>"9",LEFT(A1,5)&RIGHT(A1)+1,IF(RIGHT(A1,2)="Z9",LEFT(A1,3)&MID(A1,4,1)+1&"00",LEFT(A1,4)&IF(MID(A1,5,1)="0","A",CHAR(CODE(MID(A1,5,1))+1))&"0")) B2 =LEFT(B1,2)&BASE(SUM((SEARCH(MID(B1,{3,4,5,6},1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ")-1)*{46656,1296,36,1})+1,36,4) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

The A2 formula does pretty much as you ask. But if your far right column also uses A-Z, and your second to right column also uses 1-9, etc., then the B2 formula should work. BASE was added in 2013.

 

[iliace]

So, after TA0009, it goes to AT00A0 - not TA000A? In other words, the least significant character does not cycle through the letters?

When you say function or VBA code, do you want it to fill a range, or just give you the next item?

Sheet10​

Worksheet Formulas Cell Formula A2 =IF(RIGHT(A1)<>"9",LEFT(A1,5)&RIGHT(A1)+1,IF(RIGHT(A1,2)="Z9",LEFT(A1,3)&MID(A1,4,1)+1&"00",LEFT(A1,4)&IF(MID(A1,5,1)="0","A",CHAR(CODE(MID(A1,5,1))+1))&"0")) B2 =LEFT(B1,2)&BASE(SUM((SEARCH(MID(B1,{3,4,5,6},1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ")-1)*{46656,1296,36,1})+1,36,4) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

The A2 formula does pretty much as you ask. But if your far right column also uses A-Z, and your second to right column also uses 1-9, etc., then the B2 formula should work. BASE was added in 2013.

Exactly - that's what I am wondering.

 

[brod78311]

So, after TA0009, it goes to AT00A0 - not TA000A? In other words, the least significant character does not cycle through the letters?

When you say function or VBA code, do you want it to fill a range, or just give you the next item?

I'd like it to fill a range with the next item (much like how the flash fill function works), based on the predefined pattern I noted in the original post.

 

[brod78311]

Thanks Eric. I'll give these a shot and let you know how it goes.

Probably my initial explanation, but the two noted formulas assume something is in column A to build their logic from. While in reality I'm trying to extend the pattern, below the last alphanumeric value and continue it, based on the sequencing I noted in the initial post.

 

[dreid1011]

Here is a VBA solution for you. A couple notes, this will currently run through "TA09Z9", and can be easily modified to account for the first 0 (TA0000) if necessary. However, I did not make it to identify the last used pattern and continue from there. This will generate the entire list from "TA0000" to "TA09Z9" (2700 rows). One option is to run this on a new sheet, then on your main sheet, use a formula to pick up the next unused pattern.

Anyways, here is the code, copy+paste in a new module.

Private Sub PatternFill()
Dim ws As Worksheet
Dim i As Long, j As Long, k As Long, x As Long
Dim pArr() As Variant
Set ws = Sheets("Sheet1") 'Update sheet name as appropriate
x = 0
ReDim pArr(2699)
Application.ScreenUpdating = False
For i = 0 To 9
    For j = 0 To 269 Step 10
        For k = 0 To 9
            If j = 0 Then
                pArr(x) = "TA0" & i & j & k
                x = x + 1
            Else
                pArr(x) = "TA0" & i & Chr(Int((j - 10) / 10) + 65) & k
                x = x + 1
            End If
        Next k
    Next j
Next i
'Change the A's in the next line to the column you want to put the list. This will output to row 1-2700.
ws.Range("A" & LBound(pArr) + 1 & ":A" & UBound(pArr) + 1) = Application.Transpose(pArr)
Application.ScreenUpdating = True
End Sub

 

[brod78311]

dreid1011 - Thanks! This worked great! Thank you to all who took the time to assist. Much appreciated.

 

[dreid1011]

You're welcome. Let me know if you want it adjusted to account for the first 0.