Actually, in this particular case, you could use this variation of a shorter
ARRAY FORMULA which I first saw posted by BIff

C1: =SMALL(IF(ISNA(MATCH(ROW($18:$51),B$1:B$51,0)),ROW ($18:$51)),ROW(A1))


Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Maybe something like this, which will actually put the missing items in the
list.
using your example:

Here are the steps.....
1)Create a column of the existing numbers (without the alpha prefix)
2)Use a formula to list the missing values
3)Use another formula to convert the missing values to valid items
4)Copy/Paste_Special_Values the converted items to below the origina list
5)Sort the list

Assuming your list is in A1:A12 (as you posted)
1)B1: =--MID(A1,2,255)
copy that formula down thru B12
(the series will start with 18, 19, 23....thru 50)

2)Enter this ARRAY FORMULA in cell
C1:
=INDEX(ROW($A$1:INDEX(B:B,MAX(B:B))),SMALL(IF(COUN TIF($A$1:INDEX(B:B,MAX(B:B)),ROW($A$1:INDEX(B:B,MA X(B:B)))+MIN(B:B)-1)=0,ROW($A$1:INDEX(B:B,MAX(B:B)))+MIN(B:B)-1),ROWS($1:1))-MIN(B:B))+MIN(B:B)
copy C1 and paste into C2 and down as far as you need

Note: For array formulas,
hold down [Ctrl] and [Shift] when you press [Enter],
instead of just pressing [Enter].

3)D1: =TEXT(C1,"\D00000")
copy that formula down as far as you need (in this case thru D22)

4)Copy D1:D22 and Paste_Special.Values into A13

5)Sort Col_A
(The missing items will sort into the list)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Dave09" wrote:

Hi all,
I am in a situation where if I could figure out how to correct
sort AlphaNumeric Data sequentially with adding empty cells where the
sequence skips I could save myself Hours upon HOURS of work. Here is
what I need help with....

I have 1 columns of data for example ( the < before the data is the
cell number)....

<A1 D00018
<A2 D00019
<A3 D00023
<A4 D00030
<A5 D00031
<A6 D00033
<A7 D00034
<A8 D00042
<A9 D00045
<A10 D00046
<A11 D00049
<A12 D00051

What I wish for is a macro or some 'sort' function to do is include
blank cells where the numbers skip. I.E. the output to look like
this....

<A1 D00018
<A2 D00019
<A3
<A4
<A5
<A6 D00023
<A7 D00024
<A8 D00025
<A9
<A10 D00027
<A11
<A12 D00029
<A13
<A14 D00031
<A15 D00032
<A16
<A17 D00034


Only alternative I have is going through and manually putting blank
cells in where the sequence skips. I have to do this for over 10000
entries =/ IS there a link to a solution for this already as im new to
the forums? Or anyone have a step by step way of completing this task?
ANYTHING would be GREATLY appreciated!! Thanks much!!