# MISSING Alphanumeric Sequence

#### mkemp

##### New Member
Greets,
I am trying to determine the easiest way to find a missing alphanumeric, alpha, or numeric sequence in a excel. I believe the functionality is already there in excel just not sure how to do it or where to start. Any help would be appreciated.

Use Case: I am trying to find all the missing alphanumeric or alpha sequence in a column. The constant is that the sequence is always alpha leading A-Z and is 3 characters in length. The sequence is always A-Z or 0-9.

Example data:

AA1
AE0
AE1
AE2
AE3
AE4
AE5
AE6
AE7
AE8
AE9
AMS
AO0
AO2
AO3
AO4
AO5
AO6
AO7
AO9

Function would return something like this below.

AAA
AAB
AAC
AAE
AAF etc ....

AA0
AA2
AA3
AA4
AA5
AA6
AA7
AA8
AA9

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### mikerickson

##### MrExcel MVP
I'm not sure what a complete sequence would look like.

I would create a function that takes a string as its argument and returns the next string in your sequence.

Then use that UDF to determine if each cell was the NextInLine of the previous cell.

• mkemp

#### shg

##### MrExcel MVP

In A2 and copied down,

=WhatsMissing(A1, A2, "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ")

Code:
``````Function DecToBij(iNum As Long, sSym As String) As String
' shg 2014
' VBA or UDF

' Base sSym
'   1  "1" (Tally)
'   2  "12"
'  10  "123456789A"
'  26  "ABCDEFGHIJKLMNOPQRSTUVWXYZ" (Excel)

' Returns the bijective numeral for iNum using the digits in sSym

' https://en.wikipedia.org/wiki/Bijective_numeration
' https://en.wikipedia.org/wiki/Shortlex_order

If iNum > 0 Then DecToBij = DecToBij((iNum - 1) \ Len(sSym), sSym) & _
Mid(sSym, ((iNum - 1) Mod Len(sSym)) + 1, 1)
End Function

Function BijToDec(sBij As String, sSym As String) As Long
' shg 2014
' VBA or UDF
' Returns the decimal value for the bijective numeral in sBij

' https://en.wikipedia.org/wiki/Bijective_numeration
' https://en.wikipedia.org/wiki/Shortlex_order

If Len(sBij) Then BijToDec = InStr(sSym, Right(sBij, 1)) _
+ Len(sSym) * BijToDec(Left(sBij, Len(sBij) - 1), sSym)
End Function

Function WhatsMissing(s1 As String, s2 As String, sSym As String) As String
Dim i             As Long
Dim i1            As Long
Dim i2            As Long
Dim asOut()       As String

i1 = BijToDec(s1, sSym)
i2 = BijToDec(s2, sSym)

If i2 > i1 + 1 Then
ReDim asOut(i1 + 1 To i2 - 1)
For i = i1 + 1 To i2 - 1
asOut(i) = DecToBij(i, sSym)
Next i
WhatsMissing = Join(asOut, ",")
End If
End Function``````

• mkemp

#### mkemp

##### New Member
I was able to create the UDF with the code you provided. I am still unable to get the results like you have here.

<tbody>
</tbody>

In A2 and copied down,

=WhatsMissing(A1, A2, "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ")

Code:
``````Function DecToBij(iNum As Long, sSym As String) As String
' shg 2014
' VBA or UDF

' Base sSym
'   1  "1" (Tally)
'   2  "12"
'  10  "123456789A"
'  26  "ABCDEFGHIJKLMNOPQRSTUVWXYZ" (Excel)

' Returns the bijective numeral for iNum using the digits in sSym

' https://en.wikipedia.org/wiki/Bijective_numeration
' https://en.wikipedia.org/wiki/Shortlex_order

If iNum > 0 Then DecToBij = DecToBij((iNum - 1) \ Len(sSym), sSym) & _
Mid(sSym, ((iNum - 1) Mod Len(sSym)) + 1, 1)
End Function

Function BijToDec(sBij As String, sSym As String) As Long
' shg 2014
' VBA or UDF
' Returns the decimal value for the bijective numeral in sBij

' https://en.wikipedia.org/wiki/Bijective_numeration
' https://en.wikipedia.org/wiki/Shortlex_order

If Len(sBij) Then BijToDec = InStr(sSym, Right(sBij, 1)) _
+ Len(sSym) * BijToDec(Left(sBij, Len(sBij) - 1), sSym)
End Function

Function WhatsMissing(s1 As String, s2 As String, sSym As String) As String
Dim i             As Long
Dim i1            As Long
Dim i2            As Long
Dim asOut()       As String

i1 = BijToDec(s1, sSym)
i2 = BijToDec(s2, sSym)

If i2 > i1 + 1 Then
ReDim asOut(i1 + 1 To i2 - 1)
For i = i1 + 1 To i2 - 1
asOut(i) = DecToBij(i, sSym)
Next i
WhatsMissing = Join(asOut, ",")
End If
End Function``````

#### shg

##### MrExcel MVP
There's nothing for you to create; all the code is posted.

#### shg

##### MrExcel MVP
In A2 and copied down,
That should say,

In B2 and copied down, ...

Last edited:

#### mkemp

##### New Member
That should say,

In B2 and copied down, ...

^ It works! Generous bow, thank you.

You're welcome.