Excel 2007 - calculating ranges of numbers?

[Peter]

Hi, weird question. I've got several thousand single-cell
alphanumeric values (e.g., CUSA000040, CUSA000041). Basically they
range from 01 to 10,000. However, some numbers are missing from the
sequence. Is there a way to have Excel show the ranges of values that
*do* exist in this long list? As in, 40-150; 151-200; 205-4000, etc.,
with or without the alpha prefix?

Thanks very much! Hope this question makes sense!

 

[Pete_UK]

Assume your values are in column A on Sheet1. Insert a new Sheet2 and
in A2 enter this value:

CUSA000001

and put this in A3:

CUSA000002

Then select those 2 cells and drag the fill handle down column A as
far as you need to - you will get all the numeric values automatically
in sequence. Then in B2 you can have this formula:

=IF(ISNA(MATCH(A2,Sheet2!A:A,0)),"MISSED","ok")

and then you can copy this down by double-clicking on the fill handle
(the small black square in the bottom right corner of the cursor). You
will get a series of "MISSED" and "ok" down that column, and you can
apply a filter to column B to see all the "ok" values, which are the
ones that are present in your original list (which doesn't need to be
sorted, by the way).

Hope this helps.

Pete

 

[Peter]

Assume your values are in column A on Sheet1. Insert a new Sheet2 and
in A2 enter this value:

CUSA000001

and put this in A3:

CUSA000002

Then select those 2 cells and drag the fill handle down column A as
far as you need to - you will get all the numeric values automatically
in sequence. Then in B2 you can have this formula:

=IF(ISNA(MATCH(A2,Sheet2!A:A,0)),"MISSED","ok")

and then you can copy this down by double-clicking on the fill handle
(the small black square in the bottom right corner of the cursor). You
will get a series of "MISSED" and "ok" down that column, and you can
apply a filter to column B to see all the "ok" values, which are the
ones that are present in your original list (which doesn't need to be
sorted, by the way).

Hope this helps.

[snip]

Brilliant! Thanks!

 

[Pete_UK]

You're welcome, Peter - thanks for feeding back.

Pete