confirm the missing numbers in a series

[driller2]

help pls.

I have a column of text and numbers with something like this

Col. A
1
3
3a
3b
5a
7

I need to gather <among the series> the missing numbers in anothe
helper column <note : no decimals, all positive, no gaps>...
2
4
6

is there a formula for this in excel ?

thanks in advance...

 

[Atishoo]

How long is this sequence likely to be?
If not too long you could use something like the following and copy it down
the helper column, extending the range to include the cells above in the
helper column for each cell you place it in.

=IF(ISERROR(MATCH(1,A1:A10,0)),"1",IF(ISERROR(MATCH(2,A1:A10,0)),"2",IF(ISERROR(MATCH(3,A1:A10,0)),"3",IF(ISERROR(MATCH(4,A1:A10,0)),"4",IF(ISERROR(MATCH(5,A1:A10,0)),"5","")))))

 

[Teethless mama]

"numbers" is a defined name range

=SMALL(INDEX((COUNTIF(numbers,ROW(INDIRECT(MIN(numbers)&":"&MAX(numbers))))=0)*ROW(INDIRECT(MIN(numbers)&":"&MAX(numbers))),0),ROWS($1:1)+COUNT(numbers))

copy down as far as needed

 

[Teethless mama]

The formula is required CTRL+SHIFT+ENTER, not just ENTER

 

[T. Valko]

You're not accounting for the letters in the cells.

 

[driller2]

tmama, thats a nice formula, as per fx-test result on the formula may need
some adjustment..
The growing datarange "numbers" will be on a protected column so i cannot
sort it later..

"numbers" "fx result"
1 2
3a 3
3b 4
3c 5
5a 6
5b 8
7 10
9 11
11a 12
14 13
16 15
aa 17
bb 18
cc 19
dd 20
ee 21
ff 22
24 23

is this still possible ?