confirm the missing numbers in a series

D

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...
 
A

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","")))))
 
T

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
 
D

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 ?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top