Identify missing record numbers

K

kabobot

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!
 
J

Jason Morin

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
 
G

Gord Dibben

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
 
K

kabobot

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

Gord Dibben

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 Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to Tool>Macro>Macros.

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

Gord
 
Top