Continuous numbers
 

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?

Continuous numbers
 

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

"Jeanette" wrote in message
...
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?

Continuous numbers
 

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

On May 24, 10:21 pm, Jeanette
wrote:
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?

Continuous numbers
 

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

"T. Valko" wrote:

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

"Jeanette" wrote in message
...
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?

Continuous numbers
 

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

"Jeanette" wrote:

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?

Continuous numbers
 

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

On May 24, 11:05 pm, Jeanette
wrote:
SP-07-00001 to SP-07-09524 etc



"T. Valko" wrote:
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

"Jeanette" wrote in message
...
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?- Hide quoted text -

- Show quoted text -

Continuous numbers
 

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


--
Regards,

Peo Sjoblom



"Jeanette" wrote in message
...
SP-07-00001 to SP-07-09524 etc

"T. Valko" wrote:

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

"Jeanette" wrote in message
...
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?

Continuous numbers
 

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

"Jeanette" schrieb im Newsbeitrag
...
SP-07-00001 to SP-07-09524 etc

"T. Valko" wrote:

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

"Jeanette" wrote in message
...
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?