MISSING Alphanumeric Sequence

M

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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
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.
 
Upvote 0
[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]
A​
[/td][td="bgcolor:#C0C0C0"]
B​
[/td][/tr][tr][td="bgcolor:#C0C0C0"]
1​
[/td][td]AA1[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
2​
[/td][td]AE0[/td][td="bgcolor:#CCFFCC"]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[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
3​
[/td][td]AE1[/td][td="bgcolor:#CCFFCC"][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
4​
[/td][td]AE2[/td][td="bgcolor:#CCFFCC"][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
5​
[/td][td]AE3[/td][td="bgcolor:#CCFFCC"][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
6​
[/td][td]AE4[/td][td="bgcolor:#CCFFCC"][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
7​
[/td][td]AE5[/td][td="bgcolor:#CCFFCC"][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
8​
[/td][td]AE6[/td][td="bgcolor:#CCFFCC"][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
9​
[/td][td]AE7[/td][td="bgcolor:#CCFFCC"][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
10​
[/td][td]AE8[/td][td="bgcolor:#CCFFCC"][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
11​
[/td][td]AE9[/td][td="bgcolor:#CCFFCC"][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
12​
[/td][td]AMS[/td][td="bgcolor:#CCFFCC"]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[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
13​
[/td][td]AO0[/td][td="bgcolor:#CCFFCC"]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[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
14​
[/td][td]AO2[/td][td="bgcolor:#CCFFCC"]AO1[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
15​
[/td][td]AO3[/td][td="bgcolor:#CCFFCC"][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
16​
[/td][td]AO4[/td][td="bgcolor:#CCFFCC"][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
17​
[/td][td]AO5[/td][td="bgcolor:#CCFFCC"][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
18​
[/td][td]AO6[/td][td="bgcolor:#CCFFCC"][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
19​
[/td][td]AO7[/td][td="bgcolor:#CCFFCC"][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
20​
[/td][td]AO9[/td][td="bgcolor:#CCFFCC"]AO8[/td][/tr]
[/table]

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
 
Upvote 0
I was able to create the UDF with the code you provided. I am still unable to get the results like you have here.



shg said:
[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"]
A​
[/TD]
[TD="bgcolor: #C0C0C0"]
B​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]
1​
[/TD]
[TD]AA1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]
2​
[/TD]
[TD]AE0[/TD]
[TD="bgcolor: #CCFFCC"]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[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]
3​
[/TD]
[TD]AE1[/TD]
[TD="bgcolor: #CCFFCC"][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]
4​
[/TD]
[TD]AE2[/TD]
[TD="bgcolor: #CCFFCC"][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]
5​
[/TD]
[TD]AE3[/TD]
[TD="bgcolor: #CCFFCC"][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]
6​
[/TD]
[TD]AE4[/TD]
[TD="bgcolor: #CCFFCC"][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]
7​
[/TD]
[TD]AE5[/TD]
[TD="bgcolor: #CCFFCC"][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]
8​
[/TD]
[TD]AE6[/TD]
[TD="bgcolor: #CCFFCC"][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]
9​
[/TD]
[TD]AE7[/TD]
[TD="bgcolor: #CCFFCC"][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]
10​
[/TD]
[TD]AE8[/TD]
[TD="bgcolor: #CCFFCC"][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]
11​
[/TD]
[TD]AE9[/TD]
[TD="bgcolor: #CCFFCC"][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]
12​
[/TD]
[TD]AMS[/TD]
[TD="bgcolor: #CCFFCC"]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[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]
13​
[/TD]
[TD]AO0[/TD]
[TD="bgcolor: #CCFFCC"]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[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]
14​
[/TD]
[TD]AO2[/TD]
[TD="bgcolor: #CCFFCC"]AO1[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]
15​
[/TD]
[TD]AO3[/TD]
[TD="bgcolor: #CCFFCC"][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]
16​
[/TD]
[TD]AO4[/TD]
[TD="bgcolor: #CCFFCC"][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]
17​
[/TD]
[TD]AO5[/TD]
[TD="bgcolor: #CCFFCC"][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]
18​
[/TD]
[TD]AO6[/TD]
[TD="bgcolor: #CCFFCC"][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]
19​
[/TD]
[TD]AO7[/TD]
[TD="bgcolor: #CCFFCC"][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]
20​
[/TD]
[TD]AO9[/TD]
[TD="bgcolor: #CCFFCC"]AO8[/TD]
[/TR]
</tbody>[/TABLE]


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
Click to expand...
 
Upvote 0
There's nothing for you to create; all the code is posted.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Formula not working
Replies
2
Views
316
esmarques
E
V
  • Solved
Average Function
Replies
8
Views
381
Peter_SSs
Peter_SSs
E
  • Question Question
What's the best way to pull data from this sheet?
Replies
1
Views
286
bobaol
B
K
  • Question Question
Consolidate data from individual months by summing 2, 3 or more months together
Replies
0
Views
316
Kaleo
K
P
  • Solved
Lookup based on multiple criteria
2 3
Replies
22
Views
900
paulkc
P

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top