Oct 02 2020 11:37 PM
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!
Oct 03 2020 12:47 AM
SolutionHere 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)
Oct 03 2020 03:49 AM
@Hans Vogelaar That was right on spot! Thanks a ton... :)
Oct 03 2020 12:47 AM
SolutionHere 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)