SOLVED

Automatically list down missing numbers from a set of predefined

New Contributor

auto insert missing numbersauto 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 Replies
best response confirmed by fahim108 (New Contributor)
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)

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