If you simply looking for gaps then try this in E2 and drag down:-

=IF(D1=D2-1,"Y","N")

It will display N or anything you want when it encounters a gap or out of
sequence number.

Mike

"Mr. Low" wrote:

Dear Sir,

Lets consider the following worksheet:

A B C D E
Original Extract Sequence
1 0424905001 424905 Y 424905
2 0424907001 424907 Y 424906
3 0424908001 424908 Y 424907
4 0424909001 424909 Y 424908
5 0424910001 424910 Y 424909
6 0424911001 424911 Y 424910
7 0424912001 424912 Y 424911
8 0424913001 424913 Y 424912
9 0424914001 424914 Y 424913


I used =MID(A1,2,6) to extract for the six digit reference number in column
B.

I put in €œY€� in column C and a series of running numbers in column D.

To check if the document reference are in running sequence, I use the
formula =VLOOKUP(D1,B1:C9,2,FALSE) in cell E1 and copy down.

My intention was for cells in Column E to return with €œY€� when the
document numbers are in running order and #NA for missing numbers.

However I noticed that this formula does not work, with all the cells in
column E returns with #NA.

I tried to used =TRIM(CLEAN( ) for column B data and it does not solve the
problem. Neither did it work by converting column B to numbers or text.

Is there any other ways or special formula to overcome this problem?



Thanks

Low


--
A36B58K641