Continuous numbers

J

Jeanette

I am currently working with a 'very' large data base, that has a column of
Continuous numbers, is there a way in excel to find the breaks in the
Continuous numbering?
 
T

T. Valko

Be more specific:

'very' large - 500 rows? 5,000 rows? 50,000 rows?

Continuous numbers - From 1 to 500? From 1 to 5,000? From 1 to 50,000?

Biff
 
P

Pete_UK

By "continuous" do you mean that the numbers are meant to increase by
a certain increment each time?

If so, then assuming that your numbers are in column A starting at A2,
and that your increment value is in C2, then enter this formula in B3:

=IF(A3-A2=C$2,"ok","Broken here")

Copy this formula down column B for as many entries as you have in
column A.

Then highlight columns A and B and click Data | Filter | Autofilter.
Use the pull-down at the top of column B to select "Broken here" to
see the non-continuous numbers.

If your increment is fractional you might need to apply ROUND( ... ,2)
or something similar as follows:

=IF(ROUND(A3-A2,2)=ROUND(C$2,2),"ok","Broken here")

Hope this helps.

Pete
 
J

Jeanette

SP-07-00001 to SP-07-09524 etc

T. Valko said:
Be more specific:

'very' large - 500 rows? 5,000 rows? 50,000 rows?

Continuous numbers - From 1 to 500? From 1 to 5,000? From 1 to 50,000?

Biff
Click to expand...
 
P

pshepard

Hi Jeanette,

Assume that the continuous numbers start in A2.

In a new column - assume columm Z:

In Z3: A3-A2

copy this formula for all rows in column Z.

Select Z2, Data/Filter/Autofilter: click on the down arrow, select Custom,
Does Not Equal and 1.

Let me know if this helps.

Thanks,
Peggy
 
P

Pete_UK

Okay, assuming these are in A2 onwards, then enter this formula in B3:

=IF(VALUE(RIGHT(A3,5))-VALUE(RIGHT(A2,5))=1,"ok","Broken here")

Follow the instructions as before.

Hope this helps.

Pete
 
P

Peo Sjoblom

Use a help column, assume the values are in A1:Awhatever, in B1 put

=RIGHT(A2,5)-RIGHT(A1,5)<>1


copy down as long as needed, now every string that is followed by a string
that is not increased by 1 will be tagged with TRUE

now you can filter on the TRUE if you want
 
E

Erny

Hello Jeanette,

If the data is for instance in column A, I could apply "Conditional
formatting" with the following formula:

=(VALUE(RIGHT(A2,5))-VALUE(RIGHT(A1,5)))=(VALUE(RIGHT(A3,5))-VALUE(RIGHT(A2,5)))

and select background colour "red" for instance if this condition is TRUE.

This will colour all those cells in red where the increment to the next one
is different to the one from the next to the "over-next".
Another method is to put the formula in another column and filter on the
TRUE value to find the row numbers, where breaks are.

Kind regards,
Erny
 
Top