Help with finding missing serial numbers

[sspreyer]

Hi all ,


I have a list of serial numbers like so below in column E

A0001
A0002
A0003
All Up to A0200

Is there away I can find the missing number in the sequence and paste the missing numbers on sheets 2

For example
A0001
A0003
A0004
A0006

Missing numbers to be pasted on sheet2 would be

A0002
A0005

Thank in advance for any help

shane

[Glenn Kennedy]

You can use this array formula:

=IFERROR("A"&TEXT(SMALL(IF(ISNA(MATCH(ROW(A$1:A$10),--MID(Sheet1!A$1:A$10,2,255),0)),ROW(A$1:A$10)),ROW(A1)),"0000"),"")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...