Gap Detection

[iyervsv]

Sir,


How to find the gap in a continuous serial no using Excel


For Example

Invoice No


110102870

110102871

110102874

110102875

110102876

110200001


In the above data 110102873 is missing and the serial number 110200001 is totally new and hence from 110102876 to 110200000 should be identified as missing.


Thanking you in advance


V S Venkatraman

iyervsv@gmail.com

+91 9825023502

 

[Hui]

Iyervsv

Assuming your data was in A1:A6 use:

=IF((A6-A1+1)<>COUNT(A1:A6),"Gap","No Gap")

 

[keymaster]

To add to Hui's point, you can write this formula in B2:B6 to indicate if any gap is present.


=if(A2=A1+1,"","Gap - "&A2-A1-1&" invoices")

 

[iyervsv]

GAP DETECTION - I want my result in the following way:


110102870 110102871 0

110102872 110102873 2

110102874 110102876 0

110102876 110200000 97124

110200001


However, the second suggestion gives me the result.... i'll try this and shall get back to you....


Thanks

 

[iyervsv]

Boss Chandoo.... it goes with my requirement


thanks


V S Venkatraman

+91 9825023502

iyervsv@gmail.com