Find missing item in series

M

Mack Neff

I have a spreadsheet showing the storage location of production tooling.
Locations are identified as A051, A052, A235, B543, C234.

The Series would be A001 through A200; B001 through B325, etc.

I need to be able to find an open slot - that is a number that isn't used.
for exampe: Let's say I have tools stored in all slots in section "A" except
A156. how can I create a function in Excel that will let me find a number
that isn't there compared to a list of all numbers?
 
T

Trevor Shuttleworth

Try:

Function FindNextLoc(ByRef RangeLetter As String)
'
' Call Example: =FindNextLoc("A")
'
Application.Volatile
Dim i As Long
Dim SearchLoc As String
Dim NextValue As String
On Error Resume Next
For i = 1 To 201
SearchLoc = RangeLetter & _
Application.WorksheetFunction.Text(i, "000")
NextValue = ""
NextValue = Cells.Find(What:=SearchLoc, _
After:=Range("A01"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If NextValue = "" Then
NextValue = SearchLoc
Exit For
End If
Next i
On Error GoTo 0
FindNextLoc = SearchLoc
If i = 201 Then FindNextLoc = "All Gone"
End Function

Regards

Trevor
 
R

Ragdyer

Here's one idea.

Say you list all possible numbers for a group (C001 - C300), in order, down
Column A.
Say your used locations are randomly listed down Column K.

In B1, enter this formula:

=COUNTIF($K$1:$K$300,A1)

*Double* click the fill handle of B1 to automatically copy the formula down
Column B, as far as there is data in Column A.

NOW, Column B will display 0 next to numbers in Column A that have not been
used.
You will get a 1 in Column B next to all numbers alreadu used.

Also, if you see a 2 displayed in Column B, you'll know that you have a
*duplicate* entry in Column K.
 
Top