Formula To Show Missing Numbers in a Range

[oldeirish]

Hi,
I have 3 columns with numbers associated with a person. Some people have 1, 2 or 3 numbers assigned to them within the 3 columns.
I’d like to have a 4^th column show all the numbers missing from the range of the 3 columns. I’ve tried this array =SMALL(IF(COUNTIF(D:F,ROW($D$3:$F$79))=0,ROW($D$3:$F$79),""),ROW()) which returns a number, but it is not the smallest(beginning) number that is missing. The 1st number missing is 4 and I cannot figure out how to create this formula. I’d even try VBA, but I understand that less. I can figure it out if it’s written to the specific issue, but other than that, not much.
I’d love to know what I’m doing wrong, or if I should start from scratch and use something completely different.
Thank you kindly in advance for any help anyone may have. I have a few other questions, but will post separately.

 

[oldeirish]

So you want the same set of available numbers repeated in every row of the table?

And how is it that 75 (for example) is already used at least twice?

If there are duplicates, then it is an error on my part. I’d only like the number between columns D, E and F. Not the same set of numbers for each column, just the missing numbers from the amalgamated columns D, E and F. Whatever the missing numbers from 1-80 within the range, I’d like them to show in numerical order from smallest to largest in Colum J. I hope this helps..

Thank you again J

 

[oldeirish]

If there are duplicates, then it is an error on my part. I’d only like the number between columns D, E and F. Not the same set of numbers for each column, just the missing numbers from the amalgamated columns D, E and F. Whatever the missing numbers from 1-80 within the range, I’d like them to show in numerical order from smallest to largest in Colum J. I hope this helps..

Thank you again J

Assigned Stalls should all be part of one range

 

[Peter_SSs]

For somebody who is not familiar with your exact requirements, it is rather confusing. Let's recap a few parts of the thread so far:

Can you show .... what you expect?

It also appears to have no indication of the expected results

What would a sample answer look like

 

[Peter_SSs]

For somebody who is not familiar with your exact requirements, it is rather confusing.

.. on re-reading I think I have it. The remaining question is whether column J is the correct column for the results? In your sample file it would seem more likely to be column I. In any case that is easy to adjust in the code.

This is Worksheet_Change code so the results should automatically update any time a change is made in columns D:F.
Test in a copy of your workbook.

To implement ..

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window & test.

4. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm) & you will need to enable macros.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim d As Object
  Dim a As Variant, e As Variant
  Dim i As Long, lr As Long
  
  Const MaxNum As Long = 85   '<- Highest possible stall number **
  
  If Not Intersect(Target, Columns("D:F")) Is Nothing Then
    Set d = CreateObject("Scripting.Dictionary")
    For i = 1 To MaxNum
      d(i) = 1
    Next i
    lr = Columns("D:F").Find(What:="*", After:=Range("D1"), LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=False).Row
    a = Range("D3:F" & lr).Value
    For Each e In a
      If d.exists(CLng(e)) Then d.Remove CLng(e)
    Next e
    Application.EnableEvents = False
    With Range("J3:J" & lr)   '<- Check the column **
      .ClearContents
      .Resize(d.Count).Value = Application.Transpose(d.Keys)
    End With
    Application.EnableEvents = True
  End If
End Sub

 

[FDibbins]

Sorry for the delay in replying (and Pete makes some good points)

See if you can use this for what you want. I made a small table with columns showing all available stalls, and then next to them, whether that stall number was available. Below is a short sample showing the 1st few rows...
[Table="width:, class:grid"][tr][td] [/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][td]

M​

[/td][td]

N​

[/td][td]

O​

[/td][/tr]
[tr][td]

2​

[/td][td]

1​

[/td][td]Available[/td][td]

26​

[/td][td][/td][td]

51​

[/td][td][/td][/tr]

[tr][td]

3​

[/td][td]

2​

[/td][td]Available[/td][td]

27​

[/td][td]Available[/td][td]

52​

[/td][td]Available[/td][/tr]

[tr][td]

4​

[/td][td]

3​

[/td][td]Available[/td][td]

28​

[/td][td][/td][td]

53​

[/td][td]Available[/td][/tr]

[tr][td]

5​

[/td][td]

4​

[/td][td]Available[/td][td]

29​

[/td][td]Available[/td][td]

54​

[/td][td][/td][/tr]

[tr][td]

6​

[/td][td]

5​

[/td][td]Available[/td][td]

30​

[/td][td]Available[/td][td]

55​

[/td][td][/td][/tr]

[tr][td]

7​

[/td][td]

6​

[/td][td]Available[/td][td]

31​

[/td][td]Available[/td][td]

56​

[/td][td][/td][/tr]
[/table]
K2=IF(COUNTIF($D$3:$F$26,J2)>0,"","Available")
copied down K, then copied down M and O

You can change the working and placement as needed