Automatically list down missing numbers from a set of predefined

[fahim108]

auto insert missing numbers

Is there a function/ script that can list down "missing numbers" from a predefined set?

Say I have a set of 9 numbers (1 to 9) and I fill up a couple of cells with some of these. Can Excel fill up the remaining numbers automatically? - preferably in a single cell, with commas!

[#2 · Accepted solution]

@fahim108 

Here is a user-defined VBA function:

Function Missing(rng As Range, lo As Long, hi As Long) As String
    Dim dict As Object
    Dim cel As Range
    Dim arr
    Dim itm
    Dim i As Long
    Dim s As String
    Set dict = CreateObject("Scripting.Dictionary")
    For Each cel In rng
        arr = Split(cel.Value, ",")
        For Each itm In arr
            dict(Val(itm)) = 1
        Next itm
    Next cel
    arr = dict.keys
    For i = lo To hi
        If Not dict.exists(i) Then
            s = s & "," & i
        End If
    Next i
    If s <> "" Then
        Missing = Mid(s, 2)
    End If
End Function

Use like this:

=Missing(C3:C4,1,9)

[#3]

@Hans Vogelaar That was right on spot! Thanks a ton... :)