MISSING Alphanumeric Sequence

mkemp

New Member
Joined
Oct 16, 2018
Messages
3
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
AAD
AAE
AAF etc ....


AA0
AA2
AA3
AA4
AA5
AA6
AA7
AA8
AA9
 

Some videos you may like

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
Joined
Jan 15, 2007
Messages
23,254
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.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,746
Office Version
2010
Platform
Windows
A​
B​
1​
AA1
2​
AE0AA2,AA3,AA4,AA5,AA6,AA7,AA8,AA9,AAA,AAB,AAC,AAD,AAE,AAF,AAG,AAH,AAI,AAJ,AAK,AAL,AAM,AAN,AAO,AAP,AAQ,AAR,AAS,AAT,AAU,AAV,AAW,AAX,AAY,AAZ,AB0,AB1,AB2,AB3,AB4,AB5,AB6,AB7,AB8,AB9,ABA,ABB,ABC,ABD,ABE,ABF,ABG,ABH,ABI,ABJ,ABK,ABL,ABM,ABN,ABO,ABP,ABQ,ABR,ABS,ABT,ABU,ABV,ABW,ABX,ABY,ABZ,AC0,AC1,AC2,AC3,AC4,AC5,AC6,AC7,AC8,AC9,ACA,ACB,ACC,ACD,ACE,ACF,ACG,ACH,ACI,ACJ,ACK,ACL,ACM,ACN,ACO,ACP,ACQ,ACR,ACS,ACT,ACU,ACV,ACW,ACX,ACY,ACZ,AD0,AD1,AD2,AD3,AD4,AD5,AD6,AD7,AD8,AD9,ADA,ADB,ADC,ADD,ADE,ADF,ADG,ADH,ADI,ADJ,ADK,ADL,ADM,ADN,ADO,ADP,ADQ,ADR,ADS,ADT,ADU,ADV,ADW,ADX,ADY,ADZ
3​
AE1
4​
AE2
5​
AE3
6​
AE4
7​
AE5
8​
AE6
9​
AE7
10​
AE8
11​
AE9
12​
AMSAEA,AEB,AEC,AED,AEE,AEF,AEG,AEH,AEI,AEJ,AEK,AEL,AEM,AEN,AEO,AEP,AEQ,AER,AES,AET,AEU,AEV,AEW,AEX,AEY,AEZ,AF0,AF1,AF2,AF3,AF4,AF5,AF6,AF7,AF8,AF9,AFA,AFB,AFC,AFD,AFE,AFF,AFG,AFH,AFI,AFJ,AFK,AFL,AFM,AFN,AFO,AFP,AFQ,AFR,AFS,AFT,AFU,AFV,AFW,AFX,AFY,AFZ,AG0,AG1,AG2,AG3,AG4,AG5,AG6,AG7,AG8,AG9,AGA,AGB,AGC,AGD,AGE,AGF,AGG,AGH,AGI,AGJ,AGK,AGL,AGM,AGN,AGO,AGP,AGQ,AGR,AGS,AGT,AGU,AGV,AGW,AGX,AGY,AGZ,AH0,AH1,AH2,AH3,AH4,AH5,AH6,AH7,AH8,AH9,AHA,AHB,AHC,AHD,AHE,AHF,AHG,AHH,AHI,AHJ,AHK,AHL,AHM,AHN,AHO,AHP,AHQ,AHR,AHS,AHT,AHU,AHV,AHW,AHX,AHY,AHZ,AI0,AI1,AI2,AI3,AI4,AI5,AI6,AI7,AI8,AI9,AIA,AIB,AIC,AID,AIE,AIF,AIG,AIH,AII,AIJ,AIK,AIL,AIM,AIN,AIO,AIP,AIQ,AIR,AIS,AIT,AIU,AIV,AIW,AIX,AIY,AIZ,AJ0,AJ1,AJ2,AJ3,AJ4,AJ5,AJ6,AJ7,AJ8,AJ9,AJA,AJB,AJC,AJD,AJE,AJF,AJG,AJH,AJI,AJJ,AJK,AJL,AJM,AJN,AJO,AJP,AJQ,AJR,AJS,AJT,AJU,AJV,AJW,AJX,AJY,AJZ,AK0,AK1,AK2,AK3,AK4,AK5,AK6,AK7,AK8,AK9,AKA,AKB,AKC,AKD,AKE,AKF,AKG,AKH,AKI,AKJ,AKK,AKL,AKM,AKN,AKO,AKP,AKQ,AKR,AKS,AKT,AKU,AKV,AKW,AKX,AKY,AKZ,AL0,AL1,AL2,AL3,AL4,AL5,AL6,AL7,AL8,AL9,ALA,ALB,ALC,ALD,ALE,ALF,ALG,ALH,ALI,ALJ,ALK,ALL,ALM,ALN,ALO,ALP,ALQ,ALR,ALS,ALT,ALU,ALV,ALW,ALX,ALY,ALZ,AM0,AM1,AM2,AM3,AM4,AM5,AM6,AM7,AM8,AM9,AMA,AMB,AMC,AMD,AME,AMF,AMG,AMH,AMI,AMJ,AMK,AML,AMM,AMN,AMO,AMP,AMQ,AMR
13​
AO0AMT,AMU,AMV,AMW,AMX,AMY,AMZ,AN0,AN1,AN2,AN3,AN4,AN5,AN6,AN7,AN8,AN9,ANA,ANB,ANC,AND,ANE,ANF,ANG,ANH,ANI,ANJ,ANK,ANL,ANM,ANN,ANO,ANP,ANQ,ANR,ANS,ANT,ANU,ANV,ANW,ANX,ANY,ANZ
14​
AO2AO1
15​
AO3
16​
AO4
17​
AO5
18​
AO6
19​
AO7
20​
AO9AO8

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

New Member
Joined
Oct 16, 2018
Messages
3
I was able to create the UDF with the code you provided. I am still unable to get the results like you have here.



A​
B​
1​
AA1
2​
AE0AA2,AA3,AA4,AA5,AA6,AA7,AA8,AA9,AAA,AAB,AAC,AAD,AAE,AAF,AAG,AAH,AAI,AAJ,AAK,AAL,AAM,AAN,AAO,AAP,AAQ,AAR,AAS,AAT,AAU,AAV,AAW,AAX,AAY,AAZ,AB0,AB1,AB2,AB3,AB4,AB5,AB6,AB7,AB8,AB9,ABA,ABB,ABC,ABD,ABE,ABF,ABG,ABH,ABI,ABJ,ABK,ABL,ABM,ABN,ABO,ABP,ABQ,ABR,ABS,ABT,ABU,ABV,ABW,ABX,ABY,ABZ,AC0,AC1,AC2,AC3,AC4,AC5,AC6,AC7,AC8,AC9,ACA,ACB,ACC,ACD,ACE,ACF,ACG,ACH,ACI,ACJ,ACK,ACL,ACM,ACN,ACO,ACP,ACQ,ACR,ACS,ACT,ACU,ACV,ACW,ACX,ACY,ACZ,AD0,AD1,AD2,AD3,AD4,AD5,AD6,AD7,AD8,AD9,ADA,ADB,ADC,ADD,ADE,ADF,ADG,ADH,ADI,ADJ,ADK,ADL,ADM,ADN,ADO,ADP,ADQ,ADR,ADS,ADT,ADU,ADV,ADW,ADX,ADY,ADZ
3​
AE1
4​
AE2
5​
AE3
6​
AE4
7​
AE5
8​
AE6
9​
AE7
10​
AE8
11​
AE9
12​
AMSAEA,AEB,AEC,AED,AEE,AEF,AEG,AEH,AEI,AEJ,AEK,AEL,AEM,AEN,AEO,AEP,AEQ,AER,AES,AET,AEU,AEV,AEW,AEX,AEY,AEZ,AF0,AF1,AF2,AF3,AF4,AF5,AF6,AF7,AF8,AF9,AFA,AFB,AFC,AFD,AFE,AFF,AFG,AFH,AFI,AFJ,AFK,AFL,AFM,AFN,AFO,AFP,AFQ,AFR,AFS,AFT,AFU,AFV,AFW,AFX,AFY,AFZ,AG0,AG1,AG2,AG3,AG4,AG5,AG6,AG7,AG8,AG9,AGA,AGB,AGC,AGD,AGE,AGF,AGG,AGH,AGI,AGJ,AGK,AGL,AGM,AGN,AGO,AGP,AGQ,AGR,AGS,AGT,AGU,AGV,AGW,AGX,AGY,AGZ,AH0,AH1,AH2,AH3,AH4,AH5,AH6,AH7,AH8,AH9,AHA,AHB,AHC,AHD,AHE,AHF,AHG,AHH,AHI,AHJ,AHK,AHL,AHM,AHN,AHO,AHP,AHQ,AHR,AHS,AHT,AHU,AHV,AHW,AHX,AHY,AHZ,AI0,AI1,AI2,AI3,AI4,AI5,AI6,AI7,AI8,AI9,AIA,AIB,AIC,AID,AIE,AIF,AIG,AIH,AII,AIJ,AIK,AIL,AIM,AIN,AIO,AIP,AIQ,AIR,AIS,AIT,AIU,AIV,AIW,AIX,AIY,AIZ,AJ0,AJ1,AJ2,AJ3,AJ4,AJ5,AJ6,AJ7,AJ8,AJ9,AJA,AJB,AJC,AJD,AJE,AJF,AJG,AJH,AJI,AJJ,AJK,AJL,AJM,AJN,AJO,AJP,AJQ,AJR,AJS,AJT,AJU,AJV,AJW,AJX,AJY,AJZ,AK0,AK1,AK2,AK3,AK4,AK5,AK6,AK7,AK8,AK9,AKA,AKB,AKC,AKD,AKE,AKF,AKG,AKH,AKI,AKJ,AKK,AKL,AKM,AKN,AKO,AKP,AKQ,AKR,AKS,AKT,AKU,AKV,AKW,AKX,AKY,AKZ,AL0,AL1,AL2,AL3,AL4,AL5,AL6,AL7,AL8,AL9,ALA,ALB,ALC,ALD,ALE,ALF,ALG,ALH,ALI,ALJ,ALK,ALL,ALM,ALN,ALO,ALP,ALQ,ALR,ALS,ALT,ALU,ALV,ALW,ALX,ALY,ALZ,AM0,AM1,AM2,AM3,AM4,AM5,AM6,AM7,AM8,AM9,AMA,AMB,AMC,AMD,AME,AMF,AMG,AMH,AMI,AMJ,AMK,AML,AMM,AMN,AMO,AMP,AMQ,AMR
13​
AO0AMT,AMU,AMV,AMW,AMX,AMY,AMZ,AN0,AN1,AN2,AN3,AN4,AN5,AN6,AN7,AN8,AN9,ANA,ANB,ANC,AND,ANE,ANF,ANG,ANH,ANI,ANJ,ANK,ANL,ANM,ANN,ANO,ANP,ANQ,ANR,ANS,ANT,ANU,ANV,ANW,ANX,ANY,ANZ
14​
AO2AO1
15​
AO3
16​
AO4
17​
AO5
18​
AO6
19​
AO7
20​
AO9AO8

<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
Joined
May 7, 2008
Messages
21,746
Office Version
2010
Platform
Windows
There's nothing for you to create; all the code is posted.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,746
Office Version
2010
Platform
Windows
In A2 and copied down,
That should say,

In B2 and copied down, ...
 
Last edited:

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,746
Office Version
2010
Platform
Windows
You're welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,363
Messages
5,444,023
Members
405,260
Latest member
Khauff

This Week's Hot Topics

Top