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
TA0000	TA0000
TA0001	TA0001
TA0002	TA0002
TA0003	TA0003
TA0004	TA0004
TA0005	TA0005
TA0006	TA0006
TA0007	TA0007
TA0008	TA0008
TA0009	TA0009
TA00A0	TA000A
TA00A1	TA000B
TA00A2	TA000C
TA00A3	TA000D
TA00A4	TA000E
TA00A5	TA000F
TA00A6	TA000G
TA00A7	TA000H
TA00A8	TA000I
TA00A9	TA000J
TA00B0	TA000K

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]17[/TD]

[TD="align: center"]18[/TD]

[TD="align: center"]19[/TD]

[TD="align: center"]20[/TD]

[TD="align: center"]21[/TD]

</tbody>

Sheet10​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A2[/TH]
[TD="align: left"]=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"))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=LEFT(B1,2)&BASE(SUM((SEARCH(MID(B1,{3,4,5,6},1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ")-1)*{46656,1296,36,1})+1,36,4)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A2[/TH]
[TD="align: left"]=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"))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=LEFT(B1,2)&BASE(SUM((SEARCH(MID(B1,{3,4,5,6},1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ")-1)*{46656,1296,36,1})+1,36,4)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


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.