Excel 2007 - calculating ranges of numbers?

  • Thread starter Thread starter Peter
  • Start date Start date
P

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!
 
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
 
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.
Click to expand...

[snip]

Brilliant! Thanks!
 
Back
Top