Identify missing record numbers
 

Hello.

I have a spreadsheet that is used to track unique tracking codes assigned to
companies. I am looking for a way to easily identify codes (within a range
of 0000-8000) that has not been assigned. I would greatly appreciate help.

Thank you!

1. Re-create the codes from 0-8000 in A2:A8001 in another
sheet (you can enter 1, 2, and then select them, grab the
fill handle, and drag down). Put "Remaining Codes" in A1.

2. In B2 on the same sheet, put:
=COUNTIF(Sheet2!A:A,A2)
This assumes your range of codes is on Sheet2, col. A.

3. Fill the formula down.

4. Filter for 0 or False on col. B using an AutoFilter
(Data Filter AutoFilter)

HTH
Jason
Atlanta, GA


-----Original Message-----
Hello.

I have a spreadsheet that is used to track unique
tracking codes assigned to
companies. I am looking for a way to easily identify
codes (within a range
of 0000-8000) that has not been assigned. I would
greatly appreciate help.

Thank you!
.

kabobot

I found this code somewhere out there in code-land.

Sub DisplayMissing()
Dim C As Range, V As Variant
Dim prev&, k&, n&

k = 1
prev = 10000
For Each C In Intersect(Range("A:A"), ActiveSheet.UsedRange)
If C prev + 1 Then
V = Evaluate("Row(" & prev + 1 & ":" & C - 1 & ")")
n = C - (prev + 1)
Cells(k, "C").Resize(n, 1) = V
k = k + n
End If
prev = C
Next C

End Sub


Lists the missing numbers from column A into Column C


Gord Dibben Excel MVP

On Tue, 4 Jan 2005 12:01:07 -0800, "kabobot"
wrote:

Hello.

I have a spreadsheet that is used to track unique tracking codes assigned to
companies. I am looking for a way to easily identify codes (within a range
of 0000-8000) that has not been assigned. I would greatly appreciate help.

Thank you!

Gord..
I am fairly unfamiliar with macros or VB. Can you point out where this
code needs editing. thank you again.

"Gord Dibben" wrote:

kabobot

I found this code somewhere out there in code-land.

Sub DisplayMissing()
Dim C As Range, V As Variant
Dim prev&, k&, n&

k = 1
prev = 10000
For Each C In Intersect(Range("A:A"), ActiveSheet.UsedRange)
If C prev + 1 Then
V = Evaluate("Row(" & prev + 1 & ":" & C - 1 & ")")
n = C - (prev + 1)
Cells(k, "C").Resize(n, 1) = V
k = k + n
End If
prev = C
Next C

End Sub


Lists the missing numbers from column A into Column C


Gord Dibben Excel MVP

On Tue, 4 Jan 2005 12:01:07 -0800, "kabobot"
wrote:

Hello.

I have a spreadsheet that is used to track unique tracking codes assigned to
companies. I am looking for a way to easily identify codes (within a range
of 0000-8000) that has not been assigned. I would greatly appreciate help.

Thank you!

kabobot

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.

As far as "editing" the macro goes, what exactly would you like changed?

Gord

On Tue, 4 Jan 2005 13:29:08 -0800, "kabobot"
wrote:

Gord..
I am fairly unfamiliar with macros or VB. Can you point out where this
code needs editing. thank you again.

"Gord Dibben" wrote:

kabobot

I found this code somewhere out there in code-land.

Sub DisplayMissing()
Dim C As Range, V As Variant
Dim prev&, k&, n&

k = 1
prev = 10000
For Each C In Intersect(Range("A:A"), ActiveSheet.UsedRange)
If C prev + 1 Then
V = Evaluate("Row(" & prev + 1 & ":" & C - 1 & ")")
n = C - (prev + 1)
Cells(k, "C").Resize(n, 1) = V
k = k + n
End If
prev = C
Next C

End Sub


Lists the missing numbers from column A into Column C


Gord Dibben Excel MVP

On Tue, 4 Jan 2005 12:01:07 -0800, "kabobot"
wrote:

Hello.

I have a spreadsheet that is used to track unique tracking codes assigned to
companies. I am looking for a way to easily identify codes (within a range
of 0000-8000) that has not been assigned. I would greatly appreciate help.

Thank you!