Code To List Numbers Missing From Sequence

[Dazzawm]

I have a very long list of numbers in column A and I would like a code to tell me which numbers are missing from the sequence. Below is an example.

Excel 2010

	A
SS9822
SS9824
SS9826
SS9828
SS9830
SS9831
SS9833

<colgroup><col style="width: 25pxpx" https:="" www.mrexcel.com="" forum="" usertag.php?do="list&action=hash&hash=DAE7F5"" target="_blank"></colgroup><colgroup><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]4565[/TD]

[TD="align: center"]4566[/TD]

[TD="align: center"]4567[/TD]

[TD="align: center"]4568[/TD]

[TD="align: center"]4569[/TD]

[TD="align: center"]4570[/TD]

[TD="align: center"]4571[/TD]

</tbody>

​

So from above the following numbers are missing so I would like a list of these made in column D.

Excel 2010

	D
SS9823
SS9825
SS9827
SS9829
SS9832

<colgroup><col style="width: 25pxpx" https:="" www.mrexcel.com="" forum="" usertag.php?do="list&action=hash&hash=DAE7F5"" target="_blank"></colgroup><colgroup><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]4565[/TD]

[TD="align: center"]4566[/TD]

[TD="align: center"]4567[/TD]

[TD="align: center"]4568[/TD]

[TD="align: center"]4569[/TD]

</tbody>

​

Thanks

 

[Peter_SSs]

What else can I say!!

You could tell us
- which of the two codes produced the error?
- is my assumption correct?
- do you get that error with all data samples or only one/some?
- do you get that error with the small sample data shown in post #1
- if the data producing the error is other than the sample data, tell us about the size & nature of that data.

If we can't reproduce the error, what else can we say other than that we don't get that error?

 

[Dazzawm]

I think I have found the problem. Not all the data has 4 numbers like my example. There are ones with 3 i.e SS010, SS200. When I run the code with the same as my example it worked fine. Sorry my fault. Can the code accommodate any amount of numbers after the prefix please?

 

[Peter_SSs]

There are ones with 3 i.e SS010, SS200.

In any one file, are all the items of data the same number of characters? That is, could you have SS010 (3 digits) and SS9876 (4 digits) in the same file?

 

[Dazzawm]

Yes there will be.

 

[Peter_SSs]

Yes there will be.

Which question is that answering?

 

[Dazzawm]

Which question is that answering?

The last one you asked in #13 . The same file may consist of SS110 (3 numbers) and SS4567 (4 numbers)

 

[Dazzawm]

Any more thoughts please?

 

[Worf]

Could we have a different number of prefix letters on the same list?

 

[Dazzawm]

9 times out of 10 the prefix letters would be the same.

 

[Dazzawm]

If it makes it easier I can try to make the files with all the same prefix and and the same amount of digits after, although may be a little tricky.