macro for extracting missing numbers from a list

Can you supply a sampe file with documents numbers in it?
If I understand well, your documents are numbered with a letter followed with numbers as in A1234. Am I right?
Also, do you want a list of all missing document numbers. Is there a lot of missing numbers or is it a rare situation?

I can't supply a sample of the list, but your understanding is correct.

One of the complications I am facing is that there are often zeros involved.

Samples of document numbers can look like: A01234, AB000456, etc.

Yes, I want to create a list of all missing document numbers.

In some cases, more than one number in series has been missing, so I have had "missing" lists that look like:
A0123
A0345-A0353
A0412-A0443
A0497

The amount of missing numbers are about 75 for a list of a thousand numbers, but the total list can sometimes be as long as 60,000 numbers, so I guess you can say there are a lot.

So far, I am doing this and other analysis by manually inputing equations, but I want to begin to learn how to program macros to eliminate the redundant activities. I figured I would start with this problem and expand into others as I learn.

One more question. Are those 2 document's numbers the same for you or are they 2 different documents:
AB000456 and AB456?
If they are different, then you might have a list of missing documents like:

A123-A145
A234-A278
A0123-A0167
and so on. Right?

Oh, thanks for the great question.

No, the document numbers are consistent in my experience.

So, a list will have all the same conditions (though maybe a zero is replaced by a whole number in a new year as displayed in the final example below).

Therefore, a list will either look like:
A345
A346
A347

or
AB345
AB346
AB347

or
A00345
A00346
A00347
A01001
A01002

or some other similar variation.

There is format variation between locations. However, I am only auditing one location at a time, and within a given location, the pattern is usually consistent. So, I want to make a macro that can filter the list for each location, but handle the variation between locations so I don't have to keep making a new macro every time.

how many characters can a document have? How many letters maximum?

Ooh, I've never considered it before.
Tough to answer.
I've never seen one larger than fifteen digits, but I suppose they could be longer.
I'd be shocked to find one longer than twenty digits, though.

Help us to help you.

If you can't post a real workbook, try making a dummy sample that has no confidential information, but is typical of the type of data to be incurred and the layout is as you are working with.

Sorry. I'm awfully new to online forums and this sort.
Thank you for the advice.

Attached is a sample of what I am working with.

Here is a macro that might help you. The macro is named "missing_doc".
I first created in column G a formula to extract what I call the PREFIX which is the first characters of the document number that is not part of the numeric one. Prefix includes not significant zeros.
Then I created in column H a formula to get the numeric part of the document number.
You copy those 2 formulas down the 2 columns so we have those informations for all the documents.
I assumed the document numbers have no more then 2 alpha characters at the beginning of it.
Now, you only have to run the "missing_doc" macro and you'll have your list in sheet2. Be sure to delete the result of the last run before running the macro a second time.

I'm sure it does not cover all exceptions but it is only a start.

Let me know what you think of it.

Attached is a worksheet containing the macro I created to filter the missing voucher numbers.

The first sheet has the sample of raw data.

The second sheet is a sample of the page where the user inputs the raw data and identifies the number of letters at the beginning of each voucher number (the quantity fills automatically).

The third sheet yields the results. With the raw data inputted into the other sheet, the user just needs to hit the "Analyze" tab, and the list will compile automatically.

I'm now submitting this so anyone who knows more about VBA code could offer a cleaner coding that performs the same functions. If you know any way to reduce some of the redundancies or bare the same results as any part of this in a better way.

(If no one responds by early next week, I will follow up and mark this thread as solved)

See attached file where I created a new button I called Missing Docs. I hope it will give you satisfaction. Do you think it could be of some help for you to know what is the first and last existing documents of a serie? If yes, I can send you a patch to do so.
Regards