SOLVED

Automatically list down missing numbers from a set of predefined

%3CLINGO-SUB%20id%3D%22lingo-sub-1741501%22%20slang%3D%22en-US%22%3EAutomatically%20list%20down%20missing%20numbers%20from%20a%20set%20of%20predefined%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1741501%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22list%20down%20missing%20numbers%20within%20a%20predefined%20series.png%22%20style%3D%22width%3A%20194px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F223868iF74AFA04C01D55BC%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22list%20down%20missing%20numbers%20within%20a%20predefined%20series.png%22%20alt%3D%22auto%20insert%20missing%20numbers%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3Eauto%20insert%20missing%20numbers%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EIs%20there%20a%20function%2F%20script%20that%20can%20list%20down%20%22missing%20numbers%22%20from%20a%20predefined%20set%3F%3C%2FP%3E%3CP%3ESay%20I%20have%20a%20set%20of%209%20numbers%20(1%20to%209)%20and%20I%20fill%20up%20a%20couple%20of%20cells%20with%20some%20of%20these.%20Can%20Excel%20fill%20up%20the%20remaining%20numbers%20automatically%3F%20-%20preferably%20in%20a%20single%20cell%2C%20with%20commas!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1741501%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1741565%22%20slang%3D%22en-US%22%3ERe%3A%20Automatically%20list%20down%20missing%20numbers%20from%20a%20set%20of%20predefined%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1741565%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F818560%22%20target%3D%22_blank%22%3E%40fahim108%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20a%20user-defined%20VBA%20function%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EFunction%20Missing(rng%20As%20Range%2C%20lo%20As%20Long%2C%20hi%20As%20Long)%20As%20String%0A%20%20%20%20Dim%20dict%20As%20Object%0A%20%20%20%20Dim%20cel%20As%20Range%0A%20%20%20%20Dim%20arr%0A%20%20%20%20Dim%20itm%0A%20%20%20%20Dim%20i%20As%20Long%0A%20%20%20%20Dim%20s%20As%20String%0A%20%20%20%20Set%20dict%20%3D%20CreateObject(%22Scripting.Dictionary%22)%0A%20%20%20%20For%20Each%20cel%20In%20rng%0A%20%20%20%20%20%20%20%20arr%20%3D%20Split(cel.Value%2C%20%22%2C%22)%0A%20%20%20%20%20%20%20%20For%20Each%20itm%20In%20arr%0A%20%20%20%20%20%20%20%20%20%20%20%20dict(Val(itm))%20%3D%201%0A%20%20%20%20%20%20%20%20Next%20itm%0A%20%20%20%20Next%20cel%0A%20%20%20%20arr%20%3D%20dict.keys%0A%20%20%20%20For%20i%20%3D%20lo%20To%20hi%0A%20%20%20%20%20%20%20%20If%20Not%20dict.exists(i)%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20s%20%3D%20s%20%26amp%3B%20%22%2C%22%20%26amp%3B%20i%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%20i%0A%20%20%20%20If%20s%20%26lt%3B%26gt%3B%20%22%22%20Then%0A%20%20%20%20%20%20%20%20Missing%20%3D%20Mid(s%2C%202)%0A%20%20%20%20End%20If%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EUse%20like%20this%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DMissing(C3%3AC4%2C1%2C9)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1741820%22%20slang%3D%22en-US%22%3ERe%3A%20Automatically%20list%20down%20missing%20numbers%20from%20a%20set%20of%20predefined%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1741820%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3BThat%20was%20right%20on%20spot!%20Thanks%20a%20ton...%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
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...