Alert when consecutive numbers in a list exceed 5

[Patricia]

Good morning

I have thousands of records, and need to be alerted when, consecutive
numbers (in a particular column) exceed 5. It has to be consecutive numbers
(not sorted).

eg
data 1
data 1
data 8
data 8
data 8
data 8
data 8
data 8

In this case the number 8 exceeds 5.
Would really appreciate help on this, as at the moment it is really time
consuming.

Thanking you in anticipation.

[Barb Reinhardt]

Have you thought about using conditional formatting for this?

Select the cells with the numbers you want to compare.

Format -> Conditional Formatting

Condition 1 -> Formula is
=ABS(B1-B2)>=5

Change the format for the cell so that you can visually find the change.

Alternatively, you could add a column with a calculation of the delta
between the two values and use AutoFilter to find the values that exceed
your limits.

"Patricia" <[email protected]> wrote in message
news:%[email protected]...
> Good morning
>
> I have thousands of records, and need to be alerted when, consecutive
> numbers (in a particular column) exceed 5. It has to be consecutive
> numbers
> (not sorted).
>
> eg
> data 1
> data 1
> data 8
> data 8
> data 8
> data 8
> data 8
> data 8
>
> In this case the number 8 exceeds 5.
> Would really appreciate help on this, as at the moment it is really time
> consuming.
>
> Thanking you in anticipation.
>
>
>

[Gary''s Student]

Say you have used cols A & B for data and numbers, then in C6 insert:

=IF((B6=B5)+(B6=B4)+(B6=B3)+(B6=B2)+(B6=B1)=5,1,"") amd copy down

If there are 6 consecutive numbers in column B the function will return a 1,
otherwise a blank.
--
Gary's Student


"Patricia" wrote:

> Good morning
>
> I have thousands of records, and need to be alerted when, consecutive
> numbers (in a particular column) exceed 5. It has to be consecutive numbers
> (not sorted).
>
> eg
> data 1
> data 1
> data 8
> data 8
> data 8
> data 8
> data 8
> data 8
>
> In this case the number 8 exceeds 5.
> Would really appreciate help on this, as at the moment it is really time
> consuming.
>
> Thanking you in anticipation.
>
>
>
>

[Patricia]

Thank you so much for your quick response.

I found that Gary"s Student solution does the job so well - I have used
Barb's idea on the formula cells using conditional formatting to have the
result highlighted.

Thank you both again for your help, really appreciate it.

"Gary''s Student" <[email protected]> wrote in message
news:[email protected]...
> Say you have used cols A & B for data and numbers, then in C6 insert:
>
> =IF((B6=B5)+(B6=B4)+(B6=B3)+(B6=B2)+(B6=B1)=5,1,"") amd copy down
>
> If there are 6 consecutive numbers in column B the function will return a
1,
> otherwise a blank.
> --
> Gary's Student
>
>
> "Patricia" wrote:
>
> > Good morning
> >
> > I have thousands of records, and need to be alerted when, consecutive
> > numbers (in a particular column) exceed 5. It has to be consecutive
numbers
> > (not sorted).
> >
> > eg
> > data 1
> > data 1
> > data 8
> > data 8
> > data 8
> > data 8
> > data 8
> > data 8
> >
> > In this case the number 8 exceeds 5.
> > Would really appreciate help on this, as at the moment it is really time
> > consuming.
> >
> > Thanking you in anticipation.
> >
> >
> >
> >