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 another helper column <note : no decimals, all positive, no gaps...
2
4
6

is there a formula for this in excel ?

thanks in advance...
--
regards

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(MATC H(2,A1:A10,0)),"2",IF(ISERROR(MATCH(3,A1:A10,0))," 3",IF(ISERROR(MATCH(4,A1:A10,0)),"4",IF(ISERROR(MA TCH(5,A1:A10,0)),"5","")))))

"driller2" wrote:


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 another
helper column <note : no decimals, all positive, no gaps...
2
4
6

is there a formula for this in excel ?

thanks in advance...
--
regards




--
driller2

"numbers" is a defined name range

=SMALL(INDEX((COUNTIF(numbers,ROW(INDIRECT(MIN(num bers)&":"&MAX(numbers))))=0)*ROW(INDIRECT(MIN(numb ers)&":"&MAX(numbers))),0),ROWS($1:1)+COUNT(number s))

copy down as far as needed


"driller2" wrote:


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 another
helper column <note : no decimals, all positive, no gaps...
2
4
6

is there a formula for this in excel ?

thanks in advance...
--
regards




--
driller2

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

"Teethless mama" wrote:

"numbers" is a defined name range

=SMALL(INDEX((COUNTIF(numbers,ROW(INDIRECT(MIN(num bers)&":"&MAX(numbers))))=0)*ROW(INDIRECT(MIN(numb ers)&":"&MAX(numbers))),0),ROWS($1:1)+COUNT(number s))

copy down as far as needed


"driller2" wrote:


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 another
helper column <note : no decimals, all positive, no gaps...
2
4
6

is there a formula for this in excel ?

thanks in advance...
--
regards




--
driller2

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

--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
"numbers" is a defined name range

=SMALL(INDEX((COUNTIF(numbers,ROW(INDIRECT(MIN(num bers)&":"&MAX(numbers))))=0)*ROW(INDIRECT(MIN(numb ers)&":"&MAX(numbers))),0),ROWS($1:1)+COUNT(number s))

copy down as far as needed


"driller2" wrote:


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 another
helper column <note : no decimals, all positive, no gaps...
2
4
6

is there a formula for this in excel ?

thanks in advance...
--
regards




--
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 ?


"Teethless mama" wrote:

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

"Teethless mama" wrote:

"numbers" is a defined name range

=SMALL(INDEX((COUNTIF(numbers,ROW(INDIRECT(MIN(num bers)&":"&MAX(numbers))))=0)*ROW(INDIRECT(MIN(numb ers)&":"&MAX(numbers))),0),ROWS($1:1)+COUNT(number s))

copy down as far as needed


"driller2" wrote:


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 another
helper column <note : no decimals, all positive, no gaps...
2
4
6

is there a formula for this in excel ?

thanks in advance...
--
regards




--
driller2