How do i identify missing numbers in a sequential list

[Chet-a-roo]

I have a large list of serial numbers like (1AW15648). I need to identify
numbers that are missing.

Thanks,

Chet Sheetz

PS I am using Excel 2003

[Colin Vicary]

Hi Chet

I once had a similar problem where I had to match two lists to see which was missing. In my case I had to insert lines in the original list so the two lists matched.

eg

A B
1 1
2 2
3 3
5 4
6 5

The code I used moved down column A to match the two values.

Try this, you will have to adapt it for you own job.

Sub Matchem()
LastRow = ActiveSheet.Cells(Rows.Count, "a").End(xlUp).Row
Range("C1").Select
For j = 1 To LastRow
If Range("a" & j) <> Range("b" & j) Then Range("a" & j).Select
Selection.Insert Shift:=xlDown
Range("c" & j).Select
Next j
End Sub

[Bernie Deitrick]

Chet,

For a list starting in cell A1, you could use something like

=IF((VALUE(RIGHT(A2,5))-1)=VALUE(RIGHT(A1,5)),"","Skipped")

copied down to match your list of SORTED serial numbers.

This assumes that your numbers of interest are the 5 right-most digits.

HTH,
Bernie
MS Excel MVP


"Chet-a-roo" <[email protected]> wrote in message
news:[email protected]...
>I have a large list of serial numbers like (1AW15648). I need to identify
> numbers that are missing.
>
> Thanks,
>
> Chet Sheetz
>
> PS I am using Excel 2003

[Sheila D]

You might be able to use something like this:
=IF(RIGHT(A3,5)<>RIGHT(A2,5)+1,"Missing Number","")
assuming that the numeric part is always the last 5 characters


Sheila
"Chet-a-roo" wrote:

> I have a large list of serial numbers like (1AW15648). I need to identify
> numbers that are missing.
>
> Thanks,
>
> Chet Sheetz
>
> PS I am using Excel 2003

[B. R.Ramachandran]

Hi Chet,

The following two approaches may work.

First arrange your data in ascending order.
Let's say you have 900 values, and that the first value is 1AW00001 and it
is in A1, and the last value is 1AW01000 and it is in A900; so there are 100
missing values)
Create a helper column (say B) with consecutive numbers (1AW00001 in B1,
1AW00002 in B2,......1AW01000 in B1000). Note that this column would be
longer since it contains ALL the values in your range including the missing
values.

Approach 1 (Using Conditional Formatting)

Highlight B2, go to 'Conditional Formatting' in the Format menu;
Select "Formula Is" and enter the formula,
=ISNUMBER(MATCH(B1,$A$1:$A$900,0))+1=1
and choose an appropriate formatting (font type, font color, or cell
shading) to easily identify the missing values. And of course, extend that
conditional formatting criterion (you could use conditional formatting
painter) to the entire column B.
This should identify all the missing values.

Approach 2:

In this approach you need another helper column (say C). In C1 enter the
formula,
=IF(ISNUMBER(MATCH(B1,$A$1:$A$900,0))," ",B1)
and fill-in the formula down to C1000.

Hope one of these works for you.

Regards,
B.R. Ramachandran





"Chet-a-roo" wrote:

> I have a large list of serial numbers like (1AW15648). I need to identify
> numbers that are missing.
>
> Thanks,
>
> Chet Sheetz
>
> PS I am using Excel 2003