MISSING Alphanumeric Sequence

[mkemp]

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

 

[mikerickson]

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]

	A​	B​
1​	AA1
2​	AE0	AA2,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​	AMS	AEA,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​	AO0	AMT,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​	AO2	AO1
15​	AO3
16​	AO4
17​	AO5
18​	AO6
19​	AO7
20​	AO9	AO8

In A2 and copied down,

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

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]

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​	AE0	AA2,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​	AMS	AEA,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​	AO0	AMT,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​	AO2	AO1
15​	AO3
16​	AO4
17​	AO5
18​	AO6
19​	AO7
20​	AO9	AO8

<tbody>
</tbody>

In A2 and copied down,

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

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]

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

 

[shg]

In A2 and copied down,

That should say,

In B2 and copied down, ...

 

[mkemp]

That should say,

In B2 and copied down, ...

^ It works! Generous bow, thank you.

 

[shg]

You're welcome.