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"
<kabobot@discussions.microsoft.com> 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"
> <kabobot@discussions.microsoft.com> 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 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

On Tue, 4 Jan 2005 13:29:08 -0800, "kabobot"
<kabobot@discussions.microsoft.com> 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"
>> <kabobot@discussions.microsoft.com> 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!
>>
>>