Identifying missing numbers on a sequence

[Luthius]

I would like or a SQL statement or a VBA Function that can identify the 10 missing numbers of a sequence.
For instance
I have tblResults with 15 fields that receive random numbers from 1 to 25. There is no repetition on each sequence of 15 numbers. Only exclusive numbers on the sequence for each record of the table:

tblResults Fields
number1
number2
...
number15

I would like to know the missing numbers of each record of the table.

Example
My First Record Contains the sequence: 1,4,5,6,8,9,11,14,15,16,19,21,22,24,25
The missing numbers for my first record: 2,3,7,10,12,13,17,18,20,23

PS.: Each number of the sequence is allocated on each field of my tblResults. So the function or SQL Statement will check each field to find the missing numbers.
The sequence varies.
Example it can be like: 6,8,9,10,11,12,13,15,16,17,18,19,21,23,25
Missing numbers: 1,2,3,4,5,7,14,20,22,24

 

[Gary McMaster]

Have a look here at the UDF by Rick Rothstein:

excel formula to identify missing numbers - Microsoft Community

 

[Luthius]

I already Know about this UDF and usually with excel I prefer to use the combination of formulas

[COLOR=#333333][FONT=Segoe UI]=MIN(IF(ISNA(MATCH(ROW($1:$400),A$2:A$401,0)),IF(COUNTIF(C$1:C1,ROW($1:$400))=0,ROW($1:$400))))[/FONT][/COLOR]

But in this case is different because the range will be on fields of my Access table and I need to create an array of values so I can check what is missing on this array. But I don't know how to start it.

 

[Gary McMaster]

I already Know about this UDF and usually with excel I prefer to use the combination of formulas

=MIN(IF(ISNA(MATCH(ROW($1:$400),A$2:A$401,0)),IF(COUNTIF(C$1:C1,ROW($1:$400))=0,ROW($1:$400))))

But in this case is different because the range will be on fields ...

I don't have a formula to offer but here's a UDF that works on the "fields".

Public Function Excluded(oCell As Range)

Dim lCount As Long
Dim lLargest As Long
Dim vElement As Variant
Dim vSplit As Variant
Dim oDict As Object

Set oDict = CreateObject("scripting.dictionary")

'Change comma delimiter if needed
vSplit = Split(oCell.Value, ",")

For Each vElement In vSplit
    oDict.Add vElement, ""
    ' Find largest number in the array in case it isn't last
    If vElement > lLargest Then lLargest = vElement
Next vElement

For lCount = 1 To lLargest
    If Not oDict.Exists(CStr(lCount)) Then
        Excluded = Excluded & lCount & ","
    End If
Next lCount

Excluded = Left(Excluded, Len(Excluded) - 1)

End Function

 

[Luthius]

Thank for your support and help, but now the point is to translate it to MS Access not Excel. Because as explained before, I have a table with fields, it means that my range will be my fields values.
Access problem.

 

[Luthius]

Well, I started the translation to a VBA function on MS Access.
What I need now is a way to manage the numbers inside my object dictionary. Checking what is missing inside.

Public Function MissingNumbers(strTblName As String)Dim objDict As Object
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim key As Long
Set objDict = CreateObject("scripting.dictionary")
Set db = CurrentDb
Set rs = db.OpenRecordset(strTblName, dbOpenTable, dbAppendOnly)
key = 0


    For i = 0 To rs.Fields.Count
        objDict.Add key, rs.Fields(1).Value
        key = key + 1
    Next i

rs.Close
db.Close
End Function

 

[Luthius]

The function is almost there....
What exactly I need now is to segregate the missing numbers in my function.
I tested your suggestion but it is not working
Can you help me to implement it now so the Dictionary will keep only the missing ones?

Public Function MissingNumbers(strTblName As String, intRecordID As Integer, intItem As Integer)

Dim objDict As Object
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim key As Long


Set objDict = CreateObject("scripting.dictionary")
Set db = CurrentDb
Set rs = db.OpenRecordset(strTblName, dbOpenTable, dbAppendOnly)


key = 0
rs.Index = "PrimaryKey"
rs.Seek "=", intRecordID


    For i = 2 To 16
        objDict.Add key, rs.Fields(i).Value
        key = key + 1
    Next i
   

'I put it in here as your suggestion, but the sequence of missing numbers is wrong.
'    For j = 1 To 25 
'        If Not objDict.Exists(j) Then
'            Excluded = Excluded & j & ","
'        End If
'    Next j


MissingNumbers = objDict.Item(intItem)


rs.Close
db.Close


End Function

 

[Gary McMaster]

I didn't notice you were posting in the Access forum until post #5. Sorry for the confusion.

I'm not sure of the Access specific code but I believe the following line:

MissingNumbers = objDict.Item(intItem)

Should read:

MissingNumbers = Excluded

The character string of numbers that did not exist in the dictionary is assembled in the "Excluded" variable the previous "j" loop.

Do the numbers you are testing always run from 1-25? My original sample was keeping track of the largest number it found in the series and using that for the terminal variable of the above mentioned loop.

 

[Luthius]

First I need to add the items to Dictionary.
The code is not working because it is showing items that exist already in my "range".

The function will work this way.
I will inform the table name and the ID of my table field. I can choose the number is missing as per position of it on my array as per intItem.

 

[Gary McMaster]

In my original loop, post #4, the "Exists" function insisted that I convert the numeric value to a string using "CStr". I believe that is because my original data came from a comma delimited string in Excel so the dictionary keys also became strings.

For lCount = 1 To lLargest
    If Not oDict.Exists(CStr(lCount)) Then 'Exist function insisted on a string
        Excluded = Excluded & lCount & ","
    End If
Next lCount

You don't have anything like that in your "Exists" test in post #7. Easy enough to try it though.

'I put it in here as your suggestion, but the sequence of missing numbers is wrong.
For j = 1 To 25 
     If Not objDict.Exists(j) Then
         Excluded = Excluded & j & ","
     End If
Next j