Find missing values in logical sequence of letters+numbers

**Dno** · 11-06-2008, 08:52 PM

Hi,

I have a list of letters + numbers in sequence down a column.
The numbers are prefixed by a letter in alphabetical order.
When I sort the the column the numbers sort in (I think) numeric format
(example below)

What I'd like to do is find out if any values are missing from a sequence
and highlight the row before and/or after the missing value(s).

I know this can be done with conditional formatted when the column is a sequence of only numbers (no letter prefix)
and also sorted normally eg. 1,2,3,4,5,6,7,8,9,10 vs 1,10,11-19,2,20,21,22,23 etc.
http://www.viloria.com/secondthought.../00001358.html

A1
A10
A11
A12
A13
A14
A15
A16
A17
A18
A19
A2
A20
A21
B1
B10
B11
B12
etc

Thanks for your time,

Dino

**Dno** · 11-06-2008, 09:04 PM

Also if needed I can make the column sort as A1,A2,A3-A9, A10, A11 by applying =text(A1,0) in the helper row then sorting by the helper row.
I'd just like to eliminate this step if possible.

Thanks,

Dino