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.

 

[FDibbins]

Can you show a sample of your data and what you expect?

 

[oldeirish]

Can you show a sample of your data and what you expect?

Can you view this on Dropbox? https://www.dropbox.com/s/hcmp3uvy3tk9uyx/Example.Excel.xlsx?dl=0



Can you view this?

Thank you kindly,

Meghan

 

[FDibbins]

I am not able to access file-hosting sites from my current location Can you copy/paste a sample here?

 

[Peter_SSs]

Further to Ford's comments, you said you had 3 columns of numbers but your sheet appears to have at least 4 columns of numbers. It also appears to have no indication of the expected results as requested by him.

 

[oldeirish]

I am not able to access file-hosting sites from my current location Can you copy/paste a sample here?

[TABLE="width: 905"]
<tbody>[TR]
[TD]Unit Number[/TD]
[TD]Owner Last Name[/TD]
[TD]First Name[/TD]
[TD]Assigned Stall No.[/TD]
[TD]Stall No. 2[/TD]
[TD]Stall No. 3[/TD]
[TD]Locker No.[/TD]
[TD].[/TD]
[TD]Available Stalls[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD][/TD]
[TD][/TD]
[TD]54[/TD]
[TD]75[/TD]
[TD][/TD]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD][/TD]
[TD][/TD]
[TD]39[/TD]
[TD][/TD]
[TD][/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD][/TD]
[TD][/TD]
[TD]45[/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]104[/TD]
[TD][/TD]
[TD][/TD]
[TD]15[/TD]
[TD]25[/TD]
[TD][/TD]
[TD]37[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]105[/TD]
[TD][/TD]
[TD][/TD]
[TD]49[/TD]
[TD]48[/TD]
[TD][/TD]
[TD]45[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]106[/TD]
[TD][/TD]
[TD][/TD]
[TD]43[/TD]
[TD]72[/TD]
[TD][/TD]
[TD]43[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]107[/TD]
[TD][/TD]
[TD][/TD]
[TD]42[/TD]
[TD]41[/TD]
[TD][/TD]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]108[/TD]
[TD][/TD]
[TD][/TD]
[TD]44[/TD]
[TD][/TD]
[TD][/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]109[/TD]
[TD][/TD]
[TD][/TD]
[TD]34[/TD]
[TD][/TD]
[TD][/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]110[/TD]
[TD][/TD]
[TD][/TD]
[TD]35[/TD]
[TD][/TD]
[TD][/TD]
[TD]23[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD][/TD]
[TD][/TD]
[TD]23[/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]112[/TD]
[TD][/TD]
[TD][/TD]
[TD]55[/TD]
[TD][/TD]
[TD][/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]113[/TD]
[TD][/TD]
[TD][/TD]
[TD]58[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]114[/TD]
[TD][/TD]
[TD][/TD]
[TD]56[/TD]
[TD]42[/TD]
[TD][/TD]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]115[/TD]
[TD][/TD]
[TD][/TD]
[TD]8[/TD]
[TD]50[/TD]
[TD][/TD]
[TD]41[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]116[/TD]
[TD][/TD]
[TD][/TD]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD]36[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]117[/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD]27[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]118[/TD]
[TD][/TD]
[TD][/TD]
[TD]32[/TD]
[TD][/TD]
[TD][/TD]
[TD]42[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]119[/TD]
[TD][/TD]
[TD][/TD]
[TD]26[/TD]
[TD][/TD]
[TD][/TD]
[TD]32[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]120[/TD]
[TD][/TD]
[TD][/TD]
[TD]13[/TD]
[TD]73[/TD]
[TD]75[/TD]
[TD]26[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]121[/TD]
[TD][/TD]
[TD][/TD]
[TD]28[/TD]
[TD]70[/TD]
[TD][/TD]
[TD]44[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]122[/TD]
[TD][/TD]
[TD][/TD]
[TD]24[/TD]
[TD][/TD]
[TD][/TD]
[TD]29[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD][/TD]
[TD][/TD]
[TD]51[/TD]
[TD][/TD]
[TD][/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]124[/TD]
[TD][/TD]
[TD][/TD]
[TD]40[/TD]
[TD]64[/TD]
[TD][/TD]
[TD]33[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The 3 columns (D, E and F) labeled Assigned Stall #1, Assigned Stall #2 and Assigned Stall #3. They are assigned to people in Columns A, B and C.

In column J, I'd like to have the three columns (Assigned Stall Numbers) show the numbers, up to 85 that are not in these 3 columns.

Unit & Locker Numbers column are not used here.

Thank you for your patience.

Meghan

 

[oldeirish]

Further to Ford's comments, you said you had 3 columns of numbers but your sheet appears to have at least 4 columns of numbers. It also appears to have no indication of the expected results as requested by him.

Hi,

Sorry about that. Unit numbers and locker numbers are not used here. I'd like to have the results in Column J

Thank you kindly!

Meghan

 

[FDibbins]

OK thanks for the sample

What would a sample answer look like (and why?)

 

[oldeirish]

OK thanks for the sample

What would a sample answer look like (and why?)

A sample answer would be any/all numbers not accounted for in the range. I’m looking to automate a parking stall availability list. If someone wants an extra stall, the number entered into columns D.E and F would automatically be removed form Column J and vice versa. They want to cancel a spot and the number they had would appear in Available Stalls.

As of right now, the lowest stall number is 4, but it does not appear using the formula quoted above.

Thank you again!!

Meghan

 

[Peter_SSs]

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?